autoPrepare & autoExecute -- Automatically prepare and execute SQL statements
Description
Purpose
autoPrepare() and
autoExecute()
reduce the need to write boring
INSERT, UPDATE, DELETE
or SELECT
SQL queries which are difficult to maintain when you
add a field for instance. It requires the
use of the
Extended module
Imagine you have a 'user' table with 3 fields
(id, name and country).
You have to write sql queries like:
INSERT INTO table (id, name, country) VALUES (?, ?, ?)
UPDATE table SET id=?, name=?, country=? WHERE ...
If you add a field ('birthYear' for example), you have to rewrite your
queries which is boring and can lead to bugs (if you forget one query for
instance).
autoPrepare
With autoPrepare(), you don't have to write your
insert, update, delete or select queries. For example:
<?php
// Once you have a valid MDB2 object named $mdb2...
$table_name = 'user';
$table_fields = array('id', 'name', 'country');
$types = array('integer', 'text', 'text');
$mdb2->loadModule('Extended');
$sth = $mdb2->extended->autoPrepare($table_name, $table_fields,
MDB2_AUTOQUERY_INSERT, null, $types);
if (PEAR::isError($sth)) {
die($sth->getMessage());
}
?>
In this example, autoPrepare() will build the following SQL query:
INSERT INTO user (id, name, country) VALUES (?, ?, ?)
<?php
// ... continuing from the example above...
$table_values = array(1, 'Fabien', 'France');
$res =& $sth->execute($table_values);
if (PEAR::isError($res)) {
die($res->getMessage());
}
?>
So, you don't have to write any SQL INSERT
queries! And it works with UPDATE and DELETE
queries too. For flexibility reasons, you have only to write
the WHERE clause of the query. For instance:
<?php
// Once you have a valid MDB2 object named $mdb2...
$table_name = 'user';
$mdb2->loadModule('Extended');
$sth = $mdb2->extended->autoPrepare($table_name, null,
MDB2_AUTOQUERY_DELETE, 'id = '.$mdb2->quote(1, 'integer'));
if (PEAR::isError($sth)) {
die($sth->getMessage());
}
$res =& $sth->execute($table_values);
if (PEAR::isError($res)) {
die($res->getMessage());
}
?>
You only need an associative array (key => value) where keys are fields
names and values are corresponding values of these fields. This is only
relevant for insert and update queries.
For instance:
<?php
// Once you have a valid MDB2 object named $mdb2...
$table_name = 'user';
$fields_values = array(
'id' => 1,
'name' => 'Fabien',
'country' => 'France'
);
$types = array('integer', 'text', 'text');
$mdb2->loadModule('Extended');
$affectedRows = $mdb2->extended->autoExecute($table_name, $fields_values,
MDB2_AUTOQUERY_INSERT, null, $types);
if (PEAR::isError($affectedRows)) {
die($affectedRows->getMessage());
}
?>
And that's all! The following query is built and executed:
INSERT INTO user (id, name, country)
VALUES (1, 'Fabien', 'France')
And it's the same thing for UPDATE queries:
<?php
// Once you have a valid MDB2 object named $mdb2...
$table_name = 'user';
$fields_values = array(
'name' => 'Fabien',
'country' => 'France'
);
$types = array('text', 'text');
$mdb2->loadModule('Extended');
$affectedRows = $mdb2->extended->autoExecute($table_name, $fields_values,
MDB2_AUTOQUERY_UPDATE, 'id = '.$mdb2->quote(1, 'integer'), $types);
if (PEAR::isError($affectedRows)) {
die($affectedRows->getMessage());
}
?>
which prepares and executes the following query:
UPDATE user SET name='Fabien', country='France'
WHERE id = 1
Be careful, if you don't specify any WHERE
statement, all the records
of the table will be updated.
Here is an example for a DELETE queries:
<?php
// Once you have a valid MDB2 object named $mdb2...
$table_name = 'user';
$mdb2->loadModule('Extended');
$affectedRows = $mdb2->extended->autoExecute($table_name, null,
MDB2_AUTOQUERY_DELETE, 'id = '.$mdb2->quote(1, 'integer'));
if (PEAR::isError($affectedRows)) {
die($affectedRows->getMessage());
}
?>
which prepares and executes the following query:
DELETE FROM user WHERE id = 1
Finally an example for a SELECT queries:
<?php
// Once you have a valid MDB2 object named $mdb2...
$table_name = 'user';
// if left as a non array all fields of the table will be fetched using '*'
// in that case this variable can be set to true, to autodiscover the types
$result_types = array(
'name' => 'text',
'country' => 'text'
);
$mdb2->loadModule('Extended');
$res = $mdb2->extended->autoExecute($table_name, null,
MDB2_AUTOQUERY_SELECT, 'id = '.$mdb2->quote(1, 'integer'),
null, true, $result_types);
if (PEAR::isError($res)) {
die($res->getMessage());
}
$row = $res->fetchRow();
?>
which prepares and executes the following query:
SELECT name, country FROM user WHERE id = 1
Avertissement
The values passed in $data must be literals.
Do not submit SQL functions (for example CURDATE()).
SQL functions that should be performed at execution time need
to be put in the prepared statement.
