autoPrepare & autoExecute

autoPrepare & autoExecute – SQL 文を自動的に準備・実行する

Description

目的

autoPrepare() および autoExecute() は、うんざりするような INSERTUPDATEDELETESELECT 文を書く手間を軽減します。 これらの SQL 文を使用していると、例えばテーブルにフィールドを追加した場合などの メンテナンスが大変ですよね? autoPrepare() および autoExecute() を使用するには、 Extended モジュールを 使用する必要があります。

'user' テーブルに次の 3 つのフィールド (id, name そして country) があるとしましょう。 きっと、こんな SQL クエリを書くことになるでしょう。

INSERT INTO table (id, name, country) VALUES (?, ?, ?)
UPDATE table SET id=?, name=?, country=? WHERE ...

ここでフィールドを追加 (例えば 'birthYear' など) したとすると、 クエリを書き直さなければなりません。これはうんざりするような作業であり、 (ひとつだけクエリの修正を忘れてしまうなどの) バグの元となります。

autoPrepare

autoPrepare() を使用すると、insert や update、delete そして select といったクエリを書く必要がなくなります。例を見てみましょう。

<?php
// すでに $mdb2 という名前の 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_INSERTnull$types);

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

この例では、 autoPrepare() は以下の SQL クエリを作成します。

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

それから、そのクエリを用いて prepare() をコールします。

レコードを追加するには execute() あるいは executeMultiple() を次のように使用します。

<?php
// ... 上の例からの続きです ...
$table_values = array(1'Fabien''France');

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

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

どうです? SQL の INSERT 文を一切書く必要がないのです! そしてこれは UPDATEDELETE でも同様に動作します。ただし、柔軟性を確保するため、クエリの WHERE 句だけは書かなければなりません。 例えばこのようになります。

<?php
// すでに $mdb2 という名前の MDB2 オブジェクトが存在するものとします
$table_name   'user';

$mdb2->loadModule('Extended');
$sth $mdb2->extended->autoPrepare($table_namenull,
                        
MDB2_AUTOQUERY_DELETE'id = '.$mdb2->quote(1'integer'));

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

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

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

autoPrepare() は、次のようなクエリを作成します。

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

それから、そのクエリを使用して prepare() をコールします。

注意しなければならないのは、もし WHERE 句を指定しなければ、 テーブルの全レコードが更新されてしまうということです。

autoExecute

insert、update、delete あるいは select クエリを実行する最も簡単な方法が、 autoExecute() をコールすることです。 これは autoPrepare() execute() を組み合わせたものです。

必要なのは連想配列だけです。連想配列のキーにフィールド名、 対応する値としてそのフィールドの値を指定します。 これは、insert や update クエリの場合にのみ影響します。 例を見てみましょう。

<?php
// すでに $mdb2 という名前の 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_INSERTnull$types);

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

たったこれだけです! これで、次のクエリが作成され、そして実行されます。

INSERT INTO user (id, name, country)
  VALUES (1, 'Fabien', 'France')

UPDATE クエリについても同様です。

<?php
// すでに $mdb2 という名前の 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());
}
?>

これで、以下のクエリが作成され、そして実行されます。

UPDATE user SET name='Fabien', country='France'
  WHERE id = 1

注意しなければならないのは、もし WHERE 句を指定しなければ、 テーブルの全レコードが更新されてしまうということです。

これが DELETE クエリの例です。

<?php
// すでに $mdb2 という名前の MDB2 オブジェクトが存在するものとします
$table_name 'user';

$mdb2->loadModule('Extended');
$affectedRows $mdb2->extended->autoExecute($table_namenull,
                        
MDB2_AUTOQUERY_DELETE'id = '.$mdb2->quote(1'integer'));

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

?>

これで、以下のクエリが作成され、そして実行されます。

DELETE FROM user WHERE id = 1

最後に SELECT クエリの例を見てみましょう。

<?php
// すでに $mdb2 という名前の MDB2 オブジェクトが存在するものとします
$table_name 'user';

// 配列を指定しない場合は、'*' を使用してすべてのフィールドを取得します。
// その場合、この変数を true に設定すると自動的に型の判別を行います。
$result_types = array(
    
'name'    => 'text',
    
'country' => 'text'
);

$mdb2->loadModule('Extended');
$res $mdb2->extended->autoExecute($table_namenull,
                        
MDB2_AUTOQUERY_SELECT'id = '.$mdb2->quote(1'integer'),
                        
nulltrue$result_types);

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

$row $res->fetchRow();

?>

これで、以下のクエリが作成され、そして実行されます。

SELECT name, country FROM user WHERE id = 1

$data に渡す値はリテラルでなければなりません。 SQL の関数 (例えば CURDATE() など) を使用しないでください。 実行時に使用する SQL 関数は、プリペアドステートメントに 埋め込んでおく必要があります。

データベースの構造を管理するためのモジュール (Previous) データベースの可搬性に関する機能 (Next)
Last updated: Fri, 19 Dec 2014 — 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
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
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: joshlangley
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.