Category: Technology

Since PHP 5.0 released a day or so ago, I’ve been seeing how various applications I work with do in the new environment. Porting straight to PHP 5.0 seems to work just fine for the applications tested, including my weblogging tool, WordPress.

Ah, but there’s more to upgrades than just a simple turning off of one PHP version and turning on another. PHP 5.0 includes– in addition to the full support for object implementation and improved XML manipulation–some very slick database toys.

Though PHP is used with MySQL for the most part, look for more work with SQLite, as it’s now bundled with PHP. This lightweight little C-based database engine is ready to go as soon as you need it; for quick database development, it’s hard to resist its simplicity.

However, my heart still belongs to MySQL, and with the new version of MySQL and the new improved MySQL interface extension in PHP 5.0, the love affair continues to grow.

MySQL 4.1 is currently a beta release and should only be downloaded by developers. I found it installed easily and works nicely with existing applications; this includes the existing MySQL interface used by most weblogging tools, such as WordPress, Movable Type, and so on.

However, MySQL 4.1 has one particular new goodie that’s going to make a world of difference in performance with existing applications and that’s support for prepared statements.

Now, Oracle has had this functionality since before PHP was born, but Oracle started out as the 300 pound baby, who has since been finding ways to keep all of its great functionality while trimming down both size and cost. MySQL on the other hand, started out as a cute 6 pounder who couldn’t do much, but is learning quickly.

Prepared statements are a very simple but powerful technology. What happens is you create a statement as a template, with placeholders for values that will change each time the statement is run. You can then run the statement as many times as you want, changing the parameters as needed. We webloggers with our natural born ease around and comfort with templates should find prepared statements to be both friendly and familiar. I just know that this particular technology is what’s going to finally bring those hesitant about working with databases over to the wild data side.

Following is a very quick example of using the prepare statement functionality, tested on one of the WordPress tables ported to the new environment. This code ran successfully using the newly released PHP 5.0, the latest release of MySQL 4.1, in a Windows 2000 environment, under Apache 1.3x.

<?php
$link = mysqli_connect(“localhost”, “user”, “userpass”, “wordpress”);

/* check connection */
if (mysqli_connect_errno()) {
printf(“Connect failed: %sn”, mysqli_connect_error());
exit();
}

// create a prepared statement
if ($stmt = mysqli_prepare($link, “SELECT option_name FROM wp_options WHERE option_id=?”)) {

// bind incoming parameter
mysqli_stmt_bind_param($stmt, “i”, $id);

// bind result variables
mysqli_stmt_bind_result($stmt, $name);

// set input param, execute query and fetch and print value
$id = 77;

mysqli_stmt_execute($stmt);
mysqli_stmt_fetch($stmt);
printf(“option for id %s is %sn”, $id, $name);

// repeat with new input param
$id = 93;

mysqli_stmt_execute($stmt);
mysqli_stmt_fetch($stmt);
printf(“option for id %s is %sn”, $id, $name);

// close statement
mysqli_stmt_close($stmt);
}
else
printf(“prep failed: %sn”, mysqli_error());

// close connection
mysqli_close($link);
?>

This code demonstrates the procedural use of the new mysqli PHP interface, since this will probably be more familiar. I’ll demonstrate the use of objects the next time I try out a little of the new PHP 5.0 code.

In the example, a connection to the database is made, using the function mysqli_connect. Once the link is established and we know no error has occurred, the next thing that happens in the code is creating the prepared statement.

Notice the use of the question mark in the statement? This question mark serves as a placeholder for the input parameters that will be bound to the statement later in the code. It’s not different than the use of templates within your weblogging tool – each of them is replaced by ‘real data’ when the page is accessed or generated.

The function mysql_prepare takes the SQL passed to it, parses it, compiles it and then stores in in the database in a binary format that makes passing the data back and forth between the database and the application more efficient – a bonus for this type of statement.

Next in the code, it’s a matter of binding input and output parameters. For this example, there’s one of each. The input parameter is an integer so in the mysqli_stmt_bind_param function call, a value of ‘i’ is passed as the second parameter to define the data type of “integer”. The first parameter is the prepared statement, and the third is the actual variable that will be bound to the input parameter.

(By ‘binding’ what I mean is that changes to the variable are reflected in placeholder position in the prepared statement, each time it is executed.)

Next, the output parameter is defined, using the function mysqli_stmt_bind_result.

To test the statement, I assigned a value of 77 to the input parameter, executed the statement using mysqli_stmt_execute, fetched the result with mysql_stmt_fetch, and printed both values out. The result is:

option for id 77 is weblogs_xml_url

To demonstrate the reusability of the prepared statement, I assigned a new value to $id, this time a value of 93. Again, the statement is executed, and the resulting value fetched and printed out along with the input parameter. The resulting line from this second run of the prepared statement is:

option for id 93 is blog_charset

The prepared statement will continue to reside at the database until the application goes out of scope (beyond the current operating environment), or until the statement is closed, whichever comes first.

How can something like prepared statements help out in a weblogging environment? For the weblogging application I use, it’s not unusual for us to have to access the posts or comments table more than once in order to process new comments or display or edit posts. In particular, because of my custom spam protection and moderation, I access both tables at least twice when a new comment is posted; with something like the prepared statement, I could improve the program readability, as well as efficiency. I may not get hundreds of comments a day, but as I said in the past – I’m a coding miser. The less work the application does, the happier I am. I and everyone who shares my machine.

However, before we all run out an upgrade to PHP 5.0 and MySQL 4.1, note that I could not complete the migration of WordPress to this new environment because one MySQLi function, mysqli_fetch_object, caused Apache 1.3x to throw a memory exception. That’s ’semi-crash’ for those of you who haven’t seen programs step all over each other’s toes in memory.

This is beta, new, in development code. Just the start of new things. But such great things. And I haven’t even covered all the new goodies coming out in MySQL 5.0.