5.5.15

PDOStatement::execute

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

PDOStatement::execute Exécute une requête préparée

Description

public bool PDOStatement::execute ([ array $input_parameters ] )

Exécute une requête préparée. Si la requête préparée inclut des marqueurs de positionnement, vous pouvez :

  • appeler la fonction PDOStatement::bindParam() pour lier les variables PHP aux marqueurs de positionnement : les variables liées passent leurs valeurs en entrée et reçoivent les valeurs de sortie, s'il y en a, de leurs marqueurs de positionnement respectifs

  • ou passer un tableau de valeurs de paramètres, uniquement en entrée

Liste de paramètres

input_parameters

Un tableau de valeurs avec autant d'éléments qu'il y a de paramètres à associer dans la requête SQL qui sera exécutée. Toutes les valeurs sont traitées comme des constantes PDO::PARAM_STR.

Vous ne pouvez associer plusieurs valeurs à un seul paramètre; par exemple, vous ne pouvez associer deux valeurs à un paramètre de nom dans une clause IN().

Vous ne pouvez associer plus de valeurs que spécifié ; s'il y a plus de clés dans input_parameters que dans le code SQL utilisé pour PDO::prepare(), alors la requête préparée échouera et une erreur sera levée.

Valeurs de retour

Cette fonction retourne TRUE en cas de succès ou FALSE si une erreur survient.

Historique

Version Description
5.2.0 Les clés déclarées dans input_parameters doivent correspondre à celles déclarées dans le SQL. Avant PHP 5.2.0, ceci était ignoré sans erreur.

Exemples

Exemple #1 Exécute une requête préparée avec des variables liées

<?php
/* Exécute une requête préparée en liant des variables PHP */
$calories 150;
$couleur 'rouge';
$sth $dbh->prepare('SELECT nom, couleur, calories
    FROM fruit
    WHERE calories < :calories AND couleur = :couleur'
);
$sth->bindParam(':calories'$caloriesPDO::PARAM_INT);
$sth->bindParam(':couleur'$couleurPDO::PARAM_STR12);
$sth->execute();
?>

Exemple #2 Exécute une requête préparée avec un tableau de valeurs (paramètres de nom)

<?php
/* Exécute une requête préparée en passant un tableau de valeurs */
$calories 150;
$couleur 'rouge';
$sth $dbh->prepare('SELECT nom, couleur, calories
    FROM fruit
    WHERE calories < :calories AND couleur = :couleur'
);
$sth->execute(array(':calories' => $calories':couleur' => $couleur));
?>

Exemple #3 Exécute une requête préparée avec un tableau de valeurs (marqueurs)

<?php
/* Exécute une requête préparée en passant un tableau de valeurs */
$calories 150;
$colour 'rouge';
$sth $dbh->prepare('SELECT nom, couleur, calories
    FROM fruit
    WHERE calories < ? AND couleur = ?'
);
$sth->execute(array($calories$couleur));
?>

Exemple #4 Exécute une requête préparée avec un marqueur de positionnement

<?php
/* Exécute une requête préparée en liant des variables PHP */
$calories 150;
$couleur 'rouge';
$sth $dbh->prepare('SELECT nom, couleur, calories
    FROM fruit
    WHERE calories < ? AND couleur = ?'
);
$sth->bindParam(1$caloriesPDO::PARAM_INT);
$sth->bindParam(2$couleurPDO::PARAM_STR12);
$sth->execute();
?>

Exemple #5 Exécute une requête préparée en utilisant un tableau pour les clauses IN

<?php
/* Exécute une commande préparée en utilisant un tableau de valeurs pour les clauses IN */
$params = array(12163171);
/* Crée une chaîne pour les marqueurs */
$place_holders implode(','array_fill(0count($params), '?'));

/*
    Ce morceau de code va préparer la requête avec assez de marqueurs pour chaque valeur
    du tableau $params. Les valeurs du tableau $params sont ensuite liées aux marqueurs
    de la requête préparée lorsque la requête est exécutée. Ce n'est pas la même chose
    que d'utiliser la méthode PDOStatement::bindParam() sachant qu'elle impose une
    référence vers les valeurs. La méthode PDOStatement::execute() ne fait que lier
    par la valeur.
*/
$sth $dbh->prepare("SELECT id, name FROM contacts WHERE id IN ($place_holders)");
$sth->execute($params);
?>

Notes

Note:

Quelques drivers nécessitent de fermer le curseur avant d'exécuter la requête suivante.

Voir aussi

add a note add a note

User Contributed Notes 20 notes

up
4
Rami jamleh
1 year 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));
up
7
VolGas
7 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!
up
6
gx
4 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 ^^).
up
5
Jean-Lou dot Dupont at jldupont dot com
6 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.
up
3
Robin Millette
3 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.
up
3
Tony Casparro
4 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;
    }
?>
up
4
mail at horn-online-media dot de
2 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.
up
4
albright atat anre dotdot net
6 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));
?>
up
2
richard at securebucket dot com
2 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.
up
2
dbrucas
7 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
up
3
ElTorqiro
3 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) {
 
$pdo->execute($row);
}

// end transaction
$pdo->commit();

?>
up
1
Ant P.
5 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.
up
2
russel at sunraystudios dot com
7 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.
up
1
anon at anon dot com
2 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.
up
1
T-Rex
2 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
up
1
simon dot lehmann at gmx dot de
6 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.
up
0
Whitebeard
3 months 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.
up
0
nils andre with my googelian maily accou
2 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.
up
-2
Daniel
6 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
up
-4
narcis at narcisradu dot com
7 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.
To Top