Home arrow PHP arrow Page 4 - Database Details and PHP

Sample Application - PHP

Picking up from where we left off last week, we'll be discussing shortcuts, query responses, metadata, and more. 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.

  1. Database Details and PHP
  2. Details About a Query Response
  3. Metadata
  4. Sample Application
By: O'Reilly Media
Rating: starstarstarstarstar / 6
June 28, 2007

print this article



Because web database applications are such a mainstay of web development, we’ve decided to show you a complete sample application in this chapter. This section develops a self-maintaining business listing service. Companies add their own records to the database and pick the category or categories by which they want to be indexed.

Two HTML forms are needed to populate the database tables. One form provides the site administrator with the means to add category IDs, titles, and descriptions. The second form, used by the self-registering businesses, collects the business contact information and permits the registrant to associate the listing with one or more categories. A separate page displays the listings by category on the web page.

Database Tables

There are three tables: businesses to collect the address data for each business, categories to name and describe each category, and an associative table called biz_categories to relate entries in the other two tables to each other. These tables and their relationships are shown in Figure 8-3.

Figure 8-3.  Database design for business listing service

Example 8-2 contains a dump of the table schema in MySQL format. Depending on your database’s features, the schema may have to be altered slightly.

Example 8-2.  Database schema

# ------------------------------------------
# Table structure for table 'biz_categories' #

CREATE TABLE biz_categories (
   business_id int(11) NOT NULL,
   category_id char(10) NOT NULL,
   PRIMARY KEY (business_id, category_id),
   KEY business_id (business_id, category_id)

# ------------------------------------------#
# Table structure for table 'businesses'

CREATE TABLE businesses (
   business_id int(11) NOT NULL auto_increment,
   name varchar(255) NOT NULL,
   address varchar(255) NOT NULL,
   city varchar(128) NOT NULL,
   telephone varchar(64) NOT NULL,
   url varchar(255),
   PRIMARY KEY (business_id),
   UNIQUE business_id (business_id),
   KEY business_id_2 (business_id)

# ------------------------------------------#
# Table structure for table 'categories'

CREATE TABLE categories (
   category_id varchar(10) NOT NULL,
   title varchar(128) NOT NULL,
   description varchar(255) NOT NULL,
   PRIMARY KEY (category_id),
   UNIQUE category_id (category_id),
   KEY category_id_2 (category_id)

Database Connection

We’ve designed these pages to work with a MySQL, PostgreSQL, or Oracle 8i backend. The only visible sign of this in the PHP code is that we use commit() after every update. We’ve abstracted the database-specific stuff to a db_login.php library, shown in Example 8-3, which selects an appropriate DSN for MySQL, PostgreSQL, or Oracle.

Example 8-3.  Database connection abstraction script (db_login.php)


 // database connection setup section

 $username = 'user';
$password = 'seekrit';
$hostspec = 'localhost';
$database = 'phpbook';

 // select one of these three values for $phptype

 // $phptype = 'pgsql';
// $phptype = 'oci8';
$phptype = 'mysql';

 // check for Oracle 8 - data source name syntax is different

 if ($phptype != 'oci8'){
$dsn = "$phptype://$username:$password@$hostspec/$database";
 } else {
     $net8name = 'www';
     $dsn = "$phptype://$username:$password@$net8name";

 // establish the connection

 $db = DB::connect($dsn);
 if (DB::isError($db)) {
     die ($db->getMessage());

Please check back next week for the conclusion to this article.

>>> More PHP Articles          >>> More By O'Reilly Media

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Hackers Compromise PHP Sites to Launch Attac...
- Red Hat, Zend Form OpenShift PaaS Alliance
- PHP IDE News
- BCD, Zend Extend PHP Partnership
- PHP FAQ Highlight
- PHP Creator Didn't Set Out to Create a Langu...
- PHP Trends Revealed in Zend Study
- PHP: Best Methods for Running Scheduled Jobs
- PHP Array Functions: array_change_key_case
- PHP array_combine Function
- PHP array_chunk Function
- PHP Closures as View Helpers: Lazy-Loading F...
- Using PHP Closures as View Helpers
- PHP File and Operating System Program Execut...
- PHP: Effects of Wrapping Code in Class Const...

Developer Shed Affiliates


Dev Shed Tutorial Topics: