PHP Practical Tutorials | jQuery and Webpage Design Home

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.

PHP Session

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 be appeared.

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 final 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.