PHP
  Home arrow PHP arrow Page 4 - Database Details and PHP
Administration  
AJAX  
Apache  
BrainDump  
DHTML  
Flash  
Java  
JavaScript  
Multimedia  
MySQL  
Oracle  
Perl  
PHP  
Practices  
Python  
Reviews  
Security  
Style-Sheets  
Web Services  
XML  
Zend  
Zope  
Forums Sitemap 
IBM® developerWorks 
Sun Developer Network 
E-Commerce Hosting 
Linux Web Hosting 
Managed Hosting 
Small Business Hosting 
Mobile Linux 
App Generation ROI 
VPS Hosting 
Weekly Newsletter

 
Developer Updates  
Free Website Content 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us Get Paid 
Request Media Kit
Contact Us 
Site Map 
Privacy Policy 
Support 
 USERNAME
 
 PASSWORD
 
 
  >>> SIGN UP!  
  Lost Password? 
PHP

Database Details and PHP
By: O'Reilly Media
  • Search For More Articles!
  • Disclaimer
  • Author Terms
  • Rating: 4 stars4 stars4 stars4 stars4 stars / 6
    2007-06-28

    Table of Contents:
  • Database Details and PHP
  • Details About a Query Response
  • Metadata
  • Sample Application

  • Rate this Article: Poor Best 
      ADD THIS ARTICLE TO:
      Del.ici.ous Digg
      Blink Simpy
      Google Spurl
      Y! MyWeb Furl
    Email Me Similar Content When Posted
    Add Developer Shed Article Feed To Your Site
    Email Article To Friend
    Print Version Of Article
    PDF Version Of Article
     
     
    ADVERTISEMENT


    Database Details and PHP - Sample Application


    (Page 4 of 4 )

    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)

    <?php
     require_once('DB.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.


    DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware.

       · This article is an excerpt from the book "Programming PHP, Second Edition,"...
     

    Buy this book now. 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). Check it out today at your favorite bookstore. Buy this book now.

       

    PHP ARTICLES

    - Working With Different Namespaces in PHP 5
    - User Management Explained: Overview
    - Using Namespaces in PHP 5
    - Database Security: Guarding Against SQL Inje...
    - Building a Modular Exception Class in PHP 5
    - Database and Password Security for Web Appli...
    - Handling MySQL Data Set Failures in PHP 5
    - Building Site Registration for Web Applicati...
    - Intercepting Customized Exceptions in PHP 5
    - Securing Your Web Application Against Attacks
    - Sub Classing Exceptions in PHP 5
    - Authentication for Web Application Security
    - Building a Content Management System with Co...
    - Filters and Login Systems for Web Applicatio...
    - Working with the Email Class in Code Igniter





    © 2003-2008 by Developer Shed. All rights reserved. DS Cluster 1 hosted by Hostway
    Stay green...Green IT