We will show how to use PHP interact with MySQL database with a simple example in the next page. Please click here to view PHP and MySQL Interaction Example after reading this tutorial.
Many dynamic websites on the Internet are mainly built up with PHP and MySQL database. Therefore this is a good idea to study how PHP and MySQL database work together.
Table of Contents
Basic Concept of MySQL
MySQL is the most popular database on the Internet. Most websites on the Internet are using MySQL because it is powerful and free. The data in MySQL database is stored in tables. And the tables have records and fields. A database can be illustrated with the following schematic diagram:
Note:
- a database can has one or more tables, and
- each table must has a unique name.
How PHP Interact with MySQL Database
This is rather easy to use PHP interact with MySQL database. In order to illustrate the interaction process, it can be broken down in the following five steps.
- Connect with MySQL server.
- Select a MySQL database to work with.
- Perform query on MySQL database tables, e.g. read data, update data, insert data or delete data.
- Display results on the screen, if required.
- Close the connection with MySQL server.
Please note that the interaction with the MySQL database is actually a continuous process. This is only for illustration purpose that the whole process is broken down into the above five steps.
Using PHP Connect with MySQL Database Example
We will use a basic and simple example to illustrate how to use PHP to connect with MySQL database and fetch data from a table.
The following simple MySQL database has been created which will be used in this example.
Using PHP Connect to MySQL Server
There are many ways using PHp to connect with MySQL database. You may check this PHP and MySQL connection tutorial for more details. In this example we use a very simple and easy-to-understand method that use PHP to interact with MySQL database.
The PHP codes below will connect with MySQL database.
<?php
$servername = 'localhost'; // MySql Server Name or IP address
$dbusername = 'alex'; // MySql Server login user id
$dbpassword = 'mypassword'; // MySql Server user login password
$dbname='test'; // MySql Server database name
global $link;
// create connection
$link = mysql_connect("$servername", "$dbusername", "$dbpassword") or die ("Couldn't connect to the server.");
// select database
$db = mysql_select_db("$dbname") or die ("Couldn't select database.");
// table name working with
$table_name = "member";
?>
Here’s how the codes work:
1. PHP use mysql_connect() function to open or create a connection to a MySQL server.
<?php
// create connection
$link = mysql_connect ("$servername", "$dbusername", "$dbpassword") or die ("Couldn't connect to the server.");
?>
Note:
Three parameters are required for the mysql_connect function, they are:
- DB_SERVER – name of MySQL server. In most cases “localhost” is used for DB_SERVER since the MySQL server is usually installed with the web server. Some hosting provider like GoDaddy is using something like “p50mysqlxxx.secureserver.net” for the name of MySQL server. Ask your hosting provider if the server name is not using “localhost”.
- DB_SERVER_USERNAME – username use to connect with the MySQL server. Create a new username and password if you forgot or do not know the username. With phpMyAdmin, this is very easy to create a new username with password.
- DB_SERVER_PASSWORD – password use to connect with the MySQL server. Create a new username and password if you forgot or do not know the username. With phpMyAdmin, this is very easy to create a new username with password.
2. PHP use mysql_select_db function to select a MySQL database as shown below:
<?php
// select database
$db = mysql_select_db("$dbname") or die ("Couldn't select database.");
?>
Run the codes. If the connection is successful, no error message will appear.
Most PHP web designers will save the above codes as a configure file. This allow the same codes to be used again.
Let’s save the above codes as a single file (config.php).
Using PHP Peform Query on MySQL Database Table
We already learned how to us PHP connect with MySQL server and select a MySQL database. It’s time to see how to perform query on MySQL database tables.
In this example, we do a query of the “member” table and print out the bonus of the user. Let’s see the codes:
<?
// username and password
// Usually passed with the following format
// $username = $_POST["username"];
// $password = $_POST["password"];
$username = "alex";
$password = "alexabc";
// connect with MySQL database
include "config.php";
// Try to print out something
echo "<h1>Welcome Back to Member Area!</h1>";
echo "The value of \$link is: " . $link . "<br />";
/////////////////////////////////////////////////////////
// Perform query on MySQL database table //
////////////////////////////////////////////////////////
// create SQL
$sql = "SELECT * FROM $table_name where username = '$username' and password = '$password' " ;
// execute SQL query and get result
$sql_result = mysql_query($sql, $link) or die ("Couldn't execute SQL query.");
//Fetch data from the query result
$row = mysql_fetch_array($sql_result);
//Get the data from each result column
$bonus = $row["bonus"];
//Print the data
echo "Your current bonus is: " . $bonus;
// free resources
mysql_free_result($sql_result);
?>
Here’s how the codes work:
1. To simplify the codes, we assigned the username and password at the beginning.
// username and password
// Usually passed with the following format
// $username = $_POST["username"];
// $password = $_POST["password"];
$username = "alex";
$password = "alexabc";2. Use PHP SELECT statement to select data from a database.
/////////////////////////////////////////////////////////
// Perform query on MySQL database table //
////////////////////////////////////////////////////////
// create SQL
$sql = "SELECT * FROM $table_name where username = '$username' and password = '$password' " ;
3. Use PHP mysql_query function to execute SQL query as shown below:
// execute SQL query and get result
$sql_result = mysql_query ($sql, $link) or die ("Couldn't execute SQL query.");If everything is correct, the query to MySQL database table is finished. For some MySQL query, the process is end here. The process is actually very simple.
4. For most MySQL query, the results will usually be displayed on the screen. In this example, we need to read the data from the member table, and the results need to be displayed on screen.
Fetch the data from the record:
//Fetch data from the query result
$row = mysql_fetch_array($sql_result);
//Get the data from each result column
$bonus = $row["bonus"];
5. The coming step is to display the query result (for example, bonus) on the screen.
//Print the data
echo "Your current bonus is: " . $bonus;6. The final step is to free resources of the hosting server.
This step is usually not required. As mentioned in PHP manual that mysql_free_result() only needs to be called if you are concerned about how much memory is being used for queries that return large result sets. All associated result memory is automatically free at the end of the script’s execution.
// free resources
mysql_free_result($sql_result);That’s it! This is the end of using PHP interaction with MySQL Database.
Leave a Reply