8.4.1. Placeholders

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

For example, this code inserts three entries into the movies table:

$movies = array(array('Dr No', 1962),
                array('Goldfinger', 1965),
                array('Thunderball', 1965));
foreach ($movies as $movie) {
  $db->query('INSERT INTO movies (title,year) VALUES (?,?)', $movie);
}

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)

8.4.2. Prepare/Execute

The first step is to call prepare( ) on the query:

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

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

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

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

For example, we could insert multiple values into the movies table like this:

$movies = array(array('Dr No', 1962),
                array('Goldfinger', 1965),
                array('Thunderball', 1965));
$compiled = $q->prepare('INSERT INTO movies (title,year) VALUES (?,?)');
foreach ($movies as $movie) {
  $db->execute($compiled, $movie);
}

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

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

The values 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 in values, and the query responses are collected in $responses.

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

$movies = array(array('Dr No', 1962),
                array('Goldfinger', 1965),
                array('Thunderball', 1965));
$compiled = $q->prepare('INSERT INTO movies (title,year) VALUES (?,?)');
$db->insertMultiple($compiled, $movies);

8.4.3. Shortcuts

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(year) FROM movies");
if (DB::isError($when)) {
  die($when->getMessage( ));
}
echo "The average James Bond movie was made in $when";
The average James Bond movie was made in 1977

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, $actor) = $db->getRow(
  "SELECT movies.title,actors.name FROM movies,actors 
   WHERE movies.year=1977 AND movies.actor=actors.id");
echo "($title, starring $actor)";
(The Spy Who Loved Me, starring Roger Moore)

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 Bond movies in the database, ordered by the year they were released:

$titles = $db->getAll("SELECT title FROM movies ORDER BY year ASC");
foreach ($titles as $title) {
  echo "$title\n";
}
Dr No
From Russia With Love
Goldfinger
...

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 id,title FROM movies ORDER BY 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.

8.4.4. Details About a Query Response

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:

$info = $response->tableInfo( );
a_to_table($info);
  
function a_to_table ($a) {
  echo "<table border=1>\n";
  foreach ($a as $k => $v) {
    echo "<tr valign=top align=left><td>$k</td><td>";
    if (is_array($v)) {
      a_to_table($v);
    } else {
      print_r($v);
    }
    echo "</td></tr>\n";
  }
  echo "</table>\n";
}

Figure 8-2. The information from tableInfo( )

8.4.5. Sequences

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

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

$movies = array(array('Dr No', 1962),
                array('Goldfinger', 1965),
                array('Thunderball', 1965));
foreach ($movies as $movie) {
  $id = $db->nextID('movies');
  splice($movie, 0, 0, $id);
  $db->query('INSERT INTO movies (id,title,year) VALUES (?,?,?)', $movie);
}

$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.

8.4.6. Metadata

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

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

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

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

8.4.7. 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.