autoPrepare & autoExecute

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 (?, ?, ?)

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

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


<?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());
}
?>

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, update, delete or select 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. 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

You can also use placeholders in the WHERE clause and pass the values like this:


<?php
$id = 1;
$mdb2->autoExecute('table_name', array($id), MDB2_AUTOQUERY_DELETE, 'id = ?');
?>

which prepares the following query and passes the $id parameter on execute:

DELETE FROM table_name WHERE id = ?

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.

See

"Intro - Modules"

Reverse Module (Previous) Portability (Next)

Last updated: Tue, 02 Jun 2009 — Download Documentation

Do you think that something on this page is wrong? Please file a bug report or add a note.

View this page in:

User Notes:

Note by: prometheus

2010-08-19 19:52 UTC

I found it extremely time-consuming to figure out how to use autoPrepare with SELECT statements. The examples in the documentation are not useful. First, they execute a select but I have not been able to figure out how to get records out of an execute. The number of records that are selected is returned. Yes, that is useful sometimes but is not what is usually needed from a SELECT.

Here is a small program which demonstrates autoPrepare with SELECT. Please note that you MUST pass that sixth argument of MDB2_PREPARE_RESULT or autoPrepare defaults to MDB2_PREPARE_MANIP, which again returns the number of records affected. Why the code for autoPrepare cannot infer the correct setting for that argument based on the MDB2_AUTOQUERY_SELECT constant is a good question. I understand the desire to allow developers the flexibility to override it, but the default should make sense. For SELECT statements MDB2_PREPARE_RESULT makes sense and is NOT the default. I also note that the source code for autoPrepare mentions the MDB2_AUTOQUERY_SELECT option but does not mention that it can be used to create SELECT statements.


<?php

   require_once ('MDB2.php'); // for PEAR php



    $dsn="mysql://user:password@localhost/test_db";

    $options = array(

        'debug'       => 3,

        'portability' => MDB2_PORTABILITY_NONE,

    );



    $db = MDB2::connect($dsn, $options);



    if(PEAR::isError($db)) {

        die( $db->getMessage() );

    }



    $db->loadModule('Extended');



    $sth = $db->autoPrepare(

        'cartoon_chars',

         null,               // will get ALL the fields just like '*'

         MDB2_AUTOQUERY_SELECT,

         'cc_id = ?',

         null,

         MDB2_PREPARE_RESULT

    );



    if (PEAR::isError($sth)) {

        die($sth->getMessage());

    }



    echo 'D ' . $db->getDebugOutput() . '<BR/>';



    $result = $sth->execute(array(2));

    $_row = $result->fetchRow();

    print_r($_row);

?>

Here is the code to set up the database needed for this example:

CREATE TABLE `test_db`.`cartoon_chars` (
`cc_id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`name` VARCHAR( 32 ) NOT NULL ,
`animal` VARCHAR( 32 ) NOT NULL
) ENGINE = MYISAM ;

INSERT INTO `test_db`.`cartoon_chars`
( `cc_id` , `name` , `animal` )
VALUES ( '1', 'Scooby Doo', 'dog' );

INSERT INTO `test_db`.`cartoon_chars`
( `cc_id` , `name` , `animal` )
VALUES ( '2', 'Mickey Mouse', 'mouse' );

I hope this saves someone else time and frustration.

Note by: prometheus

2010-08-19 19:33 UTC


<?php

   require_once ('MDB2.php'); // for PEAR php



    $dsn="mysql://user:password@localhost/test_db";

    $options = array(

        'debug'       => 3,

        'portability' => MDB2_PORTABILITY_NONE,

    );



    $db = MDB2::connect($dsn, $options);



    if(PEAR::isError($db)) {

        die( $db->getMessage() );

    }



    $db->loadModule('Extended');



    $sth = $db->autoPrepare(

        'cartoon_chars',

         null,               // will get ALL the fields just like '*'

         MDB2_AUTOQUERY_SELECT,

         'cc_id = ?',

         null,

         MDB2_PREPARE_RESULT

    );



    if (PEAR::isError($sth)) {

        die($sth->getMessage());

    }



    echo 'D ' . $db->getDebugOutput() . '<BR/>';



    $result = $sth->execute(array(2));

    $_row = $result->fetchRow();

    print_r($_row);

?>

Note by: joshlangley

2007-12-13 15:41 UTC

I checked the execution times of a couple of SELECT statements , using autoExecute, and forming raw queries from scratch. I found that if the query is very simple (a few fields at most), then it is often faster to use raw queries.