Accessing MySQL Using PHP


Share

Querying a MySQL Database with PHP


The reason for using PHP as an interface to MySQL is to format the results of SQL queries in a form visible in a web page. As long as you can log in to your MySQL installation using your username and password, you can also do so from PHP . However, instead of using MySQL’s command line to enter instructions and view output, you will create query strings that are passed to MySQL. When MySQL returns its response, it will come as a data structure that PHP can recognize instead of the formatted output you see when you work on the command line. Further PHP commands can retrieve the data and format it for the web page.



The Process


The process of using MySQL with PHP is:
1. Connect to MySQL.
2. Select the database to use.
3. Build a query string.
4. Perform the query.
5. Retrieve the results and output them to a web page.
6. Repeat Steps 3 through 5 until all desired data has been retrieved.
7. Disconnect from MySQL.



Creating a Login File


Most websites developed with PHP contain multiple program files that will require access to MySQL and will therefore need your login and password details. So, it’s sensible to create a single file to store these and then include that file wherever it’s needed. In below shows such a file, which I’ve called login.php.

    <?php // login.php
    $db_hostname = 'localhost'; 
    $db_database = 'publications'; $db_username = 'username';
    $db_password = 'password'; 
    ?>


The enclosing <?php and ?> tags are especially important for the login .php file in above example because they mean that the lines between can be interpreted only as PHP code. If you were to leave them out and someone were to call up the file directly from your website, it would display as text and reveal your secrets. However, with the tags in place, all they will see is a blank page . The file will correctly include in your other PHP files.

The $db_hostname variable will tell PHP which computer to use when connecting to a database. This is required because you can access MySQL databases on any computer connected to your PHP installation, and that potentially includes any host anywhere on the Web. However, the examples in this chapter will be working on the local server, so in place of specifying a domain such as mysql.myserver.com, you can just use the word localhost (or the IP address 127.0.0.1).

The database we’ll be using, $db_database, is the one called publications . The variables $db_username and $db_password should be set to the username and password that you have been using with MySQL.

Connecting to MySQL


Now that you have the login.php file saved, you can include it in any PHP files that will need to access the database by using the require_once statement. This has been chosen in preference to an include statement, as it will generate a fatal error if the file is not found. And believe me, not finding the file containing the login details to your database is a fatal error.

Also, using require_once instead of require means that the file will be read in only when it has not previously been included, which prevents wasteful duplicate disk accesses. below example shows the code to use.


    <?php require_once 'login.php';
    $db_server = mysql_connect($db_hostname, $db_username, $db_password);
    if (!$db_server) die("Unable to connect to MySQL: " . mysql_error()); 
    ?>



This example runs PHP’s mysql_connect function, which requires three parameters, the hostname, username, and password of a MySQL server. Upon success it returns an identifier to the server; otherwise, FALSE is returned. Notice that the second line uses an if statement with the die function, which does what it sounds like and quits from PHP with an error message if $db_server is not TRUE.

The die message explains that it was not possible to connect to the MySQL database, and—to help identify why this happened—includes a call to the mysql_error function. This function outputs the error text from the last called MySQL function.

The database server pointer $db_server will be used in some of the following examples to identify the MySQL server to be queried. Using identifiers this way, it is possible to connect to and access multiple MySQL servers from a single PHP program.



Selecting a database


Having successfully connected to MySQL, you are now ready to select the database that you will be using. Below example shows how to do this.


    <?php 
    mysql_select_db($db_database) 
    or die("Unable to select database: " . mysql_error());
    ?>

The command to select the database is mysql_select_db. Pass it the name of the database you want and the server to which you connected. As with the previous example, a die statement has been included to provide an error message and explanation, should the selection fail—the only difference is that there is no need to retain the return value from the mysql_select_db function, as it simply returns either TRUE or FALSE. Therefore, the PHP or statement was used, which means, “If the previous command failed, do the following.” Note that for the or to work, there must be no semicolon at the end of the first line of code.



Building and executing a query


Sending a query to MySQL from PHP is as simple as issuing it using the mysql_query function. Below example shows you how to use this function


     <?php 
    $query = "SELECT * FROM classics";
    $result = mysql_query($query);
    if (!$result) die ("Database access failed: " . mysql_error()); 
    ?>


First, the variable $query is set to the query to be made. In this case it is asking to see all rows in the table classics. Note that, unlike when using MySQL’s command line, no semicolon is required at the tail of the query. This is because the mysql_query function is used to issue a complete query, and cannot be used to query by sending multiple parts, one at a time. Therefore, MySQL knows the query is complete and doesn’t look for a semicolon.
This function returns a result that we place in the variable $result. Having used MySQL at the command line, you might think that the contents of < b>$result will be the same as the result returned from a command-line query, with horizontal and vertical lines, and so on. However, this is not the case with the result returned to PHP. Instead, upon success, $result will contain a resource that can be used to extract the results of the query. You’ll see how to extract the data in the next section. Upon failure, $result contains FALSE. So, the example finishes by checking $result. If it’s FALSE, it means that there was an error and the die command is executed.



Fetching a result


Once you have a resource returned from the mysql_query function, you can use it to retrieve the data you want. The simplest way to do this is to fetch the cells you want, one at a time, using the mysql_result function. Below example combines and extends the previous examples into a program that you can type in and run yourself to retrieve the returned results. I suggest that you save it in the same folder as login.php and give it the name query.php.


     <?php 
     require_once 'login.php'; $db_server = mysql_connect($db_hostname, $db_username, $db_password);
     if (!$db_server) die("Unable to connect to MySQL: " . mysql_error());
     mysql_select_db($db_database)    or die("Unable to select database: " . mysql_error());
    $query = "SELECT * FROM classics"; $result = mysql_query($query);
    if (!$result) die ("Database access failed: " . mysql_error());
       $rows = mysql_num_rows($result);
       for ($j = 0 ; $j < $rows ; ++$j) 
       {   
       echo 'Author: '   . mysql_result($result,$j,'author'); 
       echo 'Title: '    . mysql_result($result,$j,'title') ;  
       echo 'Category: ' . mysql_result($result,$j,'category');   
       echo 'Year: '     . mysql_result($result,$j,'year') ;   
       echo 'ISBN: '     . mysql_result($result,$j,'isbn') ;
       } 
     ?>



Fetching a row


        <?php 
        for ($j = 0 ; $j < $rows ; ++$j) {    $row = mysql_fetch_row($result); 
        echo 'Author: ' .     $row[0] ;
        echo 'Title: ' .      $row[1] ; 
        echo 'Category: ' .   $row[2] ; 
        echo 'Year: ' .       $row[3] ; 
        echo 'ISBN: ' .       $row[4] ;
        } 
    ?>
    



Closing a connection


         <?php 
         mysql_close($db_server); 
          ?>