Description
Purpose
prepareQuery()
and
executeQuery*()
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 ... |
Corresponding 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 above, you have to do two steps. Step one is to
prepareQuery the statement and the second is
to executeQuery it.
Prepare()
has to be called with the generic
statement at least once. It returns a handle for the statement.
To create a generic statement 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
prepareQuery()
.
prepareQuery()
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 statement
(i.e. for saving binary data of a graphic file in a database)
|
Execute/ ExecuteMultiple
After preparing the statement, you can execute the query. This
means to assign the variables to the prepared statement. To do
this,
executeQuery()
requires two arguments, the
statement handle of
prepareQuery()
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.
Пример 35-1. Inserting data into a datebase
$alldata = array( array(1, 'one', 'en'),
array(2, 'two', 'to'),
array(3, 'three', 'tre'),
array(4, 'four', 'fire'));
$sth = $dbh->prepareQuery("INSERT INTO numbers VALUES(?,?,?)");
foreach ($alldata as $row) {
$dbh->executeQuery($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.
Пример 35-2.
Using executeMultiple() instead of
executeQuery()
...
$alldata = array( array(1, 'one', 'en'),
array(2, 'two', 'to'),
array(3, 'three', 'tre'),
array(4, 'four', 'fire'));
$sth = $dbh->prepareQuery("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
executeQuery*()
fails a
MDB_Error, else MDB_OK will returned.