55
$update_fields[] = "$field = " . $dbh->quote($GLOBALS[$field]);
}
$sql = 'UPDATE zodiac SET ' . join(',',$update_fields)
. ' WHERE sign = ' . $dbh->quote($sign);
For an
INSERT
query, construct an array of values in the same order as the fields, and build
the query by applying
join( )
to each array:
$fields = array('symbol','planet','element');
$insert_values = array();
foreach ($fields as $field) {
$insert_values[] = $dbh->quote($GLOBALS[$field]);
}
$sql = 'INSERT INTO zodiac (' . join(',',$fields) . ') VALUES ('
. join(',',$insert_values) . ')';
If you have PEAR DB Version 1.3 or later, use the
DB::autoPrepare( )
method:
$fields = array('symbol','planet','element');
// UPDATE: specify the WHERE clause
$update_prh = $dbh->autoPrepare('zodiac',$fields,DB_AUTOQUERY_UPDATE,
'sign = ?');
$update_values = array();
foreach ($fields as $field) { $update_values[] = $GLOBALS[$field]; }
$update_values[] = $GLOBALS['sign'];
$dbh->execute($update_prh,$update_values);
// INSERT: no WHERE clause
$insert_prh = $dbh->autoPrepare('zodiac',$fields,DB_AUTOQUERY_INSERT);
$insert_values = array();
foreach ($fields as $field) { $insert_values[] = $GLOBALS[$field]; }
$dbh->execute($insert_prh,$insert_values);
10.13.3 Discussion
The
DB::autoPrepare( )
method is concise and easy to use if you have a recent version of
DB. PHP 4.2.2 comes with DB 1.2. Newer versions of DB can be downloaded from PEAR. Use
method_exists( )
to check whether your version of DB supports
autoPrepare( )
:
if (method_exists($dbh,'autoPrepare')) {
$prh = $dbh->autoPrepare('zodiac',$fields,DB_AUTOQUERY_UPDATE','sign =
?');
// ...
} else {
error_log("Can't use autoPrepare");
exit;
}
If you can't use
DB::autoPrepare( )
, the array-manipulation techniques shown in the
Solution accomplish the same thing. If you use sequence-generated integers as primary keys,
you can combine the two query-construction techniques into one function. That function
53
determines whether a record exists and then generates the correct query, including a new ID,
as shown in the
pc_build_query( )
function in Example 10-1
.
Example 10-1. pc_build_query( )
function pc_build_query($dbh,$key_field,$fields,$table) {
if (! empty($_REQUEST[$key_field])) {
$update_fields = array();
foreach ($fields as $field) {
$update_fields[] = "$field = ".$dbh->quote($_REQUEST[$field]);
}
return "UPDATE $table SET " . join(',',$update_fields) .
" WHERE $key_field = ".$_REQUEST[$key_field];
} else {
$insert_values = array();
foreach ($fields as $field) {
$insert_values[] = $dbh->quote($_REQUEST[$field]);
}
$next_id = $dbh->nextId($table);
return "INSERT INTO $table ($key_field," . join(',',$fields) .
") VALUES ($next_id," . join(',',$insert_values) . ')';
}
}
Using this function, you can make a simple page to edit all the information in the
zodiac
table:
require 'DB.php';
$dbh = DB::connect('mysql://test:@localhost/test');
$dbh->setFetchMode(DB_FETCHMODE_OBJECT);
$fields = array('sign','symbol','planet','element',
'start_month','start_day','end_month','end_day');
switch ($_REQUEST['cmd']) {
case 'edit':
$row = $dbh->getRow('SELECT ' . join(',',$fields) .
" FROM zodiac WHERE id =
?",array($_REQUEST['id']));
case 'add':
print '<form method="post" action="'.$_SERVER['PHP_SELF'].'">';
print '<input type="hidden" name="cmd" value="save">';
print '<table>';
if ('edit' == $_REQUEST['cmd']) {
printf('<input type="hidden" name="id" value="%d">',
$_REQUEST['id']);
}
foreach ($fields as $field) {
if ('edit' == $_REQUEST['cmd']) {
$value = htmlspecialchars($row->$field);
} else {
$value = '';
}
printf('<tr><td>%s: </td><td><input type="text" name="%s"
value="%s">,
64
$field,$field,$value);
printf('</td></tr>');
}
print '<tr><td></td><td><input type="submit" value="Save"></td></tr>';
print '</table></form>';
break;
case 'save':
$sql = pc_build_query($dbh,'id',$fields,'zodiac');
if (DB::isError($sth = $dbh->query($sql))) {
print "Couldn't add info: ".$sth->getMessage();
} else {
print "Added info.";
}
print '<hr>';
default:
$sth = $dbh->query('SELECT id,sign FROM zodiac');
print '<ul>';
while ($row = $sth->fetchRow()) {
printf('<li> <a href="%s?cmd=edit&id=%s">%s</a>',
$_SERVER['PHP_SELF'],$row->id,$row->sign);
}
print '<hr><li> <a href="'.$_SERVER['PHP_SELF'].'?cmd=add">Add
New</a>';
print '</ul>';
break;
}
The
switch
statement controls what action the program takes based on the value of
$_REQUEST['cmd']
. If
$_REQUEST['cmd']
is
add
or
edit
, the program displays a form
with textboxes for each field in the
$fields
array, as shown in Figure 10-1
. If
$_REQUEST['cmd']
is
edit
, values for the row with the supplied
$id
are loaded from the
database and displayed as defaults. If
$_REQUEST['cmd']
is
save
, the program uses
pc_build_query( )
to generate an appropriate query to either
INSERT
or
UPDATE
the data
in the database. After saving (or if no
$_REQUEST['cmd']
is specified), the program displays
a list of all zodiac signs, as shown in Figure 10-2
.
Figure 10-1. Adding and editing a record
23
Figure 10-2. Listing records
Whether
pc_build_query( )
builds an
INSERT
or
UPDATE
statement is based on the
presence of the request variable
$_REQUEST['id']
(because
id
is passed in
$key_field
).
If
$_REQUEST['id']
is not empty, the function builds an
UPDATE
query to change the row
with that ID. If
$_REQUEST['id']
is empty (or it hasn't been set at all), the function
46
generates a new ID with
nextId( )
and uses that new ID in an
INSERT
query that adds a
row to the table.
10.13.4 See Also
Documentation on
DB::autoPrepare( )
at
http://pear.php.net/manual/en/core.db.autoprepare.php
; new versions of PEAR DB are
available at http://pear.php.net/package-info.php?package=DB
.
Recipe 10.14 Making Paginated Links for a Series of Records
10.14.1 Problem
You want to display a large dataset a page at a time and provide links that move through the
dataset.
10.14.2 Solution
Use the PEAR
DB_Pager
class:
require 'DB/Pager.php';
$offset = intval($_REQUEST['offset']);
$per_page = 3;
$sth = $dbh->query('SELECT * FROM zodiac ORDER BY id');
$pager = new DB_Pager($sth, $offset, $per_page);
$data = $pager->build();
// display each row on this page
while ($v = $pager->fetchRow()) {
print "$v->sign, $v->symbol ($v->id)<br>";
}
// a link to the previous page
printf('<a href="%s?offset=%d"><<Prev</a> |',
$_SERVER['PHP_SELF'],$data['prev']);
// direct links to each page
foreach ($data['pages'] as $page => $start) {
printf(' <a href="%s?offset=%d">%d</a>
|',$_SERVER['PHP_SELF'],$start,$page);
}
// a link to the next page
printf(' <a href="%s?offset=%d">Next>></a>',
$_SERVER['PHP_SELF'],$data['next']);
// display which records are on this page
printf("<br>(Displaying %d - %d of %d)",
$data['from'],$data['to'],$data['numrows']);
56
If you don't have
DB_Pager
or you do but don't want to use it, you can roll your own indexed
link display using the
pc_indexed_links( )
and
pc_print_link( )
functions shown in
the Discussion in Examples 10-2 and 10-3.
$offset = intval($_REQUEST['offset']);
if (! $offset) { $offset = 1; }
$per_page = 5;
$total = $dbh->getOne('SELECT COUNT(*) FROM zodiac');
$sql = $dbh->modifyLimitQuery('SELECT * FROM zodiac ORDER BY id',
$offset - 1,$per_page);
$ar = $dbh->getAll($sql);
foreach ($ar as $k => $v) {
print "$v->sign, $v->symbol ($v->id)<br>";
}
pc_indexed_links($total,$offset,$per_page);
printf("<br>(Displaying %d - %d of %d)",$offset,$offset+$k,$total);
10.14.3 Discussion
DB_Pager
is designed specifically to paginate results that come from a PEAR DB query. To use
it, create a
DB_Pager
object and tell it what query to use, what offset into the result set to
start at, and how many items belong on each page. It calculates the correct pagination.
The
$pager->build( )
method calculates the appropriate rows to return and other page-
specific variables.
DB_Pager
provides a
fetchRow( )
method to retrieve the results in the
same way the
DB
class operates. (You can also use
fetchInto( )
with DB_Pager). However,
while it provides all the data you need to build appropriate links, it also leaves it up to you to
build those links. The offset the previous page starts at is in
$data['prev']
, and
$data['next']
is the offset of the next page. The
$data['pages']
array contains page
numbers and their starting offsets. The output when
$offset
is is shown in Figure 10-3
.
Figure 10-3. Paginated results with DB_Pager
All the page numbers, "<<Prev" and "Next>>," are links. "<<Prev" and "1" point to the
current page; the others point to their corresponding pages. On page 4, the "Next>>" link
46
points back to page 1. (But on page 1, the "<<Prev" link doesn't point to page 4.) The
numbers in the links refer to page numbers, not element numbers.
If
DB_Pager
isn't available, you can use the
pc_print_link( )
and
pc_indexed_links(
)
functions shown in Examples 10-2 and 10-3 to produce properly formatted links.
Example 10-2. pc_print_link( )
function pc_print_link($inactive,$text,$offset='') {
if ($inactive) {
printf('<font color="#666666">%s</font>',$text);
} else {
printf('<a
href="%s?offset=%d">%s</a>',$_SERVER['PHP_SELF'],$offset,$text);
}
}
Example 10-3. pc_indexed_links( )
function pc_indexed_links($total,$offset,$per_page) {
$separator = ' | ';
// print "<<Prev" link
pc_print_link($offset == 1, '<<Prev', $offset - $per_page);
// print all groupings except last one
for ($start = 1, $end = $per_page;
$end < $total;
$start += $per_page, $end += $per_page) {
print $separator;
pc_print_link($offset == $start, "$start-$end", $start);
}
/* print the last grouping -
* at this point, $start points to the element at the beginning
* of the last grouping
*/
/* the text should only contain a range if there's more than
* one element on the last page. For example, the last grouping
* of 11 elements with 5 per page should just say "11", not "11-11"
*/
$end = ($total > $start) ? "-$total" : '';
print $separator;
pc_print_link($offset == $start, "$start$end", $start);
// print "Next>>" link
print $separator;
pc_print_link($offset == $start, 'Next>>',$offset + $per_page);
}
59
To use these functions, retrieve the correct subset of the data using
DB::modifyLimitQuery( )
and then print it out. Call
pc_indexed_links( )
to display
the indexed links:
$offset = intval($_REQUEST['offset']);
if (! $offset) { $offset = 1; }
$per_page = 5;
$total = $dbh->getOne('SELECT COUNT(*) FROM zodiac');
$sql = $dbh->modifyLimitQuery('SELECT * FROM zodiac ORDER BY id',
$offset - 1,$per_page);
$ar = $dbh->getAll($sql);
foreach ($ar as $k => $v) {
print "$v->sign, $v->symbol ($v->id)<br>";
}
pc_indexed_links($total,$offset,$per_page);
printf("<br>(Displaying %d - %d of %d)",$offset,$offset+$k,$total);
After connecting to the database, you need to make sure
$offset
has an appropriate value.
$offset
is the beginning record in the result set that should be displayed. To start at the
beginning of the result set,
$offset
should be 1. The variable
$per_page
is set to how
many records to display on each page, and
$total
is the total number of records in the entire
result set. For this example, all the Zodiac records are displayed, so
$total
is set to the
count of all the rows in the entire table.
The SQL query that retrieves information in the proper order is:
SELECT * FROM zodiac ORDER BY id
Use
modifyLimitQuery( )
to restrict the rows being retrieved. You'll want to retrieve
$per_page
rows, starting at
$offset - 1
, because the first row is 0, not 1, to the
database. The
modifyLimitQuery( )
method applies the correct database-specific logic to
restrict what rows are returned by the query.
The relevant rows are retrieved by
$dbh->getAll($sql)
, and then information is displayed
from each row. After the rows,
pc_indexed_links( )
provides navigation links. The output
when
$offset
is not set (or is 1) is shown in Figure 10-4
.
Figure 10-4. Paginated results with pc_indexed_links( )
37
In Figure 10-4
, "6-10", "11-12", and "Next>>" are links to the same page with adjusted
$offset
arguments, while "<<Prev" and "1-5" are greyed out, because what they would link
to is what's currently displayed.
10.14.4 See Also
Information on
DB_Pager
at http://pear.php.net/package-info.php?package=DB_Pager
.
Recipe 10.15 Caching Queries and Results
10.15.1 Problem
You don't want to rerun potentially expensive database queries when the results haven't
changed.
10.15.2 Solution
Use PEAR's
Cache_DB
package. It wraps the
DB
database abstraction layer with an object that
has similar methods and that automatically caches the results of
SELECT
queries:
require 'Cache/DB.php';
$cache = new Cache_DB;
$cache->connect('mysql://test:@localhost/test');
$sth = $cache->query("SELECT sign FROM zodiac WHERE element LIKE 'fire'");
while($row = $sth->fetchRow()) {
print $row['sign']."\n";
}
10.15.3 Discussion
Using
Cache_DB
is almost the same as using
DB
, but there are some crucial differences. First,
Cache/DB.php is required instead of DB.php. The Cache/DB.php file then loads the appropriate
Documents you may be interested
Documents you may be interested
