©
本文檔使用 php中文網手册 發布
(PHP 5 >= 5.1.0, PECL pdo >= 0.1.0)
PDOStatement::execute — 执行一条预处理语句
$input_parameters
] )执行预处理过的语句。如果预处理过的语句含有参数标记,必须选择下面其中一种做法:
调用 PDOStatement::bindParam() 绑定 PHP 变量到参数标记:如果有的话,通过关联参数标记绑定的变量来传递输入值和取得输出值
或传递一个只作为输入参数值的数组
input_parameters
一个元素个数和将被执行的 SQL 语句中绑定的参数一样多的数组。所有的值作为 PDO::PARAM_STR
对待。
不能绑定多个值到一个单独的参数;比如,不能绑定两个值到 IN()子句中一个单独的命名参数。
绑定的值不能超过指定的个数。如果在 input_parameters
中存在比 PDO::prepare() 预处理的SQL 指定的多的键名,则此语句将会失败并发出一个错误。
成功时返回 TRUE
, 或者在失败时返回 FALSE
。
版本 | 说明 |
---|---|
5.2.0 | input_parameters 中的键名必须和 SQL 中声明的相匹配。PHP 5.2.0 之前默认忽略。
|
Example #1 执行一条绑定变量的预处理语句
<?php
$calories = 150 ;
$colour = 'red' ;
$sth = $dbh -> prepare ( 'SELECT name, colour, calories
FROM fruit
WHERE calories < :calories AND colour = :colour' );
$sth -> bindParam ( ':calories' , $calories , PDO :: PARAM_INT );
$sth -> bindParam ( ':colour' , $colour , PDO :: PARAM_STR , 12 );
$sth -> execute ();
?>
Example #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 ));
?>
Example #3 使用一个含有插入值的数组执行一条预处理语句(占位符)
<?php
$calories = 150 ;
$colour = 'red' ;
$sth = $dbh -> prepare ( 'SELECT name, colour, calories
FROM fruit
WHERE calories < ? AND colour = ?' );
$sth -> execute (array( $calories , $colour ));
?>
Example #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 ();
?>
Example #5 使用数组执行一条含有 IN 子句的预处理语句
<?php
$params = array( 1 , 21 , 63 , 171 );
$place_holders = implode ( ',' , array_fill ( 0 , count ( $params ), '?' ));
$sth = $dbh -> prepare ( "SELECT id, name FROM contacts WHERE id IN ( $place_holders )" );
$sth -> execute ( $params );
?>
Note:
有些驱动在执行下一条语句前需要 关闭游标 。
[#1] 434593063 at qq dot com [2015-12-01 02:18:36]
???:?????????????????·????1С??3??602??? ????621558260800076657
[#2] pere dot pasqual at gmail dot com [2015-06-11 15:02:15]
It's been 7 years since simon dot lehmann at gmx dot comment, but today I found myself having problems with a prepared statement involving an INSERT, PDO odbc driver for Microsoft Access and PHP 5.4.7. The prepared statement was done using the prepare + execute method, throwing an ugly
"SQLExecDirect[-3500] at ext\\pdo_odbc\\odbc_driver.c:247" error
and a
42000 ("Syntax error or access violation") SQLSTATE.
He suspects what the problem is and points to a possible solution: using bindParam() or bindValue() and specify a data type.
Well, that seems to be right identifying the source of the problem, but there is a simpler solution that worked for me, simpler and that allows you to continue using pdo::prepare() with ? as parameters and pdo::execute():
the only thing you have to do is, if not done before, a cast of the binded parameters to its specific type (the type that the database is expecting) before putting them in the array you pass to pdo::execute($array).
The following code fails, throwing the error above:
<?php
$name = "John";
$length = "1";
$price = "1.78";
$SQL = "INSERT INTO table (name, length, price) VALUES (?,?,?)";
$arra = array($name, $length, $price);
$sth = $msq->prepare($SQL);
$sth->execute($arra);
?>
This one works for me like a charm:
<?php
$name = "John";
$length = (int)"1"; // the database is expecting this type
$price = (float)"1.78"; // the database is expecting this type
$SQL = "INSERT INTO table (name, length, price) VALUES (?,?,?)";
$arra = array($name, $length, $price);
$sth = $msq->prepare($SQL);
$sth->execute($arra);
?>
[#3] Clair Shaw [2015-03-17 15:15:52]
Rami's code snippet is a very good solution for using an associated array to insert a line.
However, there's a small, but hard to spot, syntax error which may take a few moments to figure out what's gone wrong. So I thought I should provide the corrected line:
Instead of:
$placeholder = substr(str_repeat('?,',count($keys),0,-1));
The correct code you'll want to use is:
$placeholder = substr(str_repeat('?,',count($keys)),0,-1);
(Notice that substr has 3 parameters, and str_repeat has 2).
[#4] takingsides at gmail dot com [2014-12-05 11:10:17]
Debugging prepared statements can be a pain sometimes when you need to copy a query and run it in the DB directly. The function below is an example of how to compile your own query (of course it would need some tweaking and may not work in all scenarios).
<?php
$sql = "
SELECT t1.*
FROM table1 AS t1
INNER JOIN table2 AS t2 ON (
t2.code = t1.code
AND t1.field1 = ?
AND t1.field2 = ?
AND t1.field3 = ?
)
";
$stmt = $pdo->prepare($sql);
$params = [ 'A', 'B', 'C' ];
$stmt->execute($params);
// Output the compiled query
debug($sql, $params);
function debug($statement, array $params = [])
{
$statement = preg_replace_callback(
'/[?]/',
function ($k) use ($params) {
static $i = 0;
return sprintf("'%s'", $params[$i++]);
},
$statement
);
echo '<pre>Query Debug:<br>', $statement, '</pre>';
}
?>
This would output something like:
SELECT t1.*
FROM table1 AS t1
INNER JOIN table2 AS t2 ON (
t2.part_code = t1.code
AND t1.field1 = 'A'
AND t1.field2 = 'B'
AND t1.field3 = 'C'
)
[#5] Pere [2014-09-12 13:07:10]
If you are trying to use ElTorqiro example, there is a bug in the code. Where it says
<?php
$pdo->execute($row);
?>
it should say
<?php
$insertStatement->execute($row);
?>
[#6] Whitebeard [2014-04-01 19:27:47]
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.
[#7] Rami jamleh [2013-04-02 11:09:15]
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));
[#8] anon at anon dot com [2012-06-15 09:10:21]
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.
[#9] mail at horn-online-media dot de [2012-06-14 10:32:27]
hi,
just a qick note to get started without problems when using quotation: PDO does NOT replace given variables if they are wrapped in quotationmarks, e.g.
<?php
$st = $db->prepare( '
INSERT INTO fruits( name, colour )
VALUES( :name, ":colour" )
';
$st->execute( array( ':name' => 'Apple', ':colour' => 'red' ) );
?>
results in in a new fruit like
-> Apple, :colour
without the colour beeing replaced by "red". so leave variables WITHOUT the quotation - PDO will do.
[#10] nils andre with my googelian maily accou [2012-04-27 14:25:09]
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.
[#11] T-Rex [2012-04-11 15:44:51]
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
[#12] richard at securebucket dot com [2011-11-25 13:16:41]
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.
[#13] ElTorqiro [2011-04-05 14:51:31]
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) {
$pdo->execute($row);
}
// end transaction
$pdo->commit();
?>
[#14] Robin Millette [2011-01-10 18:30:00]
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:
<?php
class MyPDOStatement extends PDOStatement {
// ...
// don't use this form!
// function execute($input_parameters = array()) {
// use this instead:
function execute($input_parameters = null) {
// ...
return parent::execute($input_parameters);
}
}
?>
As a sidenote, that's why I always set default parameter to NULL and take care of handling the actual correct default parameters in the body of the method or function. Thus, when you have to call the function with all the parameters, you know to always pass NULL for defaults.
[#15] Tony Casparro [2010-07-08 10:09:58]
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.
<?php
public function showQuery($query, $params)
{
$keys = array();
$values = array();
# build a regular expression for each parameter
foreach ($params as $key=>$value)
{
if (is_string($key))
{
$keys[] = '/:'.$key.'/';
}
else
{
$keys[] = '/[?]/';
}
if(is_numeric($value))
{
$values[] = intval($value);
}
else
{
$values[] = '"'.$value .'"';
}
}
$query = preg_replace($keys, $values, $query, 1, $count);
return $query;
}
?>
[#16] gx [2010-04-24 10:05:18]
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 ^^).
[#17] Ant P. [2008-08-08 10:33:46]
As of 5.2.6 you still can't use this function's $input_parameters to pass a boolean to PostgreSQL. To do that, you'll have to call bindParam() with explicit types for each parameter in the query.
[#18] Jean-Lou dot Dupont at jldupont dot com [2008-03-09 10:38:48]
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.
[#19] albright atat anre dotdot net [2008-01-18 20:33:53]
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));
?>
[#20] Daniel [2007-08-30 05:20:05]
You could also use switch the order of t1 and t2 to get user_id from t1 (tested on postgresql):
SELECT
t2.*,
t1.user_id, t1.user_name
FROM table1 t1
LEFT JOIN table2 t2 ON t2.user_id = t1.user_id
WHERE t1.user_id = 2
[#21] simon dot lehmann at gmx dot de [2007-08-08 03:17:05]
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.
[#22] narcis at narcisradu dot com [2007-01-08 01:16:00]
For a query like this:
SELECT
t1.user_id, t1.user_name,
t2.*
FROM table1 t1
LEFT JOIN table2 t2 ON t2.user_id = t1.user_id
WHERE t1.user_id = 2
If I don't have an entry in table2 for user_id=2, the user_id in result will be empty.
SELECT
t1.user_id, t1.user_name,
t2.user_pet, t2.user_color, t2.user_sign
FROM table1 t1
LEFT JOIN table2 t2 ON t2.user_id = t1.user_id
WHERE t1.user_id = 2
This query will return nonempty user_id.
So please be careful with wildcard select.
[#23] VolGas [2006-12-22 22:38:47]
An array of insert values (named parameters) don't need the prefixed colon als key-value to work.
<?php
$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!
[#24] dbrucas [2006-11-21 10:54:04]
If you don't want to turn on exception raising, then try this:
//$dbErr = $dbHandler->errorInfo(); OR
$dbErr = $dbStatement->errorInfo();
if ( $dbErr[0] != '00000' ) {
print_r($dbHandler->errorInfo());
die( "<div class='redbg xlarge'>FAILED: $msg</div><br />".$foot);
// or handle the error your way...
}
echo "SUCCESS: $msg<br />";
... continue if succesful
[#25] russel at sunraystudios dot com [2006-10-15 14:42:35]
I've used it and it returns booleans=>
$passed = $stmt->execute();
if($passed){
echo "passed";
} else {
echo "failed";
}
If the statement failed it would print failed. You would want to use errorInfo() to get more info, but it does seem to work for me.