Example 8-4 shows the backend page that allows administrators to add categories to the listing service. The input fields for adding a new record appear after a dump of the current data. The administrator fills in the form and presses the Add Category button, and the page redisplays with the new record. If any of the three fields are not filled in, the page displays an error message.

<?php
// display any records fetched from the database
// plus an input line for a new category
while ($row = $result->fetchRow()){echo "<tr><td>$row[0]</td><td>$row[1]</td><td>$row[2]</td></tr>n";
}
?>

When the administrator submits a new category, we construct a query to add the category to the database. Another query displays the table of all current categories. Figure 8-4 shows the page with five records loaded.

{mospagebreak title=Adding a Business}

Example 8-5 shows the page that lets a business insert data into the business and biz_categories tables. Figure 8-5 shows the form.

Figure 8-4. Category Administration page

Figure 8-5.The business registration page

In the confirmation page, the Add Business button is replaced by a link that will invoke a fresh instance of the script. A success message is displayed at the top of the page. Instructions for using the scrolling pick list are replaced with explanatory text.

As shown in Example 8-5, we build the scrolling list from a query to select all the cat
egories. As we produce HTML for each of the results from that query, we also check to see whether the current category was one of the categories submitted for the new business. If it was, we add a new record to the
biz_categories
table.

Example 8-6 shows a page that displays the information in the database. The links on the left side of the page are created from the categories table and link back to the script, adding a category ID. The category ID forms the basis for a query on the businesses table and the biz_categories table.

There is another process that you can use to access database information. It is a database extension called PDO (PHP Data Objects), and the php.net web site had this to say about it:

The PHP Data Objects (PDO) extension defines a lightweight, consistent interface for accessing databases in PHP. Each database driver that implements the PDO interface can expose database-specific features as regular extension functions. Note that you cannot perform any database functions using the PDO extension by itself; you must use a database-specific PDO driver to access a database server.

This new product addition and enhancement was scheduled for release in Version 5.1 and should be in general use by the time you are reading this. Basically, this is another approach to connecting to databases in an abstract way. Though similar to the PEAR::DB approach that we have just covered, it has (among others) these unique features:

PDO is a native C extension.

PDO takes advantage of latest PHP 5 internals.

PDO uses buffered reading of data from the result set.

PDO gives common DB features as a base.

PDO is still able to access DB-specific functions.

PDO can use transaction-based techniques.

PDO can interact with LOBS (Large Objects) in the database.

PDO can use Prepared and executable SQL statements with bound parameters.

PDO can implement scrollable cursors.

PDO has access to SQLSTATE error codes and has very flexible error handling.

Since there are a number of features here, we will only touch on a few of them to show you just how beneficial PDO is purported to be.

First, a little about PDO. It will have drivers for almost all database engines in existence, and those drivers that PDO does not supply should be accessible through PDO’s generic ODBC connection. PDO is modular in that it has to have at least two extensions enabled to be active: the PDO extension itself and the PDO extension specific to the database to which you will be interfacing. See the online documentation to set up the connections for the database of your choice at http://ca.php.net/pdo. For establishing PDO on a windows server for MySQL interaction, simply enter the following two lines into your php.ini file and restart your server:

extension=php_pdo.dl
l
extension=php_pdo_mysql.dll

The PDO library is also an object-oriented extension (you will see this in the code examples that follow).

Making a connection

The first thing that is required for PDO is that you make a connection to the database in question and hold that connection in a connection handle variable, as in the following code:

$ConnHandle = new PDO ($dsn, $username, $password);

The
$dsn
stands for the data source name, and the other two parameters are self-explanatory. Specifically, for a MySQL connection, you would write the following code:

Of course, you could (should) maintain the username and password parameters as variable-based for code reuse and flexibility reasons.

Interaction with the database

So, once you have the connection to your database engine and the database that you want to interact with, you can use that connection to send SQL commands to the server. A simple UPDATE statement would look like this:

This code simply updates the books table and releases the query. This is how you would usually send non-resulting simple SQL commands (UPDATE, DELETE, INSERT) to the database through PDO, unless you are using prepared statements, a more complex approach that is discussed in the next section.

{mospagebreak title=PDO and prepared statements}

PDO also allows for what is known as prepared statements. This is done with PDO calls in stages or steps. Consider the following code:

while ($row = $stmt->fetch()) { // gets rows one at a timeprint_r ($row);// or do something more meaningful with each returned row }
$stmt = null;

In this code, we “prepare” the SQL code then “execute” it. Next, we cycle through the result with the
while
code and, finally, we release the result object by assigning
null
to it. This may not look all that powerful in this simple example, but there are other features that can be used with prepared statements. Now, consider this code:

Here, we prepare the SQL statement with four named placeholders:
authorid
, title
,ISBN
, and
pub_year
. These happen to be the same names as the columns in the database. This is done only for clarity; the placeholder names can be anything that is meaningful to you. In the execute call, we replace these placeholders with the actual data that we want to use in this particular query. One of the advantages of prepared statements is that you can execute the same SQL command and pass in different values through the array each time. You can also do this type of statement preparation with positional placeholders (not actually naming them), signified by a
?
, which is the positional item to be replaced. Look at the following variation of the previous the code:

This code accomplishes the same thing but with less code, as the value area of the SQL statement does not name the elements to be replaced, and, therefore, the array in the execute statement only needs to send in the raw data and no names. You just have to be sure about the position of the data that you are sending into the prepared statement.

This was just a brief overview of what the new PDO library will be able to do for you in the database realm of PHP. If you want to explore this new library in more depth, be sure to do your research and testing before using it in a production environment. You can find information on PDO at http://ca.php.net/pdo.