Home arrow PHP arrow Page 4 - Database Techniques and PHP

Advanced Database Techniques - PHP

Picking up where we left off last week in our discussion of databases and PHP, we'll talk about connecting, issuing a query, 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 Techniques and PHP
  2. Issuing a Query
  3. Inside a row array
  4. Advanced Database Techniques
By: O'Reilly Media
Rating: starstarstarstarstar / 4
June 21, 2007

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

PEAR DB goes beyond the database primitives shown earlier; it provides several shortcut functions for fetching result rows, as well as a unique row ID system and separate prepare/execute steps that can improve the performance of repeated queries.

Placeholders

Just as printf() builds a string by inserting values into a template, the PEAR DB can build a query by inserting values into a template. Pass the query() function SQL with ? in place of specific values, and add a second parameter consisting of the array of values to insert into the SQL:

  $result = $db->query(SQL, values);

For example, this code inserts three entries into thebookstable:

  $movies = array(array('Foundation', 1951),
                 
array('Second Foundation', 1953),
                 
array('Foundation and Empire', 1952));
  foreach ($books as $book) {
   
$db->query('INSERT INTO books (title,pub_year) VALUES (?,?)', $book);
  }

There are three characters that you can use as placeholder values in an SQL query:

?

A string or number, which will be quoted if necessary (recommended)

|

A string or number, which will never be quoted

&

A filename, the contents of which will be included in the statement (e.g., for storing an image file in a BLOB field)

Prepare/Execute

When issuing the same query repeatedly, it can be more efficient to compile the query once and then execute it multiple times using the prepare(), execute(), and executeMultiple() methods.

The first step is to callprepare()on the query:

  $compiled = $db->prepare(SQL);

This returns a compiled query object. Theexecute()method fills in any placeholders in the query and sends it to the RDBMS:

  $response = $db->execute(compiled, values);

Thevalues  array contains the values for the placeholders in the query. The return value is either a query response object, orDB_ERRORif an error occurred.

For example, we could insert multiple values into thebookstable like this:

  $books = array(array('Foundation', 1951), 
                  array('Second Foundation', 1953),
                  array('Foundation and Empire', 1952));
  $compiled = $q->prepare('INSERT INTO books (title,pub_year) VALUES (?,?)');
  foreach ($books as $book) {
    $db->execute($compiled, $book);
  }

TheexecuteMultiple()method takes a two-dimensional array of values to insert:

  $responses = $db->executeMultiple(compiled, values);

Thevalues  array must be numerically indexed from 0 and have values that are arrays of values to insert. The compiled query is executed once for every entry invalues, and the query responses are collected in$responses.

A better way to write the book-insertions code is:

  $books = array(array('Foundation', 1951),
                  array('Second Foundation', 1953),
                  array('Foundation and Empire', 1952));
 
$compiled = $q->prepare('INSERT INTO books (title,pub_year) VALUES (?,?)');
  $db->insertMultiple($compiled, $books);

Please check back next week for the continuation of this article.



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