Приглашаем посетить
Тургенев (turgenev-lit.ru)

Section 14.1.  Using MySQL with PHP

Previous
Table of Contents
Next

14.1. Using MySQL with PHP

Working with MySQL through PHP is easy, as long as you have a working knowledge of SQL. This book does not attempt to teach SQL; if you are new to it, you should stop reading now, purchase a book on SQL, and then return after having read it.

14.1.1. Connecting to a MySQL Database

The mysql_connect( ) and mysql_select_db( ) functions connect to a database, then select a working database for use in the connection. The former usually takes three arguments, which are the IP address of a MySQL server to connect to, the username you wish to log on as, and the password for that username, like this:

    mysql_connect("db.hudzilla.org", "username", "password");

Future examples in this book will always use the username "phpuser" and the password "alm65z"; choose something more secure in your own scripts.

By default, the MySQL queries you run in PHP will be executed on the most recent connection you open in your script. Each script needs to open its own database connection through which to execute its database queries; although, by using a persistent connection, they can be made to share connections. This is discussed later in this chapter.

The first parameter in mysql_connect( ) can either be an IP address or a hostname. Most operating systems also allow you to use "localhost" as the local computer and have MySQL connect directly through a local socket. Alternatively, you can specify 127.0.0.1, which is also the local computer, and have MySQL connect through TCP/IP, which is a little slower. To connect to a remote server, just enter either the hostname (e.g., www.microsoft.com) or the IP address (e.g., 212.113.192.101) as the first parameter, and your data will be sent transparently over the Internet.

Once you have a connection open, call mysql_select_db( )it takes just one argument, which is the name of the database you wish to use. Once you select a database, all queries you run are on tables in that database until you select another database, so it is like the USE statement in MySQL. Examples in this book will always use the database "phpdb"again, you should change this for your own purposes, for security reasons.

Like mysql_connect( ), you generally use this function only once per script. Once both are done, you have a connection to your database with a database selectedyou are all set to perform queries.

    $connection = mysql_connect("localhost", "phpuser", "alm65z");
    if ($connection) {
            $db = mysql_select_db("phpdb");
            if (!$db) print "Failed to select 'phpdb'.\n";
    } else {
            print "Failed to connect to database.\n";
    }

Section 14.1.  Using MySQL with PHP

Once you are connected, you can use the function mysql_ping( ) to check whether the server is alive. It automatically uses the most recently opened database connectionso you need not pass it any parametersand returns true if the server was contacted or false if the connection appears to be lost.


The last two parameters aren't used all that often, but are worth knowing about. Calling mysql_connect( ) for the first time will open a new connection to the MySQL server, but calling it again in the same script, with the same arguments as the first call, will just return the previous connection. If you specify parameter four as true (or 1, as is most common), PHP will always open a new connection each time you call mysql_connect( ).

The last parameter allows you to specify additional connection options, of which the only really useful one is MYSQL_CLIENT_COMPRESS, which tells the server that it may use data compression to save network transfer time. This is a smart move if your web server and database server are on different machines.

14.1.2. Querying and Formatting

The majority of your interaction with MySQL in PHP will be done using the mysql_query( ) function, which takes the SQL query you want to perform as its parameter. It will then perform that query and return a special resource known as a MySQL result index, which contains a pointer to all the rows that matched your query. "Result index" is nothing more than a fancy term for a MySQL resource type, but you will see it used in MySQL error messages.

This result index is the return value of mysql_query( ), and you should save it in a variable for later use. Whenever you want to extract rows from the results, count the number of rows, or perform other operations on the results from the query, you need to use this value.

One other helpful function is mysql_num_rows( ), which takes a result index as its parameter and returns the number of rows inside that resultthis is the number of rows that matched the query you sent in mysql_query( ). With the two together, we can write a basic database-enabled script:

    mysql_connect("localhost", "phpuser", "alm65z");
    mysql_select_db("phpdb");
    $result = mysql_query("SELECT * FROM usertable");
    $numrows = mysql_num_rows($result);
    print "There are $numrows people in usertable\n";

That captures the return value of mysql_query( ) inside $result, then uses it on the very next line. This MySQL result index is used often, so it is important to keep track of it. The exception to this is when you are executing a write query in MySQL, where you don't want to know the result.

The mysql_query( ) function will return false if the query is syntactically invalid (if you have used a bad query). This means that very often, it is helpful to check the return value even if you are writing data: if the data was not written successfully, mysql_query( ) will tell you so with the return value. Similarly, an empty result will return true, which may mean you executed a dumb query by accidentsomething like SELECT * FROM people WHERE Age > 500 will return no rows (and hence, true) unless you're programming a fantasy adventure!

14.1.3. Disconnecting from a MySQL Database

It is not necessary to explicitly disconnect from your MySQL server or to free the space allocated to your SQL results by hand. However, if you have a popular script that takes more than five seconds to execute, you should do all you can to conserve resources. Therefore, it is smart to explicitly free up your MySQL resources rather than wait to let PHP do it on your behalf.

There are two functions for this purpose: mysql_free_result( ) and mysql_close( ). The first is used to deallocate memory that was used to store the query results returned by mysql_query( ). If you have big queries being returned, you should be calling mysql_free_result( ) if there is much time between you finishing with the data and your script finishing execution. Here is how it works:

    $result = mysql_query("SELECT * FROM really_big_table;");
    // ...[snip]...
    mysql_free_result($result);

The purpose of mysql_close( ) is to save computer resources, but another important reason for using it is that there is a limited number of connections that a MySQL server can accept. If you have several clients holding connections open for no reason, then the server may well need to turn away other clients who are waiting to connect to the database. The actual number of connections a database server can accept is set by the database administrator, but if you plan to have no more than 100, you should be OK. As with mysql_free_result( ), it is good to call mysql_close( ) if you think there will be some time between your last database use and your script ending.

Using mysql_close( ) is simple: you do not need to supply any parameters to it, as it will automatically close the last-opened MySQL connection. Of course, if you captured the return value from mysql_connect( ), you can supply that to mysql_close( ) and it will close a specific connectionhandy if you have multiple MySQL connections open for some reason.

Here's a simple example of mysql_close( ) in action:

    mysql_connect("localhost", "phpuser", "alm65z");
    mysql_select_db("phpdb");
    // ...[snip]...
    mysql_close( );

In the example above, the call to mysql_close( ) is not neededthe script ends immediately after, and any open MySQL connections that aren't permanent connections will be closed automatically.

14.1.4. Reading in Data

To read data from a MySQL result index, use the mysql_fetch_assoc( ) function. This takes one row from a MySQL result and converts it to an associative array, with each field name as a key and the matching field value as the value. The function increments its position each time it is called, so calling it for the first time reads the first row, the second time the second row, etc., until you run out of rowsin which case, it returns false. In this respect, it works like the each( ) array function we looked at previously.

To extend our previous script to output nicely formatted data, we would need to make it use mysql_fetch_assoc( ) to go through each row returned by the query, printing out all fields in there:

    mysql_connect("localhost", "phpuser", "alm65z");
    mysql_select_db("phpdb");
    $result = mysql_query("SELECT * FROM usertable");

    if ($result && mysql_num_rows($result)) {
            $numrows = mysql_num_rows($result);
            $rowcount = 1;
            print "There are $numrows people in usertable:<br /><br />";

            while ($row = mysql_fetch_assoc($result)) {
                    print "Row $rowcount<br />";

                    foreach($row as $var => $val) {
                            print "<B>$var</B>: $val<br />";
                    }

                    print "<br />";
                    ++$rowcount;
            }
    }

Figure 14-1 shows how that script looks when viewed through a web browser.

Figure 14-1. The contents of our table printed out through PHP
Section 14.1.  Using MySQL with PHP


That script connects to the local MySQL database server and selects the phpdb database for use. It then runs a basic query on our usertable table and stores the result index in $result. The next line checks that $result is true and that there is at least one row in thereif so, it stores the number of rows in $numrows, sets the $rowcount variable to 1, then outputs the number of rows it found.

The next section is the new part: $row is set to the return value of mysql_fetch_assoc( ), which means it will be set to an array containing the data from the next row in the result. If mysql_fetch_assoc( ) has no more rows to return, it sends back false and ends the while loop. Each time we have a row to read, $rowcount is outputted and then the script goes through the array stored in $row (sent back from mysql_fetch_assoc( )), outputting each key and its value.

Finally, $rowcount is incremented, and the while loop goes around again.

Section 14.1.  Using MySQL with PHP

As an alternative to mysql_fetch_assoc( ), many programmers use mysql_fetch_array( ). The difference between the two is that, by default, mysql_fetch_array( ) returns an array of the row data with numerical field indexes (i.e., 0, 1, 2, 3) as well as string field indexes (i.e., Name, Age, etc.). Unless you need both indexes, stick with mysql_fetch_assoc( ).


14.1.5. Mixing in PHP Variables

Because the parameter for mysql_query( ) is a string, you can use variables as you would in any other string. For example:

    $result = mysql_query("SELECT ID FROM webpages WHERE Title = '$SearchCriteria';");
    $numhits = mysql_num_rows($result);
    print "Your search for $SearchCriteria yielded $numhits results";

You can use PHP variables wherever you want inside SQL queries, as long as you end up with a valid SQL query; otherwise, mysql_query( ) will return false. For example:

    function simplequery($table, $field, $needle, $haystack) {
            $result = mysql_query("SELECT $field FROM $table WHERE
                     $haystack = $needle LIMIT 1;");

            if ($result) {
                    if (mysql_num_rows($result)) {
                            $row = mysql_fetch_assoc($result);
                            return $row[$field];
                    }
            } else {
                    print "Error in query<br />";
            }
    }

That function allows you to pass in the name of the table you want to read, the field you are interested in, and the criteria it should match. Then it executes the appropriate query and sends the requested value back as its return value. This function can, therefore, be used like this:

    $firstname = simplequery("usertable", "firstname", "ID", $UserID);

The advantage to this is that you can program all sorts of error checking into simplequery( ) without making your scripts any more cluttered to read.

Although mixing PHP variables into your MySQL calls is powerful, you must be careful not to allow your users to abuse your scripts to hack into your systems. The first defense in this fight is the function mysql_escape_string( ), which is designed to make PHP variables more safe when used inside MySQL queries. To use this function, pass in the string that you wish to make safer, and it will return the new value. The function works by escaping all potentially dangerous characters in the string you pass in, including single quotesbe wary about using this function in combination with addslashes( ).

14.1.6. Reading Auto-Incrementing Values

When creating your MySQL tables, you can specify fields as INT AUTO_INCREMENT PRIMARY KEY, which means that MySQL will automatically assign increasingly higher integers to the field as INSERT queries are sent.

There are two ways to read the last-used auto-increment value: using a query or calling a function. The query option relies on the special MAX( ) function of MySQL. As MySQL will assign increasingly higher numbers to the ID field, the way to find the most recently assigned number is to run code like this:

    mysql_query("SELECT MAX(ID) AS ID FROM dogbreeds;");

The smart alternative is to use the function mysql_insert_id( ), which will return the last ID auto-inserted by the current connection. There is a subtle difference there, and one that makes it important enough for you to learn both methods of retrieving auto-incrementing values . The difference lies in the fact that mysql_insert_id( ) returns the last ID number that MySQL issued for this connection, regardless of what other connections are doing. Furthermore, mysql_insert_id( ) only stores one valuethe last ID number that MySQL issued for this connection on any table. On the other hand, using the SQL query allows you to check the very latest ID that has been inserted, even if you have not run any queries or if it has been 20 minutes since your last query. Furthermore, you can use the query on any table you like, which makes it even more useful.

14.1.7. Unbuffered Queries for Large Data Sets

Using mysql_query( ) for large queries has several serious disadvantages:

  • PHP must wait while the entire query is executed and returned before it can start processing.

  • In order to return the whole result to PHP at once, all the data must be held in RAM. Thus, if you have 100MB of data to return, the PHP variable to hold it all will be 100MB.

The disadvantages of mysql_query( ) are the advantages of mysql_unbuffered_query( ), which also queries data through SQL:

  • The PHP script can parse the results immediately, giving immediate feedback to users.

  • Only a few rows at a time need to be held in RAM.

One nice feature of mysql_unbuffered_query( ) is that, internally to PHP, it is almost identical to mysql_query( ). As a result, you can almost use them interchangeably inside your scripts. For example, this script works fine with either mysql_query( ) or mysql_unbuffered_query( ):

    <?php mysql_connect("localhost", "php", "alm65z");
            mysql_select_db("phpdb");
            $result = mysql_unbuffered_query("SELECT ID, Name FROM conferences;");

            while ($row = mysql_fetch_assoc($result)) {
                    extract($row, EXTR_PREFIX_ALL, "conf");
                    print "$conf_Name\n";
            }
    ?>

Before you rush off to make all your queries unbuffered , be aware that there are drawbacks to using mysql_unbuffered_query( ) that can make it no better than mysql_query( ):

  • You must read all rows from the return value, as MySQL will not allow you to run fresh queries until you have done so. If you're thinking of using this as a quick way to find something and then stop processing the rows part of the way through, you're way off tracksorry!

  • If you issue another query before you finish processing all the rows from the previous query, PHP will issue a warning. SELECTs within SELECTs are not possible with unbuffered queries .

  • Functions such as mysql_num_rows( ) return only the number of rows read so far. This will be 0 as soon as the query returns, but as you call mysql_fetch_assoc( ), it will increment until it has the correct number of rows at the end.

  • Between the time the call to mysql_unbuffered_query( ) is issued and your processing of the last row, the table remains locked by MySQL and cannot be written to by other queries. If you plan to do time-consuming processing on each row, this is not good.

If you're not sure which of the two is best, use mysql_query( ).


Previous
Table of Contents
Next