"SiteGround Tutorials PHP/MySQL Tutorial: How to Use PHP and MySQL?
PHP is the most popular scripting language for web development. It is free, open source and serverside (the code is executed on the server). MySQL is a Relational Database Management System (RDBMS) that uses Structured Query Language (SQL). It is also free and open source. The combination of PHP and MySQL gives unmet options to create just about any kind of website from small contact form to large corporate portal. This PHP/MySQL tutorial is part of the rich collection of web hosting tutorials which SiteGround experts have prepared for you. Find out how to start your website, how to promote it, how to deal with cPanel, webmail and FTP.
PHP/MySQL Tutorial includes:
A short introduction on what is PHP; What is MySQL and what are its benefits; Creating MySQL database and user useful information for installing a PHP/MySQL script on your website; Removing MySQL database and user; Learn how to manage MySQL databases with phpMyAdmin MySQL Backup/Restore functionality and how to use MySQL Access Host; Creating a database table; Connecting to the database; Querying the database; Displaying table data in HTML; Selecting individual records; How to use the MySQL GUI tools to backup, restore and execute queries on your database; Information about Zend Optimizer; Learn how to install Pear modules.
PHP Tutorial What is PHP?
PHP was at first created as a simple scripting platform called "Personal Home Page". Nowadays PHP (the short for Hypertext Preprocessor) is an alternative of Microsoft's Active Server Pages (ASP) technology. PHP is an open source serverside language which is used for creating dynamic web pages. It can be embedded into HTML. PHP is usually used in conjunction with a MySQL database on Linux/UNIX web servers. It is probably the most popular scripting language. PHP is a widely used generalpurpose scripting language and interpreter that is freely available. A full explanation of all the PHP tags, complete user manual and lots of tutorials can be found on the PHP's official page. One of the most important things about using PHP is to have a PHP specialized host. Here are some of the things we at SiteGround are proud of: We have long experience in providing technical support for PHPbased web sites. Thanks to it our servers are perfectly optimized to offer the best overall performance for most PHP applications. We offer a lot of FREE PHP tools including CMS systems, forums, galleries, blogs, shopping carts and more. We provide several versions of PHP (PHP 5.0, PHP 5.1, PHP 5.2) on a perfolder basis. Our PHP hosting package is the best offer on the market it has the lowest price for the quality and features it includes. Sign up now for our Professional PHP Hosting!
MySQL Tutorial What is MySQL?
MySQL is a freely available open source Relational Database Management System (RDBMS) that uses Structured Query Language (SQL). SQL is the most popular language for adding, accessing and managing content in a database. It is most noted for its quick processing, proven reliability, ease and flexibility of use. MySQL is an essential part of almost every open source PHP application. Good examples for PHP/MySQLbased scripts are phpBB, osCommerce, and Joomla.
One of the most important things about using MySQL is to have a MySQL specialized host. Here are some of the things SiteGround is proud of: We have long experience in providing technical support for MySQL based web sites. Thanks to it our servers are perfectly optimized to offer the best overall performance for most MySQL applications. We offer a lot of FREE MySQL tools including CMS systems, forums, galleries, blogs, shopping carts and more. We support both MySQL 4 and MySQL 5. We provide unlimited MySQL databases. Our MySQL hosting package is the best offer on the market it has the lowest price for the quality and features it includes. Sign up now for our Professional MySQL Hosting!
MySQL Tutorial: Creating MySQL Database and User
Check out the Creating and Modifying MySQL Databases video tutorial: Creating and Modifying MySQL Databases Video Tutorial
Creating a MySQL Database Detailed Tutorial
Creating a MySQL database with cPanel, which is included in all of SiteGround's hosting plans, can be easily accomplished. In order to create a MySQL database on your hosting account, you need to log in the cPanel. This can be done by either using the Go button in the Customer Area >My Account Account section:
or by entering the following URL in your web browser: http://yourdomainname.com/cpanel http://cpanel.servername.com
You should replace "yourdomainname.com" with your actual domain name or "servername.com" with the hostname of the SiteGround server on which your account is hosted.
Once you have logged in the cPanel, you can access the MySQL management screen by clicking on the MySQL Databases button as shown below.
In order to create a new database you need to enter the desired name for the database in the New
Database field and click on the Create Database button as shown below:
A confirmation screen will be displayed, informing that the database has been successfully created:
You can click on the Go Back button to return to the database management screen.
The MySQL database is created with your username as prefix username_databasename and this is the name you have to use for your application.
Creating a MySQL Username Detailed Tutorial
You can continue with the creation of a user who should be able to connect to the newly created database. To create a user you just need to fill in the desired user name and password as shown in the image below:
Click Submit to save your MySQL username.
You should always bear in mind that your cPanel username will be added as a prefix to all databases and usernames you create.
Adding a user to a MySQL Database Detailed Tutorial
Once the database and the username are created, you can add certain privileges for the user to the
database. In order to do so you should select them both from the corresponding dropdown menus under Add User To Database label and click on the Submit button:
You will be redirected to a screen where you will be prompted to choose the desired privileges. It is advisable to select All Privileges and click on the Make Changes button:
Using the MySQL Database Wizard
All of the steps above can be accomplished using the MySQL Database Wizard. Click on it and you will be guided trough all steps that you should perform in order to successfully create a new MySQL database and add a user to it.
Check out the Using MySQL Databases Wizard video tutorial: Using MySQL Databases Wizard Video Tutorial
MySQL Tutorial: Removing MySQL Database and User Removing Privileged User from a database
If you would like to remove a privileged user from a database you can easily do so by clicking on the corresponding button next to the database user as shown below:
A confirmation message for the removal will be displayed. This means that the username no longer has any privileges to the database.
Removing a MySQL database
If you would like a MySQL database removed this can be easily accomplished by clicking on the Delete Database button under the Actions area:
You will get a confirmation screen when the database is removed.
Note that this process is irreversible and all the data from this database will be lost.
Removing a MySQL User
If a MySQL username is no longer needed you can remove it by clicking on the corresponding Delete button under the Current Users area:
Again a confirmation screen will be shown upon successful removal.
MySQL Tutorial How to Access and Manage your MySQL Database phpMyAdmin
There are two ways to access and manage your newly created database. The first one is locally through the Web based manager phpMyAdmin, which is accessed from the cPanel Databases section.
A new page/tab of your browser will open and the phpMyAdmin will load as shown below:
Check out how to manage MySQL database through phpMyAdmin video tutorial: How to Manage MySQL Database Through phpMyAdmin Video Tutorial
With phpMyAdmin you can create a MySQL backup and restore it. The second database management solution is to add a MySQL access host and allow a remote MySQL connection.
MySQL Tutorial MySQL Export: How to backup your MySQL database?
You can easily create a dump file(export/backup) of a database used by your account. In order to do so you should access the phpMyAdmin tool available in your cPanel:
The phpMyAdmin tool will be loaded shortly. You can select the database that you would like to backup from the dropdown menu called Database (located in the upper left corner of the page).
A new page will be loaded in phpMyAdmin showing the selected database. In order to proceed with the backup click on the Export tab:
The options that you should select apart from the default ones are Save as file (which will save the file locally to your computer in an .sql format) and Add DROP TABLE (which will add the drop table functionality if the table already exists in the database backup) as shown below:
Click on the Go button to start the export/backup procedure for your database:
A download window will pop up prompting for the exact place where you would like to save the file on your local computer. It is possible that the download starts automatically. This depends on your browser's settings.
MySQL Import: How to restore your MySQL database from a backup
To restore (import) a database via phpMyAdmin, first choose the database you'll be importing data into. This can be done from the dropdown menu on the left. Then click the Import tab:
You have the option of importing an .sql file. Use the Browse button to find it on your local computer. Note that you are given the option to choose the character set of the file from the drop down menu just below the upload box. If you are not certain about the character set your database is using just leave the default one.
In order to start the restore click on the Go button at the bottomright. A notification will be displayed upon a successful database import.
MySQL Tutorial Adding MySQL access hosts
You can allow access to MySQL databases from an external location by adding its domain name or IP address to the list of hosts that are allowed to access your databases remotely. Additionally, if you would like to manage your database using an application for remote database management, the IP address from which the connection is established should be added to the list of allowed hosts. In order to add allowed hosts you should access the tool via cPanel > Remote MySQL:
In the Host field you should enter the desired domain name or IP address:
Click on the Add host button. A confirmation message will inform that the host is successfully added. Next time you access Remote MySQL the newly added host will be listed under Access Hosts.
PHP/MySQL Tutorial Creating a database table
Before you can do anything with your database, you should create a table and set the corresponding fields in it. Creating a table in PHPMyAdmin is simple. Just type the name, select the number of fields and click the Go button. You will then be taken to a setup screen where you should create the fields for the database.
Alternatively, you can run a MySQL query, which will create the table. The format is: CREATE TABLE tablename ( Fields ) The fields are defined as follows: fieldname type(length) extra info, The fields are separated by comma. For example, if you wish to create a table called Members with 3 fields in it FirstName, LastName and Age, you should execute the following query: CREATE TABLE Members ( FirstName varchar(15), LastName varchar(15), Age int ); More information on how to connect to the database and how to query it can be found in the following tutorials: Step 2: Connect to the database Step 3: Query the database Step 4: Display table data Step 5: Select individual records
PHP/MySQL Tutorial PHP MySQL Connection How to Connect to Your Database
Step 2: You should establish a connection to the MySQL database. This is an extremely important step because if your script cannot connect to its database, your queries to the database will fail. A good practice when using databases is to set the username, the password and the database name values at the beginning of the script code. If you need to change them later, it will be an easy task. $username="your_username"; $password="your_password"; $database="your_database"; You should replace "your_username", "your_password" and "your_database" with the MySQL username, password and database that will be used by your script. At this point you may be wondering if it is a security risk to keep your password in the file. You don't need to worry because the PHP source code is processed by the server before being sent to the browser. So the visitor will not see the script's code in the page source. Next you should connect your PHP script to the database. This can be done with the mysql_connect PHP function: mysql_connect(localhost,$username,$password); This line tells PHP to connect to the MySQL database server at 'localhost' (localhost is the MySQL server which usually runs on the same physical server as your script).
After the connection is established you should select the database you wish to use. This should be a database to which your username has access to. This can be completed through the following command: @mysql_select_db($database) or die( "Unable to select database"); It tells PHP to select the database stored in the variable $database (in our case it will select the database "your_database"). If the script cannot connect it will stop executing and will show the error message: Unable to select database The 'or die' part is useful as it provides debugging functionality. However, it is not essential. Another important PHP function is: mysql_close(); This is a very important function as it closes the connection to the database server. Your script will still run if you do not include this function. And too many open MySQL connections can cause problems for your account. This it is a good practice to close the MySQL connection once all the queries are executed. You have connected to the server and selected the database you want to work with. You can start querying the database now. Step 3: Query the database Step 4: Display table data Step 5: Select individual records
PHP/MySQL Tutorial PHP MySQL Connection How to Query the Database
Step 3: There are at least two ways to query a database. One is to enter the command in PHP. Another way is to define the command as a variable. In this part of the tutorial we will show the first way. The command will look like this: mysql_query($query); The command can be repeated over and over again in the source code. All you need to do is to change the variable. Here is the complete code that should be used to create a MySQL table in PHP: <?php $user="username"; $password="password"; $database="database"; mysql_connect(localhost,$user,$password); @mysql_select_db($database) or die( "Unable to select database"); $query="CREATE TABLE tablename(id int(6) NOT NULL auto_increment,first varchar(15) NOT NULL,last varchar(15) NOT NULL,field1name varchar(20) NOT NULL,field2name varchar(20) NOT NULL,field3 name varchar(20) NOT NULL,field4 name varchar(30) NOT NULL,field5 name varchar(30) NOT NULL,PRIMARY KEY (id),UNIQUE id (id),KEY id_2 (id))"; mysql_query($query); mysql_close(); ?> Enter your database, MySQL username and MySQL password in the appropriate positions on the first three lines above. The next query should fill in the table. Here is a sample one: $query = "INSERT INTO tablename VALUES ('','$field1name','$field2name','$field3name','$field4name','$field5name')"; You can't insert more values than the number of fields you have created with the first query. Step 4: Display table data Step 5: Select individual records
PHP/MySQL Tutorial PHP MySQL Connection
How to Display MySQL Table Data?
Step 4: After you have created the table and entered the data, you will probably need to display it. This is usually done using basic HTML code which invokes a PHP script. We will start the example from the beginning. We will populate a new database table with data. The following HTML code will collect the data from the text boxes and pass it to the PHP script: <form action="insert.php" method="post"> Value1: <input type="text" name="field1name"><br> Value2: <input type="text" name="field2name"><br> Value3: <input type="text" name="field3name"><br> Value4: <input type="text" name="field4name"><br> Value5: <input type="text" name="field5name"><br> <input type="Submit"> </form> The next thing you need is a new PHP script which will enter the data in the database. <?php $username="username"; $password="passw..."
|
You need to upgrade your Flash Player , or try to enable javascript in order see this document properly.
|
|
Comments
