Description
Purpose
Prepare() and execute*()
give you more power and flexibilty for query execution. You
can use them, if you have to do more than one equal query
(i.e. adding a list of adresses to a database) or if you want
to support different databases, which have different
implementations of the SQL standard.
Imagine you want to support two databases with different INSERT
syntax:
db1 : INSERT INTO tbl_name ( col1, col2 ... ) VALUES ( expr1, expr2 ... )
db2 : INSERT INTO tbl_name SET col1=expr1, col2=expr2 ... |
Correspondending to create multi-lingual scripts you can create
a array with queries like this:
$statement['db1']['INSERT_PERSON'] = "INSERT INTO person ( surname, name, age ) VALUES ( ?, ?, ? )" ;
$statement['db2']['INSERT_PERSON'] = "INSERT INTO person SET surname=?, name=?, age=?" ; |
Prepare
To use the features give
in Purpose
you have to do two steps. Step one is to
prepare the statment and the second is
to excute it.
Prepare() has to be called with the generic
statment at least once. It returns a handle for the statment.
To create a generic statment is simple. Write the SQL query
as usual, i.e.
SELECT surname, name, age FROM person
WHERE name = 'name_to_find' AND age < 'age_limit' |
Now check which parameters should be replaced while script
runtime. Substitute this parameters with a placeholder.
SELECT surname, name, age FROM person WHERE name = ? AND age < ? |
So, thats all! Now you have a generic statement, required by
prepare().
Prepare() can handle different types of
placeholders or wildcards.
|
? - (recommended) stands for a scalar value like strings
or numbers, the value will be quoted depending of the database
|
|
! - stands for a scalar value and will inserted into the
statement „as is“.
|
|
& - requires an existing filename, the content of this file
will be included into the statment (i.e. for saving binary
data of a graphic file in a database)
|
Execute/ ExecuteMultiple
After preparing the statement, you can excute the query. This
means to assign the variables to the prepared statement. To do
this, execute() requires two arguments, the
statement handle of prepare() and an array
with the values to assign. The array has to be numerically
ordered. The first entry of the array represents the first
wildcard, the second the second wildcard etc. The order is
independent from the used wildcard char.
<?php
// Example inserting data
$alldata = array( array(1, 'one', 'en'),
array(2, 'two', 'to'),
array(3, 'three', 'tre'),
array(4, 'four', 'fire'));
$sth = $dbh->prepare("INSERT INTO numbers VALUES(?,?,?)");
foreach ($alldata as $row) {
$dbh->execute($sth, $row);
}
?> |
In the example the query is done four times:
INSERT INTO numbers VALUES( '1', 'one', 'en')
INSERT INTO numbers VALUES( '2', 'two', 'to')
INSERT INTO numbers VALUES( '3', 'three', 'tre')
INSERT INTO numbers VALUES( '4', 'four', 'fire') |
ExecuteMultiple() works in the same way, but
requires a two dimensional array. So you can avoid the explicit
foreach in the eample above.
<?php
// Example inserting data
$alldata = array( array(1, 'one', 'en'),
array(2, 'two', 'to'),
array(3, 'three', 'tre'),
array(4, 'four', 'fire'));
$sth = $dbh->prepare("INSERT INTO numbers VALUES(?,?,?)");
$dbh->executeMultiple($sth, $alldata);
}
?> |
The result is the same. If one of the records failed, the
unfinished records will not be executed.
If execute*() fails a
DB_Error, else a
DB_OK will returned.