Difference between revisions of "Using PHP with MySQL"

Revision as of 22:21, 1 February 2016

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 generates 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 that 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 using 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 useful 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 database 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 fields 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 example 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 manipulate 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 formidable 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 MySQL and PHP does the rest.