Php Database Notes

Php Database Notes
— print (last updated: Mar 11, 2009) — print

Php PDO API

The Php PDO (Php Data Objects) API is one of several choices for APIs connecting Php to a MySQL DBMS. These are alternatives:

The original MySQL function-oriented API.
The MySQLi object-oriented/function-based API.
The PEAR (Php Extension and Application Repository) APIs.

The potential advantage to the first two alternatives is that they are MySQL-specific, and thus are possibly better for dealing with MySQL DBMS, per se. On the other hand, the down side is that using such MySQL-specific APIs "hardwires" your application to MySQL (which may be OK). The PDO API is quite similar to Java's JDBC. In particular,

it's object-oriented
it's DBMS-independent
it has support for prepared statements
it can throw exceptions on error

The PEAR APIs are also object-oriented with effectively the same features of PDO. The problem with the PEAR APIs is that their installation and usage is less simple and that (as far as I know) they do not support exception generation. The point about exception handling is noteworthy because virtually every SQL-based execution has the possibility of generating an error. Without exception generation, each statement must be tested for errors and dealt with immediately after execution.

Database Connection

Like JDBC, PDO connects to a data source, meaning a specific database in a MySQL DBMS. The connection information is provided as a string referred to as a data source name. Assuming that the MySQL DBMS is on localhost and that the database is test, the data source name would be:

$dsn = "mysql:host=localhost;dbname=test"

Additional property=value pairs can be added as necessary. We will use the guest user with empty password via the variables:

$user      = "guest";
$password  = "";

Given these variables, a connection is created by this call:

$cx = new PDO($dsn, $user, $password);

The connection creation execution is the only one which automatically generates an exception on failure. JDBC-like exception generation for all subsequent SQL executions can be achieved by setting the error mode:

$cx->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );

The two other error mode values are these:

PDO::ERRMODE_SILENT (this is the default: do nothing except set an error code),
PDO::ERRMODE_WARNING (generate warnings but keep on going).

With either of these two settings, one must test the value of the PDO::errorCode() function and do the necessary operations if an error is discovered.

Simple query execution

Simple execution of non-SELECT statements can be done using the exec member function like this:

$sql = "some SQL operation like CREATE, UPDATE, INSERT";
$numrows = $cx->exec($sql);

The output, $numrows, is the number of affected rows.

For SELECT statements, the simple one-liner uses the query function:

$sql = "SELECT ... FROM ...";
$res = $cx->query( $sql );

The return value, $res, can be used to deliver the row data in a number of ways. Single rows from $res can be retrieved, one at a time, via:

$row = $res->fetch();

The $row object is an array which holds the field data in both indexed and associative form. For example, if the first field in the SELECT statement were "id", then both of these are defined and equal:

$row[0]   and   $row['id']

There is also the option to retrieve all the rows as an array of arrays:

$all = $res->fetchAll();

In many cases we want to process the rows as they are retrieved, and so this is quite common:

foreach ($res as $row) {
  // do something with the indexed/associative array $row
}

Prepared statements

Prepared statements serve to make repeated usage more efficient and to provide "variable-insertion slots" to insert (possibly complicated) values. The execution format is this:

$sql = "...";
$sth = $cx->prepare( $sql );
$sth->execute( ... );

This prepare/execute style permits both SELECT and non-SELECT statements. For non-SELECT statements we would be likely to use it like this:

$num_affected_rows = $sth->execute( ... );

For SELECT statements, after $sth->execute( ... ), the $sth object acts like $res in the section above, namely, we can do:

$row = $sth->fetch();                    // one row at a time
$rows = $sth->fetchAll();                // all rows
foreach ($sth as $row) { /* .. */ }      // loop through the rows

PDO allows the creation of variable-insertion slots in a number of different ways, including the JDBC style using the ? character. Associating a ? position to a value can also be done in two ways, one in a manner similar to JDBC's set<type> function. However, the simplest way is to pass them as an array in the execute function like this:

$sql = "INSERT INTO my_table VALUES (?, ?, ?)";
$sth = $cx->prepare( $sql );
$sth->execute( array($first, $second, $third) );

You have to be careful to always pass an array, i.e., not this:

$sth->execute( $first, $second, $third );

MySQL-oriented APIs

In this section we'll look at the original function-oriented MySQL and the newer MySQLi APIs. Assume these definitions to access our MySQL data source:

$host      = "localhost";
$database  = "test";
$user      = "guest";
$password  = "";

The table below illustrates the code for creating a connection to this data source.

MySQL

$cx = mysql_connect($host, $user, $password) or die(mysql_error());

mysql_query("USE $database", $cx) or die(mysql_error($cx));

MySQLi

$cx = new mysqli($host, $user, $password);

if (mysqli_connect_errno()) die(mysqli_connect_error());

$cx->query("USE $database") or die($cx->error);

As mentioned above, both of these APIs permit a connection-level interface to the MySQL DBMS without specifying the database which is very useful if you want to write some web application specific to MySQL. In these cases to get to the data source (i.e., the database), the MySQL USE statement is employed.

After connecting with the MySQL API, all SQL executions use the mysql_query function, and typically refer directly or indirectly to the opened database connection, $cx. However the calls need not mention $cx explicitly, which is initially confusing. For example, the USE statement can be rewritten without the $cx parameter as follows:

mysql_query( "USE $database" ) or die(mysql_error());

This is why $cx is the second parameter, whereas one might think it should be the first. The reason is that Php, although it doesn't allow function overloading, does permits default values for "later" function parameters. In this case, omitting the $cx makes Php use, by default, the most recent open connection.

The MySQLi API permits either a function-call interface similar to the MySQL API where functions employ the "mysqli_" prefix. However, since the option is available, it is better to use the object-oriented approach as seen here. Note, however, that the connection error test must use a regular function call. Like the MySQL API, all SQL operations are delivered by $cx->query(...) executions.

Execution with no results

Assume we have a connection object, $cx, and a string $sql_op defining an SQL operation with no parameters and no expected results. Here are the calls:

MySQL	mysql_query($sql_op, $cx) or die(mysql_error($cx)); echo "num affected rows: ", mysql_affected_rows();
MySQLi	$cx->query( $sql_op ) or die($cx->error); echo "num affected rows: ", $cx->affected_rows;

Quote-escaping of inserted values

The MySQL API has no prepared statements; all must be constructed by-hand from the specific values. MySQLi purports to have prepared statements, but earlier tests I conducted indicated that the usage was neither consistent across different platforms, nor is capable of handling binary data (that problem may not be resolved).

In any case, we can get around the lack of prepared statements by using the addslashes function to insert necessary escape characters (i.e., "\") into a string (text or binary!). Here is how we use it:

$mysql_ready_value = addslashes($raw_value);
$sql_op = "...  '$mysql_ready_value' ...";

then, in MySQL:

mysql_query($sql_op, $cx) or die(mysql_error($cx));

and in MySQLi:

$cx->query($sql_op, $cx) or die($cx->error);

Accessing the result set for SELECT statements

SQL statements which generate results (typically the SELECT statement, although the same is true of SHOW, DESCRIBE and EXPLAIN statements) are handled as follows:

MySQL

$res = mysql_query($sql_op, $cx) or die(mysql_error());
echo "rows returned: ", mysql_num_rows($res);

Then,

while( $row = mysql_fetch_row($res) ) {
  // $row is an array of values corresponding to selected fields
}

while( $map = mysql_fetch_assoc($res) ) {
  // $map['column_name'] = the data in selected column
}

MySQLi

$res = $cx->query($sql_op) or die($cx->error);
echo "rows returned: ", $res->num_rows;

Then,

while( $row = $res->fetch_row() ) {
  // $row is an array of values corresponding to selected fields
}

while( $map = $res->fetch_assoc() ) {
  // $map['column_name'] = the data in selected column
}