|
Introduction - Prepare & Execute -- Prepare and execute SQL statements ïÐÉÓÁÎÉÅPurpose
prepare() and
execute*()
give you more power and flexibilty for query execution.
Prepare/execute mode is helpful when you have to run the
same query several times but with different values in it,
such as adding a list of addresses into a database.
Another place prepare/execute is useful is supporting
databases which have different SQL syntaxes.
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:
<?php
$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 above, you have to do two steps. Step one is to
prepare the statement and the second is
to execute it.
To start out, you need to
prepare() a generic
SQL statment. Create a generic statment by writing
the SQL query as usual:
SELECT surname, name, age
FROM person
WHERE name = 'name_to_find' AND age < age_limit |
Then substitute "placeholders" for the
literal values which will be provided at run time:
SELECT surname, name, age
FROM person
WHERE name = ? AND age < ? |
Then pass this SQL statement to
prepare(), which returns
a statement handle to be used when calling
execute().
prepare() can handle different types of
placeholders (a.k.a. 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)
|
Use backslashes to escape placeholder characters if you don't want
them to be interpreted as placeholders:
UPDATE foo SET col=? WHERE col='over \& under' |
Execute
After preparing the statement, you can execute the query. This
means to assign the variables to the prepared statement. To do
this,
execute() requires two arguments:
the statement handle returned by
prepare() and a scalar or array
with the values to assign.
Пример 22-1. Passing scalars to execute()
<?php
// Once you have a valid DB object named $db...
$sth = $db->prepare('INSERT INTO numbers (number) VALUES (?)');
$db->execute($sth, 1);
$db->execute($sth, 8);
?>
|
|
When a prepared statement has multiple placeholders, you must use
an array to pass the values to execute().
The first entry of the array represents the first
placeholder, the second the second placeholder, etc.
The order is independent of the type of placeholder used.
Пример 22-2. Passing an array to execute()
<?php
// Once you have a valid DB object named $db...
$sth = $db->prepare('INSERT INTO numbers VALUES (?, ?, ?)');
$data = array(1, 'one', 'en');
$db->execute($sth, $data);
?>
|
|
ExecuteMultiple
DB contains a process for executing several queries at once.
So, rather than having to execute them manually, like this:
Пример 22-3. Passing arrays to execute()
<?php
// Once you have a valid DB object named $db...
$alldata = array(array(1, 'one', 'en'),
array(2, 'two', 'to'),
array(3, 'three', 'tre'),
array(4, 'four', 'fire'));
$sth = $db->prepare('INSERT INTO numbers VALUES (?, ?, ?)');
foreach ($alldata as $row) {
$db->execute($sth, $row);
}
?>
|
|
which would issue four queries:
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') |
you can use
executeMultiple() to avoid the explicit
foreach in the eample above:
Пример 22-4.
Using executeMultiple() instead of
execute()
<?php
// Once you have a valid DB object named $db...
$alldata = array(array(1, 'one', 'en'),
array(2, 'two', 'to'),
array(3, 'three', 'tre'),
array(4, 'four', 'fire'));
$sth = $db->prepare('INSERT INTO numbers VALUES (?, ?, ?)');
$db->executeMultiple($sth, $alldata);
?>
|
|
The result is the same. If one of the records failed, the
unfinished records will not be executed.
execute*() has three
possible returns:
a new DB_result object
for queries that return results (such as
SELECT queries),
DB_OK for queries that manipulate data (such as
INSERT queries)
or a DB_Error object on failure
|