Database Details and 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.

Shortcuts 

PEAR DB provides a number of methods that perform a query and fetch the results in one step: getOne(), getRow(), getCol(), getAssoc() , and getAll() . All of these methods permit placeholders.

The getOne() method fetches the first column of the first row of data returned by an SQL query:

  $value = $db->getOne(SQL [, values ]);

For example:

  $when = $db->getOne("SELECT avg(pub_year) FROM books");
  if (DB::isError($when)) {
   
die($when->getMessage());
  }

  echo "The average book in the library was published in $when";

  The average book in the library was published in 1974

The getRow() method returns the first row of data returned by an SQL query:

  $row = $db->getRow(SQL [, values ]]);

This is useful if you know only one row will be returned. For example:

  list($title, $author) = $db->getRow(
    "SELECT books.title,authors.name FROM books, authors
     WHERE books.pub_year=1950 AND books.authorid=authors.authorid");
  echo "($title, written by $author)";
  (I, Robot, written by Isaac Asimov)

The getCol() method returns a single column from the data returned by an SQL query:

  $col = $db->getCol(SQL [, column [, values ]]);

The column  parameter can be either a number (0, the default, is the first column), or the column name.

For example, this fetches the names of all the books in the database, ordered by the year they were released:

  $titles = $db->getAll("SELECT title FROM books ORDER BY pub_year ASC");
  foreach ($titles as $title) {
   
echo "$titlen";
  }
 
the Hobbit
  I, Robot
  Foundation
  …

The getAll() method returns an array of all the rows returned by the query:

  $all = $db->getAll(SQL [, values [, fetchmode ]]);

For example, the following code builds a select box containing the names of the movies. The ID of the selected movie is submitted as the parameter value.

  $results = $db->getAll("SELECT bookid,title FROM books ORDER BY pub_year ASC") ;
  echo "<select name=’movie’>n";
  foreach ($results as $result) {
    echo "<option value={$result[0]}>{$result[1]}</option>n";
  }
  echo "</select>";

All the get*() methods return DB_ERROR when an error occurs.

{mospagebreak title=Details About a Query Response}

Four PEAR DB methods provide you with information on a query result object: numRows(), numCols(), affectedRows(), and tableInfo() .

The numRows() and numCols() methods tell you the number of rows and columns returned from a SELECT query:

  $howmany = $response->numRows();
  $howmany = $response->numCols();

The affectedRows() method tells you the number of rows affected by an INSERT , DELETE , or UPDATE operation:

  $howmany = $response->affectedRows();

The tableInfo() method returns detailed information on the type and flags of fields returned from a SELECT operation:

  $info = $response->tableInfo();

The following code dumps the table information into an HTML table:

  // connect
   require_once(‘DB.php’);
   $db = DB::connect("mysql://librarian:passw0rd@localhost/ library");
   if (DB::iserror($db)) {
    
die($db->getMessage());
   }

  $sql = "SELECT * FROM BOOKS";

  $q = $db->query($sql);
  if (DB::iserror($q)) {
    die($q->getMessage());
  }

  $info = $q->tableInfo();
  a_to_table($info);

  function a_to_table ($a) {
    echo "<html><head><title> Table Info </title></head>";
    echo "<table border=1>n";
    foreach ($a as $key => $value) {
     
echo "<tr valign=top align=left><td>$key</td><td>";
      if (is_array($value)) {
       
a_to_table($value);
      } else {
       
print_r($value);
     
}
     
echo "</td></tr>n";
    
}
    
echo "</table>n";
  }

Figure 8-2 shows the output of the table information dumper.


Figure 8-2.  The information from tableInfo( )

Sequences

Not every RDBMS has the ability to assign unique row IDs, and those that do have wildly differing ways of returning that information. PEAR DB sequences are an alternative to database-specific ID assignment (for instance, MySQL’s AUTO_INCREMENT).

The nextID() method returns the next ID for the given sequence:

  $id = $db->nextID(sequence);

Normally you’ll have one sequence per table for which you want unique IDs. This example inserts values into the books table, giving a unique identifier to each row:

  $books = array(array(‘Foundation’, 1951),
                 
array(‘Second Foundation’, 1953),
                 
array(‘Foundation and Empire’, 1952));

  foreach ($books as $book) {
   
$id = $db->nextID(‘books’);
   
splice($book, 0, 0, $id);
   
$db->query(‘INSERT INTO books (bookid,title,pub_year) VALUES (?,?,?)’,
$book);
 
}

A sequence is really a table in the database that keeps track of the last-assigned ID. You can explicitly create and destroy sequences with the createSequence() and dropSequence() methods:

  $res = $db->createSequence(sequence);
  $res = $db->dropSequence(sequence);

The result will be the result object from the create or drop query or DB_ERROR if an error occurred.

{mospagebreak title=Metadata} 

The getListOf() method lets you query the database for information on available databases, users, views, and functions:

  $data = $db->getListOf(what);

The wha t  parameter is a string identifying the database feature to list. Most data bases support "databases ; " some support "users , " "views , " and "functions . "

For example, this stores a list of available databases in $dbs :

  $dbs = $db->getListOf("databases");

Transactions

Some RDBMSs support transactions, in which a series of database changes can be committed (all applied at once) or rolled back (discarded, with the changes not applied to the database). For example, when a bank handles a money transfer, the withdrawal from one account and deposit into another must happen together—neither should happen without the other, and there should be no time between the two actions. PEAR DB offers the commit () and rollback() methods to help with transactions:

  $res = $db->commit();
 
$res = $db->rollback();

If you call commit() or rollback() on a database that doesn’t support transactions, the methods return DB_ERROR .

Be sure to check your underlying database product to ensure that it supports transactions.

{mospagebreak title=Sample Application}

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.

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

chat sex hikayeleri Ensest hikaye