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.
PDOStatement::execute
(PHP 5 >= 5.1.0, PHP 7, PHP 8, PECL pdo >= 0.1.0)
PDOStatement::execute — Запускает подготовленный запрос на выполнение
Описание
Запускает подготовленный запрос. Если запрос содержит маркеры параметров (псевдопеременные), вы должны либо:
-
вызвать PDOStatement::bindParam() и/или PDOStatement::bindValue(), чтобы связать эти маркеры, соответственно, с переменными или значениями. Связанные переменные передают свои значения как входные данные и получают выходные значения
-
или передать массив значений только на вход
Список параметров
-
params -
Массив значений, содержащий столько элементов, сколько параметров заявлено в SQL-запросе. Все значения будут приняты, как имеющие тип
PDO::PARAM_STR.Нельзя привязать несколько значений к одному параметру; например, нельзя привязать два значения к именованному параметру в выражении IN().
Нельзя привязать больше значений, чем заявлено в запросе; если в массиве
paramsбольше элементов, чем заявлено в SQL-запросе методом PDO::prepare(), выполнение запроса завершится неудачей и будет вызвана ошибка.
Возвращаемые значения
Возвращает true в случае успешного выполнения или false в случае возникновения ошибки.
Ошибки
Выдаёт ошибку уровня E_WARNING, если атрибуту PDO::ATTR_ERRMODE
установлено значение PDO::ERRMODE_WARNING.
Выбрасывает исключение PDOException, если атрибуту PDO::ATTR_ERRMODE
установлено значение PDO::ERRMODE_EXCEPTION.
Примеры
Пример #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
/* Выполнение подготовленного запроса с привязкой 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 = implode(',', array_fill(0, count($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() - Возвращает данные одного столбца следующей строки результирующего набора
add a note
User Contributed Notes 31 notes
Jean-Lou dot Dupont at jldupont dot com ¶
16 years ago
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 ^^).
VolGas ¶
17 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!
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));
?>
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]
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
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));
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.
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
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.
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));
?>
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"
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.
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.
Robin Millette ¶
13 years ago
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.
Ant P. ¶
16 years ago
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.
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();
Tony Casparro ¶
14 years ago
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;
}
?>
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();
}
?>
davidhcefx ¶
4 years ago
As already pointed out by some folks, DON'T PASS ARRAYS KEYED WITH NAMES TO QUESTION MARK PARAMETERS!
<?php
$sth = $dbh->prepare('INSERT INTO fruit (name, colour, colories) VALUES (?, ?, ?)');
// This is wrong!
// $param = array("name" => "apple", "colour" => "red", "colories" => 150);
// Array must be keyed with integers starting from zero
$param = array("apple", "red", 150);
$sth->execute($param);
mail at horn-online-media dot de ¶
12 years ago
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.
russel at sunraystudios dot com ¶
18 years ago
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.
nils andre with my googelian maily accou ¶
12 years ago
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.
mail at tinodidriksen dot com ¶
8 years ago
The example shows this to generate the needed number of question marks, which is amazingly wasteful:
$place_holders = implode(',', array_fill(0, count($params), '?'));
Instead, just do:
$place_holders = '?'.str_repeat(',?', count($params)-1);
pere dot pasqual at gmail dot com ¶
9 years ago
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);
?>
valterekholm at hotmail.com ¶
4 years ago
I'm using the question-mark way when binding. I tried to use an an associative array when doing
$stmt->execute($values);
having column-names as keys. I thought this would make it easier when rendering the query (as I had variable amounts of parameters).
I found that the execute then could not work with associative array, only with numeric array (with numeric indexes).
takingsides at gmail dot com ¶
10 years ago
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'
)
narcis at narcisradu dot com ¶
17 years ago
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.
Daniel ¶
17 years ago
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
dbrucas ¶
18 years ago
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
dg_waves ¶
7 years ago
Note the return values for this method.
[bool] => true or false
So when i check several queries like INSERT, UPDATE, REPLACE AND DELETE, i got accurate results => bool(true) i.e. The query executed successfully.
$query = "SELECT session_data" FROM sessions WHERE session_id = ?";
$stmt = $pdo->prepare($query);
$stmt->bindValue(1, 'login_user_5121');
if($stmt->execute()) {
print 'Query executed successfully';
}
//Output
Query executed successfully
So, i think u might not have to use { $stmt->rowCount() } method to know whether the query executed successfully.
But on every community i haven't seen anyone point out, so i might be wrong here but u should give it a try with several queries.
