Databases: Finishing a Listing Service

Concluding our discussion of databases and PHP, we’ll finish building the example that we started last week. This article is excerpted from chapter eight of the book Programming PHP, Second Edition, written by Kevin Tatroe, Rasmus Lerdorf, and Peter MacIntyre (O’Reilly, 2006; ISBN: 0596006810). Copyright © 2006 O’Reilly Media, Inc. All rights reserved. Used with permission from the publisher. Available from booksellers or direct from O’Reilly Media.

Administrator’s Page 

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.

Example 8-4.  Backend administration page

<html>
<head>
<?php
 
require_once(‘db_login.php’);
?>

<title>
<?php
 // print the window title and the topmost body heading
 $doc_title = ‘Category Administration';
 echo "$doc_titlen";
?>
</title>
</head>
<body>
<h1>
<?php
 
echo "$doc_titlen";
?>
</h1>

<?php
 // add category record input section

 // extract values from $_REQUEST
 $Cat_ID = $_REQUEST['Cat_ID'];
 $Cat_Title = $_REQUEST['Cat_Title'];
 $Cat_Desc = $_REQUEST['Cat_Desc'];
 $add_record = $_REQUEST['add_record'];

 // determine the length of each input field
 $len_cat_id = strlen($_REQUEST['Cat_ID']);
 $len_cat_tl = strlen($_REQUEST['Cat_Title']);
 $len_cat_de = strlen($_REQUEST['Cat_Desc']);

 // validate and insert if the form script has been
 // called by the Add Category button
 if ($add_record == 1) {
    
if (($len_cat_id > 0) and ($len_cat_tl > 0) and ($len_cat_de > 0)){
         $sql = "insert into categories (category_id, title, description)";
         $sql .= " values (‘$Cat_ID’, ‘$Cat_Title’, ‘$Cat_Desc’)";
         $result = $db->query($sql);
         $db->commit();
     } else {
     echo "<p>Please make sure all fields are filled in ";
     echo "and try again.</p>n";
     } 
 
}

 // list categories reporting section

 // query all records in the table after any
 // insertion that may have occurred above
 $sql = "select * from categories";
 $result = $db->query($sql);
?>

<form method="post" action="<?= $PHP_SELF
?>">

<table>
<tr><th bgcolor="#eeeeee">Cat ID</th>
    <th bgcolor="#eeeeee">Title</th>
    <th bgcolor="#eeeeee">Description</th>
</tr>

<?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";
 }
?>

<tr><td><input type="text" name="Cat_ID" size="15" maxlength="10" /></td>
    <td><input type="text" name="Cat_Title" size="40" maxlength="128" /></td>
    <td><input type="text" name="Cat_Desc" size="45" maxlength="255" /></td>
</tr>
</table>
<input type="hidden" name="add_record" value="1" />
<input type="submit" name="submit" value="Add Category" />
</body>
</html>

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.


Figure 8-6.  Listing assigned to two categories

Example 8-5.  Adding a business

<html>
<head>
<title>
<?php
 
$doc_title = ‘Business Registration';
 
echo "$doc_titlen";
?>
</title>
</head>
<body>
<h1>
<?= $doc_title ?>
</h1>

<?php
 require_once(‘db_login.php’);

 // fetch query parameter s
 $add_record = $_REQUEST['add_record'];
 $Biz_Name = $_REQUEST['Biz_Name'];
 $Biz_Address = $_REQUEST['Biz_Address'];
 $Biz_City = $_REQUEST['Biz_City'];
 $Biz_Telephone = $_REQUEST['Biz_Telephone'];
 $Biz_URL = $_REQUEST['Biz_URL'];
 $Biz_Categories = $_REQUEST['Biz_Categories'];

 $pick_message = ‘Click on one, or control-click on<BR>multiple ‘;
 $pick_message .= ‘categories:';

 // add new business
 
if ($add_record == 1) {
     $pick_message = ‘Selected category values<br />are highlighted:';
     $sql  = ‘INSERT INTO businesses (name, address, city, telephone, ‘;
     $sql .= ‘ url) VALUES (?, ?, ?, ?, ?)';
     $params = array($Biz_Name, $Biz_Address, $Biz_City, $Biz_Telephone, $Biz_URL);
     $query = $db->prepare($sql);
     if (DB::isError($query)) die($query->getMessage());
     $resp = $db->execute($query, $params);
     if (DB::isError($resp)) die($resp->getMessage());
     $resp = $db->commit();
     if (DB::isError($resp)) die($resp->getMessage());
     echo ‘<p class="message">Record inserted as shown below.</p>';
     $biz_id = $db->getOne(‘SELECT max(business_id) FROM businesses’);
 }
?>

<form method="post" action="<?= $PHP_SELF ?>">
<table>
<tr><td class="picklist"><?= $pick_message ?>
    <p>
    <select name="Biz_Categories[]" size="4" multiple>
    <?php
     // build the scrolling pick list for the categories
     $sql = "SELECT * FROM categories";
     $result = $db->query($sql);
     if (DB::isError($result)) die($result->getMessage());
     while ($row = $result->fetchRow()){
        
if (DB::isError($row)) die($row->getMessage());
        
if ($add_record == 1){
             $selected = false;
             // if this category was selected, add a new biz_categories row
             if (in_array($row[1], $Biz_Categories)) {
                
$sql  = ‘INSERT INTO biz_categories';
                 $sql .= ‘ (business_id, category_id)';
                 $sql .= ‘ VALUES (?, ?)';
                 $params = array($biz_id, $row[0]);
                 $query = $db->prepare($sql);
                 if (DB::isError($query)) die($query->getMessage());
                 $resp = $db->execute($query, $params);
                 if (DB::isError($resp)) die($resp->getMessage());
                 $resp = $db->commit();
                 if (DB::isError($resp)) die($resp->getMessage());
                 echo "<option selected="selected">$row[1]</option>n";
                 $selected = true; 
             
}
             if ($selected == false) {
                  echo "<option>$row[1]</option>n";
             }
         } else {
             echo "<option>$row[1]</option>n";
         }
      }
     ?>

     </select>
     </td>
     <td class="picklist">
        
<table>
         <tr><td class="FormLabel">Business Name:</td>
             <td><input type="text" name="Biz_Name" size="40" maxlength="255"
                
value="<?= $Biz_Name ?>" /></td>
         </tr>
         <tr><td class="FormLabel">Address:</td>
          <td><input type="text" name="Biz_Address" size="40" maxlength="255"
                
value="<?= $Biz_Address ?>" /></td>
         </tr>
         <tr><td class="FormLabel">City:</td>
            
<td><input type="text" name="Biz_City" size="40" maxlength="128"
                
value="<?= $Biz_City ?>" /></td>
         </tr>
         <tr><td class="FormLabel">Telephone:</td>
         <td><input type="text" name="Biz_Telephone" size="40" maxlength="64"
                
value="<?= $Biz_Telephone ?>" /></td>
         </tr>
         <tr><td class="FormLabel">URL:</TD>
             <td><input type="text" name="Biz_URL" size="40" maxlength="255"
                
value="<?= $Biz_URL ?>" /></td>
         </tr>
         </table>
    
</td>
 </tr>
 </table>
 
<p>
 <input type="hidden" name="add_record" value="1" />

 <?php
 // display the submit button on new forms; link to a fresh registration
 // page on confirmations
 if ($add_record == 1){
    
echo ‘<p><a href="’.$PHP_SELF.’">Add Another Business</a></p>';
 } else {
     echo ‘<input type="submit" name="submit" value="Add Business" />';
 }
?>

</p>
</body>
</html>

{mospagebreak title=Displaying the Database}

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.

Example 8-6.  Business listing page

<html>
<head>
<title>
<?php
 
$doc_title = ‘Business Listings';
 
echo "$doc_titlen";
?>
</title>
</head>
<body>
<h1>
<?= $doc_title ?>
</h1>

<?php
 // establish the database connection

 require_once(‘db_login.php’);

 $pick_message = ‘Click on a category to find business listings:';
?>

<table border=0>
<tr><td valign="top">
    <table border=5>
    <tr><td class="picklist"><strong><?= $pick_message ?></strong></td></tr>
   
<p>
   
<?php
     // build the scrolling pick list for the categories
     $sql = "SELECT * FROM categories";
     $result = $db->query($sql);
     if (DB::isError($result)) die($result->getMessage());
     while ($row = $result->fetchRow()){
         if (DB::isError($row)) die($row->getMessage());
         echo ‘<tr><td class="formlabel">';
         echo "<a href="$PHP_SELF?cat_id=$row[0]">";
         echo "$row[1]</a></td></tr>n";
     }
    ?>
    </table>
</td>
<td valign="top">
     <table border=1>
     <?php

     if ($cat_id) {
       $sql = "SELECT * FROM businesses b, biz_categories bc where";
       $sql .= " category_id = ‘$cat_id’";
       $sql .= " and b.business_id = bc.business_id";
       $result = $db->query($sql);
       if (DB::isError($result)) die($result->getMessage());
       while ($row = $result->fetchRow()){
        
if (DB::isError($row)) die($row->getMessage());
        
if ($color == 1) {
           $bg_shade = ‘dark';
           $color = 0;
         } else {
           $bg_shade = ‘light';
           $color = 1;
        
}
         echo "<tr>n";
         for($i = 0; $i < count($row); $i++) {
           echo "<td class="$bg_shade">$row[$i]</td>n";
         }
         echo "</tr>n";
     
}
    }
   ?>
   </table>
</td></tr>
</table>
</body>
</html>

The business listings page is illustrated in Figure 8-7.


Figure 8-7.  Business Listings page

{mospagebreak title=PHP Data Objects}

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:

  1. PDO is a native C extension.
  2. PDO takes advantage of latest PHP 5 internals.
  3. PDO uses buffered reading of data from the result set.
  4. PDO gives common DB features as a base.
  5. PDO is still able to access DB-specific functions.
  6. PDO can use transaction-based techniques.
  7. PDO can interact with LOBS (Large Objects) in the database.
  8. PDO can use Prepared and executable SQL statements with bound parameters.
  9. PDO can implement scrollable cursors.
  10. 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 $ds n  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:

  $ConnHandle = new PDO(‘mysql:host=localhost;dbname=library’,
   ‘petermac’, ‘abc123′);

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:

  $ConnHandle->query("UPDATE books SET authorid=4 "
   . "WHERE pub_year = 1982");

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:

  $stmt = $ConnHandle->prepare( "SELECT * FROM books");
  $stmt->execute();

  while ($row = $stmt->fetch()) {   // gets rows one at a time
     
print_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:

  $stmt = $db->prepare("INSERT INTO authors"
     
. "(authorid, title, ISBN, pub_year)"
     
. "VALUES (:authorid, :title, :ISBN, :pub_year)");
  $stmt->execute(array(‘authorid‘  => 4,
                      
title‘     => ‘Foundation’,
                      
ISBN‘      => 0-553-80371-9,
                      
pub_year‘ => 1951)
  );

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:

  $stmt = $db->prepare("INSERT INTO authors"
     
. "(authorid, title, ISBN, pub_year)"
     
. "VALUES (?,?,?,?)");

  $stmt->execute(array(4, ‘Foundation’, 0-553-80371-9, 1951));

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.

[gp-comments width="770" linklove="off" ]

antalya escort bayan antalya escort bayan Antalya escort diyarbakir escort