PDOStatement::execute()

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

执行一条预处理语句

说明

执行预处理过的语句。如果预处理过的语句含有参数标记，必须选择下面其中一种做法：

• 调用PDOStatement::bindParam()绑定 PHP 变量到参数标记：如果有的话，通过关联参数标记绑定的变量来传递输入值和取得输出值

• 或传递一个只作为输入参数值的数组

参数

一个元素个数和将被执行的 SQL 语句中绑定的参数一样多的数组。所有的值作为PDO::PARAM_STR对待。

不能绑定多个值到一个单独的参数；比如，不能绑定两个值到 IN（）子句中一个单独的命名参数。

绑定的值不能超过指定的个数。如果在$input_parameters中存在比PDO::prepare()预处理的SQL 指定的多的键名，则此语句将会失败并发出一个错误。

返回值

成功时返回TRUE，或者在失败时返回FALSE。

更新日志

范例

执行一条绑定变量的预处理语句

使用一个含有插入值的数组执行一条预处理语句（命名参数）

使用一个含有插入值的数组执行一条预处理语句（占位符）

执行一条问号占位符的预处理语句

使用数组执行一条含有 IN 子句的预处理语句

注释

有些驱动在执行下一条语句前需要关闭游标。

参见

• PDO::prepare() 准备要执行的语句，并返回语句对象
• PDOStatement::bindParam() 绑定一个参数到指定的变量名
• PDOStatement::fetch() 从结果集中获取下一行
• PDOStatement::fetchAll() 返回一个包含结果集中所有行的数组
• PDOStatement::fetchColumn() 从结果集中的下一行返回单独的一列。

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.

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:

and should therefore be replaced by something like:

Also note that PDOStatement::execute() doesn't require $input_parameters to be an array.
(of course, do not use it as is ^^).

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

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

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.

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

simplified $placeholder form 

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.

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

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.

There must be something with the mysql driver.
best regards
T-Rex

"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 5.4.45, mysqlnd 5.0.10

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. 

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):

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.

We know that you can't see the final raw SQL before its parsed by the DB, but if you want to simulate the final result, this may help. 

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.

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.

I realized that I ran into serious trouble when debugging my PHP scripts from the command line, and despite of going to fetchAll and so, I always got the error 
SQLSTATE[HY000]: General error: 2014 Cannot execute queries while other unbuffered queries are active.
I realized that I had a double init command: 
PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8; SET CHARACTER SET utf8;"
The first one is the better choice and removing the latter, the error is gone.

If you're going to derive PDOStatement to extend the execute() method, you must define the signature with a default NULL argument, not an empty array.
In otherwords:

鹏仔微信 15129739599 鹏仔QQ344225443 鹏仔前端 pjxi.com 共享博客 sharedbk.com