Chapter 11: MySQL Database…

Using MySQL

This chapter assumes that you already have MySQL installed on your web server and that PHP has the MySQL module loaded. For information on installing MySQL, see http://dev.mysql.com/doc/mysql/en/Installing.html.

PHP 5 introduced the mysqli extension, which can take advantage of new functionality in MySQL version 4.1 and higher and can also be used in an object-oriented manner. This tutorial concentrates on the classic mysql extension, because it is still the version offered by many web hosting providers and remains available in PHP 5.

Generally speaking, if you want to use mysqli instead of the classic mysql extension described in this chapter, most function names are prefixed mysqli rather than mysql, but they behave in a similar way. Refer to the online documentation at www.php.net/mysqli for more information.

You can connect to a MySQL database by using the mysql_connect function. Three arguments define your connection parametersthe hostname, username, and password. In many cases, the MySQL server will be running on the same machine as PHP, so this value is simply localhost. A typical mysql_connect statement may look like the following:

$db = mysql_connect(“localhost”, “chris”, “mypassword”);

The mysql_connect function returns a database link identifier, which was assigned to $db in the previous example. This resource is used as an argument to the other MySQL functions.

Notice that the connection parameters given to mysql_connect do not include a database name. In fact, selecting the database is a separate step after you are connected to a MySQL server; to do it, you use the mysql_select_db function. For example, the following statement selects mydb as the current database:

mysql_select_db(“mydb”, $db);

After mysql_select_db has been called, every subsequent SQL statement passed to MySQL will be performed on the selected database.

When you are finished using MySQL in a script, you close the connection and free up its resources by using mysql_close, like this:

mysql_close($db);

 

Create New Database Table

The following code executes a CREATE TABLE SQL statement on the MySQL database for $db:

$sql = “CREATE TABLE mytable (col1 INT, col2 VARCHAR(10))”; mysql_query($sql, $conn);

If you run a script that contains these statements in your web browser and check your MySQL database, you will find that a new table called mytable has been created.

All types of SQL statement can be executed through mysql_query, whether they alter the data in some way or fetch a number of rows.

Commands That Change a Database

Earlier in this chapter you saw an example of a CREATE TABLE statement. Other Data Definition Language (DDL) statements can be executed in a similar fashion, and, provided that no errors are encountered, they perform silently. You will learn about error handling later in this chapter.

When executing a DELETE, INSERT, or UPDATE statementa subset of SQL known as the Database Manipulation Language (DML)a number of rows in the table may be affected by the query. To find out how many rows are actually affected, you can use the mysql_affected_rows function. The following example shows how to do this with a simple UPDATE statement:

$sql = “UPDATE mytable SET col2 = ‘newvalue’ WHERE col1 > 5”; mysql_query($sql, $conn); echo mysql_affected_rows($conn) . ” row(s) were updated”;

The argument to mysql_affected_rows is the database link identifier, and a call to this function returns the number of rows affected by the most recent query. The number of rows affected by this UPDATE statement is not necessarily the number of rows matching the WHERE clause. MySQL does not update a row if the new value is the same as the one already stored.

Fetching Queried Data

The SELECT statement should return one or more rows from the database, so PHP provides a set of functions to make this data available within a script. In order to work with selected data, you must assign the result from mysql_query to a result resource identifier, as follows:

$res = mysql_query($sql, $db);

You cannot examine the value of $res directly. Instead, you pass this value to other functions to retrieve the database records.

You can use the function mysql_result to reference a data item from a specific row and column number in the query result. This is most useful when your query will definitely only return a single valuefor instance, the result of an aggregate function.

The following example performs a SUM operation on the elements in a table column and displays the resulting value onscreen:

$sql = “SELECT SUM(col1) FROM mytable”; $res = mysql_query($sql, $conn); echo mysql_result($res, 0, 0);

The three arguments to mysql_result are the result resource identifier, a row number, and a column number. Numbering for both rows and columns begins at zero, so this example finds the first row in the first column in the result set. In fact, because of the nature of aggregate functions, you can be sure that there will always be only a single row and column in the result of this query, even if there are no records in the table. An attempt to access a row or column number that does not exist will result in an error.

The function mysql_num_rows returns the number of rows found by the query, and you can use this value to create a loop with mysql_result to examine every row in the result. The following code shows an example of this:

$sql = “SELECT col1, col2 FROM mytable”; $res = mysql_query($sql, $db); for ($i=0; $i < mysql_num_rows($res); $i++) {    echo “col1 = ” . mysql_result($res, $i, 0);    echo “, col2 = ” . mysql_result($res, $i, 1) . “<br>”; }

With the query used in this example, because the column positions of col1 and col2 are known, you can use mysql_result with a numeric argument to specify each one in turn.

Fetching Full Rows of Data

PHP provides a convenient way to work with more than one item from a selected row of data at a time. By using mysql_fetch_array, you can create an array from the query result that contains one element for each column in the query.

When you call mysql_fetch_array on a result resource handle for the first time, an array is returned that contains one element for each column in the first row of the data set. Subsequent calls to mysql_fetch_array cause an array to be returned for each data row in turn. When there is no more data left to be fetched, the function returns FALSE.

You can build a very powerful loop structure by using mysql_fetch_array, as shown in the following example:

$sql = “SELECT col1, col2 FROM mytable”; $res = mysql_query($sql, $conn); while ($row = mysql_fetch_array($res)) {    echo “col1 = ” . $row[“col1”];    echo “, col2 = ” . $row[“col2”] . “<br>”; }

Each row of data is fetched in turn, and in each pass of the loop, the entire row of data is available in the array structure, without any further function calls being necessary.

The array contains the row’s data, using elements with both numeric and associative indexes. In the previous example, because you know that col1 is the first column selected, $row[“col1”] and $row[0] contain the same value.

This mechanism provides a method of sequential access to every row returned by a query. Random access is also available, and by using the function mysql_data_seek, you can specify a row number to jump to before the next mysql_fetch_array is performed.

To jump to the tenth row, you would use the following (remember that the numbering begins at zero, not one):

mysql_data_seek($res, 9);

It therefore follows that to reset the row position to the start of the data set, you should seek row zero:

mysql_data_seek($res, 0);

If you attempt to call mysql_data_seek with a row number that is higher than the total number of rows available, an error occurs. You should check the row number against the value of mysql_num_rows to ensure that it is valid.

To skip to the last row of a data set, you call mysql_data_seek($res, mysql_num_rows($res)-1). The number of the last row is one less than the total number of rows in the result.

However, the result can usually be achieved more easily by specifying reverse sorting in an ORDER BY clause in your SQL and selecting the first row instead.

SQL Errors

When there is an error in a SQL statement, it is not reported right away. You should check the return value from mysql_query to determine whether there was a problemit is NULL if the query has failed for any reason. This applies to DDL and DML statements as well as to SELECT queries.

The following example tries to perform an invalid SQL statement (the table name is missing from the DELETE command):

$sql = “DELETE FROM”; $res = mysql_query($sql, $db); if (!$res) {    echo “There was an SQL error”;    exit; }

If you want to find out why a call to mysql_query failed, you must use the mysql_error and mysql_errno functions to retrieve the underlying MySQL warning text and error code number. A link resource argument can be provided but is required only if you have two or more open MySQL connections in the script:

if (!$res) {    echo “Error ” . mysql_errno() . ” in SQL “;    echo “<PRE>$sql</PRE>”;    echo mysql_error();    exit; }

When you’re debugging SQL, it is useful to see the query that was attempted alongside the error message, particularly if your query uses variable substitutions. This is easy to do if the query is stored in a variablesuch as $sql used throughout this chapter rather than given directly as an argument to mysql_query.

If you do not trap SQL errors in script, PHP will continue to execute until an attempt is made to use the failed result resource. You will see an error message similar to the following if, for instance, mysql_result is called with an invalid $res value:

Warning: mysql_result(): supplied argument is not a valid MySQL result resource in /home/chris/mysql.php on line 8

This error does not give any indication of what the problem was, or even when in the script it occurred. The line number given is the line of the mysql_result call, not mysql_query, so you have to search upward in the script to find the root of the problem.

Connection Errors

If an error occurs during connection to a MySQL database, a PHP error is displayed onscreen, similar to the following, which were caused by an invalid password and a mistyped hostname, respectively:

Warning: mysql_connect(): Access denied for user ‘root’@’localhost’   (using password: YES) in /home/chris/connect.php on line 3Warning: mysql_connect(): Unknown MySQL server host ‘local-host’   (1) in /home/chris/connect.php on line 3

These warnings are generated by PHP and are adequately descriptive. If you want, you can view the actual MySQL error message and error code by using mysql_error and mysql_errno.

The following code checks that a connection has been successful before continuing, and it displays the reason for failure, if appropriate:

$db = mysql_connect(“localhost”, “chris”, “mypassword”); if (!$db) {   echo “Connection failed with error ” .           mysql_errno() . “<br>”;   echo “Warning: ” . mysql_error();   exit; }

Neither the PHP warning nor the message from mysql_error contains the password used when the reason for failure is an invalid logon attempt.