Description
Purpose
autoPrepare() and
autoExecute()
are facilities. With them, you don't have to write anymore these boring
(insert or update) sql queries which are difficult to maintain when you
add a field for instance.
Imagine you have an '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 :
(...) // $dbh is an DB object
$table_fields = array('id', 'name', 'country');
$table_name = 'user';
$sth = $dbh->autoPrepare($table_name, $table_fields, DB_AUTOQUERY_INSERT); |
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
so to use
execute() or
executeMultiple() like :
(...) // $dbh is an DB object
$table_fields = array('id', 'name', 'country');
$table_values = array(1, 'Fabien', 'France');
$table_name = 'user';
$sth = $dbh->autoPrepare($table_name, $table_fields, DB_AUTOQUERY_INSERT);
$dbh->execute($sth, $table_values); |
So, you don't have to write any sql insert queries ! But it works with
update queries too. For flexibility reasons, you have only to write
the WHERE statement of the query. For instance :
(...) // $dbh is an DB object
$table_fields = array('name', 'country');
$table_values = array('Bob', 'USA');
$table_name = 'user';
$sth = $dbh->autoPrepare($table_name, $table_fields, DB_AUTOQUERY_UPDATE, 'id = 1');
$dbh->execute($sth, $table_values); |
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 statement, all the records
of the table will be updated.
autoExecute
Using
execute() 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 :
(...) // $dbh is an DB object
$fields_values = array(
'id' => 1,
'name' => 'Fabien',
'country' => 'France'
);
$table_name = 'user';
$dbh->autoExecute($table_name, $fields_values, DB_AUTOQUERY_INSERT); |
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 :
(...) // $dbh is an DB object
$fields_values = array(
'name' => 'Fabien',
'country' => 'France'
);
$table_name = 'user';
$dbh->autoExecute($table_name, $fields_values, DB_AUTOQUERY_UPDATE, 'id = 1'); |
The following query is built and executed :
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.