Home arrow PHP arrow Page 2 - Database Details and PHP

Details About a Query Response - 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.

TABLE OF CONTENTS:
  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
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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

ThenumRows()andnumCols()methods tell you the number of rows and columns returned from aSELECT query:

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

TheaffectedRows()method tells you the number of rows affected by anINSERT,DELETE, orUPDATE operation:

  $howmany = $response->affectedRows();

ThetableInfo()method returns detailed information on the type and flags of fields returned from aSELECToperation:

  $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, MySQLs AUTO_INCREMENT).

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

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

Normally youll have one sequence per table for which you want unique IDs. This example inserts values into thebookstable, 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 thecreateSequence()anddropSequence()methods:

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

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



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

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

PHP ARTICLES

- 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: