PHP TOP

PDOStatement::fetch »

« PDOStatement::errorInfo

PDOStatement::execute

(PHP 5 >= 5.1.0, PHP 7, PHP 8, PECL pdo >= 0.1.0)

PDOStatement::execute — プリペアドステートメントを実行する

説明

public PDOStatement::execute(?array $params = null): bool

プリペアドステートメントを実行します。もし、プリペアドステートメントがパラメータマーカを含む場合、次のいずれかを行わなければなりません。

パラメータマーカに PHP 変数や値を(それぞれ)バインドするため PDOStatement::bindParam() や PDOStatement::bindValue() をコールする。関連づけされたパラメータマーカがあれば、バインドされた変数は入力値を渡すもしくは出力値を受け取ります。
あるいは、入力専用のパラメータ値の配列を渡す

パラメータ

params

実行される SQL 文の中のバインドパラメータと同数の要素からなる、値の配列。すべての値は PDO::PARAM_STR として扱われます。

ひとつのパラメータに対して複数の値をバインドすることはできません。例えば、IN() 句の中のひとつのパラメータに対して 2 つの値をバインドすることはできません。

指定した数よりも多い値をバインドすることはできません。 params のキーが PDO::prepare() で指定した SQL にある数より多い場合は、ステートメントが失敗してエラーが発生します。

戻り値

成功した場合に true を、失敗した場合に false を返します。

エラー / 例外

PDO::ATTR_ERRMODE が PDO::ERRMODE_WARNING に設定されていた場合、E_WARNING レベルのエラーが発生します。

PDO::ATTR_ERRMODE が PDO::ERRMODE_EXCEPTION に設定されていた場合、PDOException がスローされます。

例

例1 変数や値のバインドを伴うプリペアドステートメントの実行

<?php
/* 変数や値のバインドを伴うプリペアドステートメントの実行 */
$calories = 150;
$colour = 'gre';
$sth = $dbh->prepare('SELECT name, colour, calories
    FROM fruit
    WHERE calories < :calories AND colour LIKE :colour');
$sth->bindParam('calories', $calories, PDO::PARAM_INT);
/* 名前の前にも、コロン ":" を付けることができます(オプション) */
$sth->bindValue(':colour', "%$colour%");
$sth->execute();
?>

例2 名前付きの値を伴うプリペアドステートメントの実行

<?php
/* 入力値の配列を伴うプリペアドステートメントの実行 */
$calories = 150;
$colour = 'red';
$sth = $dbh->prepare('SELECT name, colour, calories
    FROM fruit
    WHERE calories < :calories AND colour = :colour');
$sth->execute(array('calories' => $calories, 'colour' => $colour));
/* 配列のキーの前にも、コロン ":" を付けることができます(オプション) */
$sth->execute(array(':calories' => $calories, ':colour' => $colour));
?>

例3 位置を指定した値を伴うプリペアドステートメントの実行

<?php
/* 入力値の配列を伴うプリペアドステートメントの実行 */
$calories = 150;
$colour = 'red';
$sth = $dbh->prepare('SELECT name, colour, calories
    FROM fruit
    WHERE calories < ? AND colour = ?');
$sth->execute(array($calories, $colour));
?>

例4 位置を指定したプレースホルダに、値をバインドした形でのプリペアドステートメントの実行

<?php
/* バインド変数を伴うプリペアドステートメントの実行 */
$calories = 150;
$colour = 'red';
$sth = $dbh->prepare('SELECT name, colour, calories
    FROM fruit
    WHERE calories < ? AND colour = ?');
$sth->bindParam(1, $calories, PDO::PARAM_INT);
$sth->bindParam(2, $colour, PDO::PARAM_STR, 12);
$sth->execute();
?>

例5 IN 句で配列を使うプリペアドステートメントの実行

<?php
/* IN 句で配列を使うプリペアドステートメントの実行 */
$params = array(1, 21, 63, 171);
/* パラメータのプレースホルダ用の文字列を、パラメータ数に合わせて作ります */
$place_holders = '?' . str_repeat(', ?', count($params) - 1);

/*
    これは、配列 $params のすべての値のための名前なしプレースホルダを含む
    ステートメントを準備します。配列 $params の値をそこにバインドし、それから
    ステートメントを実行します。これは PDOStatement::bindParam() を使うのとは
    違います。こちらの場合は変数への参照が必要になるからです。
    PDOStatement::execute() は、単に値だけをバインドします。
*/
$sth = $dbh->prepare("SELECT id, name FROM contacts WHERE id IN ($place_holders)");
$sth->execute($params);
?>

注意

注意:
ドライバによっては、次のステートメントを実行する前にカーソルを閉じなければならないものもあります。

参考

PDO::prepare() - 文を実行する準備を行い、文オブジェクトを返す
PDOStatement::bindParam() - 指定された変数名にパラメータをバインドする
PDOStatement::fetch() - 結果セットから次の行を取得する
PDOStatement::fetchAll() - 結果セットから、残っている行を取得する
PDOStatement::fetchColumn() - 結果セットの次行から単一カラムを返す

Found A Problem?

Learn How To Improve This Page • Submit a Pull Request • Report a Bug

＋add a note

User Contributed Notes 27 notes

down

Jean-Lou dot Dupont at jldupont dot com ¶

16 years ago

Hopefully this saves time for folks: one should use $count = $stmt->rowCount() after $stmt->execute() in order to really determine if any an operation such as ' update ' or ' replace ' did succeed i.e. changed some data.

Jean-Lou Dupont.

down

gx ¶

14 years ago

Note that you must

- EITHER pass all values to bind in an array to PDOStatement::execute()

- OR bind every value before with PDOStatement::bindValue(), then call PDOStatement::execute() with *no* parameter (not even "array()"!).

Passing an array (empty or not) to execute() will "erase" and replace any previous bindings (and can lead to, e.g. with MySQL, "SQLSTATE[HY000]: General error: 2031" (CR_PARAMS_NOT_BOUND) if you passed an empty array).



Thus the following function is incorrect in case the prepared statement has been "bound" before:



<?php

function customExecute(PDOStatement &$sth, $params = NULL) {

    return $sth->execute($params);

}

?>



and should therefore be replaced by something like:



<?php

function customExecute(PDOStatement &$sth, array $params = array()) {

    if (empty($params))

        return $sth->execute();

    return $sth->execute($params);

}

?>



Also note that PDOStatement::execute() doesn't require $input_parameters to be an array.



(of course, do not use it as is ^^).

down

VolGas ¶

18 years ago

An array of insert values (named parameters) don't need the prefixed colon als key-value to work.

<?php
/* Execute a prepared statement by passing an array of insert values */
$calories = 150;
$colour = 'red';
$sth = $dbh->prepare('SELECT name, colour, calories
   FROM fruit
   WHERE calories < :calories AND colour = :colour');
// instead of:
//     $sth->execute(array(':calories' => $calories, ':colour' => $colour));
// this works fine, too:
$sth->execute(array('calories' => $calories, 'colour' => $colour));
?>

This allows to use "regular" assembled hash-tables (arrays).
That realy does make sense!

down

ElTorqiro ¶

13 years ago

When using a prepared statement to execute multiple inserts (such as in a loop etc), under sqlite the performance is dramatically improved by wrapping the loop in a transaction.



I have an application that routinely inserts 30-50,000 records at a time.  Without the transaction it was taking over 150 seconds, and with it only 3.



This may affect other implementations as well, and I am sure it is something that affects all databases to some extent, but I can only test with PDO sqlite.



e.g.



<?php

$data = array(

  array('name' => 'John', 'age' => '25'),

  array('name' => 'Wendy', 'age' => '32')

);



try {

  $pdo = new PDO('sqlite:myfile.sqlite');

}



catch(PDOException $e) {

  die('Unable to open database connection');

}



$insertStatement = $pdo->prepare('insert into mytable (name, age) values (:name, :age)');



// start transaction

$pdo->beginTransaction();



foreach($data as &$row) {

  $insertStatement->execute($row);

}



// end transaction

$pdo->commit();



?>



[EDITED BY sobak: typofixes by Pere submitted on 12-Sep-2014 01:07]

down

Sbastien ¶

1 year ago

Strangely the manual doesn't give a full SELECT example.

<?php

$sql = <<<SQL
    SELECT ALL name, calories, colour
    FROM fruit
    WHERE calories < :calories AND colour = :colour
    SQL;

$select = $pdo->prepare($sql);

$select->execute(['calories' => 150, 'colour' => 'red']);

$data = $select->fetchAll();

down

albright atat anre dotdot net ¶

16 years ago

When passing an array of values to execute when your query contains question marks, note that the array must be keyed numerically from zero. If it is not, run array_values() on it to force the array to be re-keyed.

<?php
$anarray = array(42 => "foo", 101 => "bar");
$statement = $dbo->prepare("SELECT * FROM table WHERE col1 = ? AND col2 = ?");

//This will not work
$statement->execute($anarray);

//Do this to make it work
$statement->execute(array_values($anarray));
?>

down

Ray.Paseur sometimes uses Gmail ¶

8 years ago

"You cannot bind more values than specified; if more keys exist in input_parameters than in the SQL specified in the PDO::prepare(), then the statement will fail and an error is emitted."  However fewer keys may not cause an error.

As long as the number of question marks in the query string variable matches the number of elements in the input_parameters, the query will be attempted.

This happens even if there is extraneous information after the end of the query string.  The semicolon indicates the end of the query string; the rest of the variable is treated as a comment by the SQL engine, but counted as part of the input_parameters by PHP.

Have a look at these two query strings.  The only difference is a typo in the second string, where a semicolon accidentally replaces a comma.  This UPDATE query will run, will be applied to all rows, and will silently damage the table.

<?php
/**
 * Query is intended to UPDATE a subset of the rows based on the WHERE clause
 */
$sql  = "UPDATE my_table SET fname = ?, lname = ? WHERE id = ?";

/**
 * Query UPDATEs all rows, ignoring everything after the semi-colon, including the WHERE clause!
 * 
 * Expected (but not received):
 *
 *** Warning:  
 *** PDOStatement::execute(): 
 *** SQLSTATE[HY093]: 
 *** Invalid parameter number: number of bound variables does not match number of tokens...
 *
 */ 
// Typo here ------------------------ |
//                                    V
$sql  = "UPDATE my_table SET fname = ?; lname = ? WHERE id = ?"; // One token in effect
$pdos = $pdo->prepare($sql);
$pdos->execute( [ 'foo', 'bar', 3 ] );                           // Three input_parameters
?>

PHP 5.4.45, mysqlnd 5.0.10

down

Rami jamleh ¶

11 years ago

simplified $placeholder form 



<?php



$data = ['a'=>'foo','b'=>'bar'];



$keys = array_keys($data);

$fields = '`'.implode('`, `',$keys).'`';



#here is my way 

$placeholder = substr(str_repeat('?,',count($keys)),0,-1);



$pdo->prepare("INSERT INTO `baz`($fields) VALUES($placeholder)")->execute(array_values($data));

down

anon at anon dot com ¶

12 years ago

If your MySQL table has 500,000+ rows and your script is failing because you have hit PHP's memory limit, set the following attribute.



<?php $this->pdo->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false); ?>



This should make the error go away again and return memory usage back to normal.

down

danny dot panzer at gmail dot com ¶

3 years ago

I have found very strange behavior for PostgreSQL:

Outside of a transaction, you can pass boolean true/false as members of the input array and it seems to work.

However, *inside* a transaction, boolean true works but boolean false does not. Instead, pass something "falsey" like integer 0 or string "false"

down

simon dot lehmann at gmx dot de ¶

17 years ago

It seems, that the quoting behaviour has changed somehow between versions, as my current project was running fine on one setup, but throwing errors on another (both setups are very similar).

Setup 1: Ubuntu 6.10, PHP 5.1.6, MySQL 5.0.24a
Setup 2: Ubuntu 7.04, PHP 5.2.1, MySQL 5.0.38

The code fragment which caused problems (shortened):
<?php
$stmt = $pdo->prepare("SELECT col1, col2, col3 FROM tablename WHERE col4=? LIMIT ?");
$stmt->execute(array('Foo', 1));
?>

On the first Setup this executes without any problems, on the second setup it generates an Error:

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''1'' at line 1

The problem is, that $stmt->execute() quotes the number passed to the second placeholder (resulting in: ... LIMIT '1'), which is not allowed in MySQL (tested on both setups).

To prevent this, you have to use bindParam() or bindValue() and specify a data type.

down

T-Rex ¶

12 years ago

When you try to make a query with a date, then take the whole date and not just a number.

This Query will work fine, if you try it like this:
SELECT * FROM table WHERE date = 0

But if you try it with prepared you have to take the whole date format.
<?php
$sth = $dbh->prepare('SELECT * FROM table WHERE date = :date');
$sth->execute( $arArray );

//--- Wrong:
$arArray = array(":date",0);

//--- Right:
$arArray = array(":date","0000-00-00 00:00:00");
?>

There must be something with the mysql driver.

best regards
T-Rex

down

Ihor Ivanov ¶

7 years ago

If one parameter name is missing or misspelled, this function throws an error of level E_WARNING, even when PDO::ATTR_ERRMODE is set to PDO::ERRMODE_SILENT!
In the same situation, but when PDO::ERRMODE_WARNING is set, this function throws TWO errors of level E_WARNING!

This function does not throw any error when PDO::ERRMODE_EXCEPTION is set, instead, it throws a PDOException.

All this applies even when you use PDOStatement::bindParam() function with misspelled parameter name and than use PDOStatement::execute();

Tested on: Windows 10, PHP 5.5.35, mysqlnd 5.0.11, MySQL 5.6.30.

<?php
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_SILENT);

$colour = 'red';
$sth = $dbh->prepare('SELECT name, colour, calories
    FROM fruit
    WHERE colour = :colour');

/*
    Notice the parameter name ':color' instead of ':colour'.

    When PDO::ERRMODE_SILENT is set, this function throws the error:
    Warning: PDOStatement::execute(): SQLSTATE[HY093]: Invalid parameter number: parameter was not defined in...

    When PDO::ERRMODE_WARNING is set, this function throws this two errors:
    Warning: PDOStatement::execute(): SQLSTATE[HY093]: Invalid parameter number: parameter was not defined in...
    Warning: PDOStatement::execute(): SQLSTATE[HY093]: Invalid parameter number in...
*/
$sth->execute(array(':color' => $colour));
?>

down

joshuag at count-recount at dynaggelos dot com ¶

3 years ago

It took me a long while to realize this and the documentation didn't seem very clear on using PDO_Statement::execute() on a SELECT statement type query, so I wanted to note this here. When preparing a SELECT query and then executing it using PDO_Statement::execute(), you can then simply proceed to use PDO_Statement::fetch() or PDO_Statement::fetchAll() on that same PDO_Statement object. This is no different than using PDO::query() to return a PDO_Statement object, and then calling PDO_Statement::fetch() on that object. This is because the PDO_Statement object is of course still a PDO_Statement object, and, as the PDO::query documentation (https://www.php.net/manual/en/pdo.query.php) says, PDO::query also "[p]repares and executes an SQL statement."

<?php

$pdo_statement = $my_pdo_object->prepare( "SELECT * FROM `MyTable` WHERE `Field1` = 'this_string'" );

if ( true === $this->execute_safe_query( $pdo_statement ) ) {

    echo $pdo_statement->fetch();

}

?>

down

richard at securebucket dot com ¶

13 years ago

Note:  Parameters don't work with a dash in the name like ":asd-asd" you can do a quick str_replace("-","_",$parameter) to fix the issue.

down

Whitebeard ¶

10 years ago

If you are having issues passing boolean values to be bound and are using a Postgres database... but you do not want to use bindParam for *every* *single* *parameter*, try passing the strings 't' or 'f' instead of boolean TRUE or FALSE.

down

Atelier ”慶”

WEBクリエイター歴２０年、最先端技術と安心をお届けします。

PHPのお勉強！

PDOStatement::execute

説明

パラメータ

戻り値

エラー / 例外

例

注意

参考

Found A Problem?

User Contributed Notes 27 notes