Adding a Business - PHP

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.

TABLE OF CONTENTS:

By: O'Reilly Media

Poor Best

Rating:

/ 2

July 05, 2007

print this article

SEARCH DEV SHED

TOOLS YOU CAN USE

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 categories. 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 thebiz_categoriestable.

Figure 8-6. Listing assigned to two categories

Example 8-5. Adding a business

<?php
require_once('db_login.php');

// fetch query parameters
$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>

Next: Displaying the Database >>