Introduction - autoPrepare & autoExecute
Prev		Next

Introduction - autoPrepare & autoExecute

Introduction - autoPrepare & autoExecute – Automatically prepare and execute SQL statements

Description

Purpose

autoPrepare() and autoExecute() reduce the need to write boring INSERT or UPDATE SQL queries which are difficult to maintain when you add a field for instance.

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 or update queries. For example:


<?php
// Once you have a valid DB object named $db...
$table_name   = 'user';
$table_fields = array('id', 'name', 'country');

$sth = $db->autoPrepare($table_name, $table_fields,
                        DB_AUTOQUERY_INSERT);

if (PEAR::isError($sth)) {
    die($sth->getMessage());
}
?>

In this example, autoPrepare() will build the following SQL query:

INSERT INTO user (id, name, country) VALUES (?, ?, ?)

And then, it will call prepare() with it.

To add records, you have to use execute() or executeMultiple() like:


<?php
// ... contining from the example above...
$table_values = array(1, 'Fabien', 'France');

$res =& $db->execute($sth, $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 queries too. For flexibility reasons, you have only to write the WHERE clause of the query. For instance:


<?php
// Once you have a valid DB object named $db...
$table_name   = 'user';
$table_fields = array('name', 'country');
$table_values = array('Bob', 'USA');

$sth = $db->autoPrepare($table_name, $table_fields,
                        DB_AUTOQUERY_UPDATE, 'id = 1');

if (PEAR::isError($sth)) {
    die($sth->getMessage());
}

$res =& $db->execute($sth, $table_values);

if (PEAR::isError($res)) {
    die($res->getMessage());
}
?>

autoPrepare() will build the following query:

UPDATE user SET name=?, country=? WHERE id=1

Then, it will call prepare() with it.

Be careful, if you don't specify any WHERE clause, all the records of the table will be updated.

autoExecute

Using autoExecute() is the easiest way to do insert or update queries. It is a mix of autoPrepare() and execute().

You only need an associative array (key => value) where keys are fields names and values are corresponding values of these fields. For instance:


<?php
// Once you have a valid DB object named $db...
$table_name = 'user';

$fields_values = array(
    'id'      => 1,
    'name'    => 'Fabien',
    'country' => 'France'
);

$res = $db->autoExecute($table_name, $fields_values,
                        DB_AUTOQUERY_INSERT);

if (PEAR::isError($res)) {
    die($res->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 DB object named $db...
$table_name = 'user';

$fields_values = array(
    'name'    => 'Fabien',
    'country' => 'France'
);

$res = $db->autoExecute($table_name, $fields_values,
                        DB_AUTOQUERY_UPDATE, 'id = 1');

if (PEAR::isError($res)) {
    die($res->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.

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.

Prev	DB	Next
Introduction - Prepare & Execute	PEAR Manual	Introduction - Portability