Difference between revisions of "Using PHP with MySQL"

Revision as of 18:20, 13 June 2007

There is a TV commercial that shows what would happen to the world if we didn't have plastic. In the commercial objects like telephones, chairs and computers disappear before our eyes. The objective is to make us appreciate what would happen to our infrastructure without plastic. A similar analogy can be drawn to the need for databases on the world wide web. Without databases many of the web sites we rely on would quickly cease to function. It is not an exaggeration to suggest that databases form the heart of the web and the internet as we know it. Without some way to store and retrieve data the usefulness of the internet would be a greatly reduced.

One of the many advantages of PHP is the seemless way in which it integrates with the MySQL database. In this chapter we will take a close look at how to access information stored in a MySQL database from a PHP script and present that data to a user's web browser.

Before beginning it is important to note that MySQL is an advanced Relatation Database Management System (RDCMS), and as such, may be more sophisticated than is needed for every data storage need. In the next chapter (PHP and SQLite) we will look at a more lightweight solution that may meet less demanding needs.

This chapter assumes that you have MySQL installed and running on your server. There are many resources on the internet that can help you achieve this. The first place to start is the MySQL documentation.

Creating a MySQL User Account

The first task is to create a MySQL user account we can use for the purposes of this chapter. To set up as user account you will need to log into MySQL using an account that has suitable privilages. If you are unsure about which user account to use speak to your system administrator or refer to the MySQL documentation. In this example we will assume you have the root password:

mysql -p -u root
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10411 to server version: 5.0.27

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

Having logged into MySQL we can now create a new user called phptest. In the example below we assign the password mypassword. It is advised you use your own passowrd in place of this one:

mysql> GRANT ALL PRIVILEGES ON *.* TO 'phptest'@'localhost'
    -> IDENTIFIED BY '3579php' WITH GRANT OPTION;
Query OK, 0 rows affected (0.07 sec)

We now have a user account we can use to create a sample database table and can log into MySQL using this account and password:

$ mysql -p -u phptest
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10476 to server version: 5.0.27

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

Creating and Select MySQL Database

The next step is to create a database to store the data we will use in our PHP database example. We will call the database PHPsampleDb as follows:

mysql> CREATE DATABASE PHPsampleDB;
Query OK, 1 row affected (0.00 sec)

We can then select the database using the USE command:

mysql> USE PHPsampleDB;
Database changed

Our database is now created and selected. Next we need to add a table to the database.

Creating a MySQL Database Table

For the purposes of our example we will create a very simple database table. It is going to contain just three fields, a customer name, email address and account number. We will call this table customer and create it as follows:

mysql> CREATE TABLE customer (name VARCHAR(30), email VARCHAR(30),
    -> account VARCHAR(20));
Query OK, 0 rows affected (0.01 sec)

Inserting Data into a MySQL Database Table

As the final step in setting our sample database we need to add some data to the table. This is achieved using tyhe INSERT command. We will add three initial rows to the table:

mysql> INSERT INTO customer VALUES ('Gregory House', '[email protected]', '12345678');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO customer VALUES ('Robert Chase', '[email protected]', '87654321');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO customer VALUES ('Lisa Cuddy', '[email protected]', '24688642');
Query OK, 1 row affected (0.00 sec)

We can now check that these records are set up correctly using the SELECT command:

mysql> SELECT * FROM customer;
+---------------+-----------------+----------+
| name          | email           | account  |
+---------------+-----------------+----------+
| Gregory House | [email protected] | 12345678 |
| Robert Chase  | [email protected] | 87654321 |
| Lisa Cuddy    | [email protected] | 24688642 |
+---------------+-----------------+----------+
3 rows in set (0.00 sec)

Connecting with PHP to a MySQL Server

Now that we have set up our MySQL database and entered some data it is time to look at using PHP to connect to the database so that we can start to query the database and add new data. The first step in our PHP script is to connect to our MySQL database server. This is achieved using the PHP mysql_connect() function. The mysql_connect() function creates a connection to the database server and returns a database resource handle. The function takes five optional arguments. The first is the address of the server hosting the database. This defaults to localhost:3306. The second argument is the user name to be used to connect to the database. The third argument is the password associated with the user name.

If a second call is made to mysql_connect() the default behavior is to return the handle from the first call. Setting the fourth argument overrides this behavior and generate a new handle.

The final argument represents the flags for the PHP client which works in the background to connect to the database. Options are MYSQL_CLIENT_COMPRESS, MYSQL_CLIENT_IGNORE_SPACE and MYSQL_CLIENT_INTERACTIVE.

To disconnect from the database use the msql_close() function which takes as a sole argument the database resource handle returned by mysql_connect().

We can now write a script which will connect us to our sample database (remember to modify the password to match the one you specified when creating the user account):

<?php

        $dbhandle = mysql_connect('localhost', 'phptest', '3579php');

        if ($dbhandle)
        {
                echo "Connected to MySQL Database<br>";
                mysql_close($dbhandle);

        } else {
                 echo "Unable to connect to MySQL Database<br>";
        }


?>

Selecting Records from a MySQL Database Using PHP

Now that we have connected to our MySQL database we can begin accessing the data in our table. To achieve this we need to first select the database we wish to use by calling the mysql_select_db() function, passing through the database name as an argument. This will return a database handle. We then need to construct a SQL SELECT statement which we will pass to the PHP mysql_query() function. This function takes the database handle (returned by mysql_select_db()) and the SQL query statement as arguments.

The mysql_query() function call places the results in an array which we can access using the mysql_fetch_array() function.

Bringing this all together gives us the following:

<?php

        $dbhandle = mysql_connect('localhost', 'phptest', '3579php');

        if ($dbhandle == false)
        {
                 die  ("Unable to connect to MySQL Database<br>");
        }

        $db = mysql_select_db('PHPsampleDB');

        if ($db == false)
        {
                 die  ("Unable to Select MySQL Database<br>");
        }

        $dbquery = 'SELECT * FROM customer';

        $dbresult = mysql_query ($dbquery, $dbhandle);

        if ($dbresult == false)
        {
                die  ("Unable to to perform query<br>");
        }

        while ($dbrow = mysql_fetch_array($dbresult, MYSQL_ASSOC))
        {
                print_r($dbrow);
                echo '<br>';
        }

        mysql_close($dbhandle);
?>

When loaded into a browser the following output will be generated:

Array ( [name] => Gregory House [email] => [email protected] [account] => 12345678 )
Array ( [name] => Robert Chase [email] => [email protected] [account] => 87654321 )
Array ( [name] => Lisa Cuddy [email] => [email protected] [account] => 24688642 )

Another way to fetch the data as an associative array [[see PHP Arrays) is to use the mysql_fetch_assoc(). We could, therefore, modify our while loop as follows:

        while ($dbrow = mysql_fetch_assoc($dbresult))
        {
                print_r($dbrow);
                echo '<br>';
        }

Adding Records to MySQL Database using PHP

To add records to a database using PHP we simply need to construct a new query string suing the SQL INSERT command and execute it using the mysql_query() function:

<?php

        $dbhandle = mysql_connect('localhost', 'phptest', '3579php');

        if ($dbhandle == false)
        {
                 die  ("Unable to connect to MySQL Database<br>");
        }

        $db = mysql_select_db('PHPsampleDB');

        if ($db == false)
        {
                 die  ("Unable to Select MySQL Database<br>");
        }
        $dbquery = "INSERT INTO customer VALUES ('James Wilson', '[email protected]', '00001111')";

        $dbresult = mysql_query ($dbquery, $dbhandle);

        if ($dbresult == false)
        {
                die  ("Unable to add record<br>");
        }

        $dbquery = "SELECT * FROM customer WHERE name = 'James Wilson'";

        $dbresult = mysql_query ($dbquery, $dbhandle);

        if ($dbresult == false)
        {
                die  ("Unable to perform query<br>");
        }

        while ($dbrow = mysql_fetch_Array($dbresult, MYSQL_ASSOC))
        {
                print_r($dbrow);
                echo '<br>';
        }
?>

The above script will result in the following output:

Array ( [name] => James Wilson [email] => [email protected] [account] => 00001111 )

Modifying and Deleting MySQL Records using PHP

Record can be similarly modified and deleted by constructing appropriate SQL DELETE and UPDATE commands and passing them through to the mysql_query() function. After the function has been called the mysql_affected_rows() function can be called to identify the number of rows affected by the change. mysql_affected_rows() accepts a single argument, the handle returned by the mysql_connect() function.

Using PHP to get Information about a MySQL Database

PHP provides a number of usefuil functions for obtaining information about a MySQL database. It is possible to obtain a list of fields in a table using the mysql_list_fields() function. This function accepts three arguments, the database name, the table name and datbase handle returned by mysql_connect().

The number of fields in a table can be obtained using the mysql_num_fields() function. This function takes the resource identifier returned by mysql_list_fields() as an argument.

Once you have obtained the resource identifier from mysql_list_fields() you can use mysql_field_name(), mysql_field_type(), and ''mysql_field_len() functions to get information about each field. All of these functions take the handle returned by mysql_list_fields() as the first argument and offset into the table of the field you wish to inspect. A for loop can be constructed using the result from mysql_num_fields() to iterate through all feilds as follows:

<?php

        $dbhandle = mysql_connect('localhost', 'phptest', '3579php');

        if ($dbhandle == false)
        {
                 die  ("Unable to connect to MySQL Database<br>");
        }

        $db = mysql_select_db('PHPsampleDB');

        if ($db == false)
        {
                 die  ("Unable to Select MySQL Database<br>");
        }

        $listhandle = mysql_list_fields ('PHPsampleDB', 'customer', $dbhandle);

        $numfields = mysql_num_fields ($listhandle);

        for ($i=0; $i<$numfields; $i++)
        {
                echo 'Name: ' . mysql_field_name($listhandle, $i) . '<br>';
                echo 'Type: ' . mysql_field_type($listhandle, $i) . '<br>';
                echo 'Length: ' . mysql_field_len($listhandle, $i) . '<br>';
        }

?>

When loaded into a browser the above exmaple will generate the following output:

Name: name Type: string Length: 30
Name: email Type: string Length: 30
Name: account Type: string Length: 20

Summary

Probably one of the most powerful features of PHP (next to ease of use) is the ease with which it is possible to access and manipluate MySQL databases from PHP scripts. Without database access many web sites would simply cease to function. Without the built in support for MySQL the task of developing database powered web applications would be formiddable task. As we have demonstrated in this chapter, PHP makes MySQL database access fast and easy. PHP essentially does all the work of communicating with the database server for us. All we need to do is write the SQL commands to pass to PHP and PHP does the rest.