PDOStatement
在线手册：中文 英文
PHP手册

PDOStatement::execute

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

PDOStatement::execute — Executes a prepared statement

说明

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

Execute the prepared statement. If the prepared statement included parameter markers, you must either:

参数

input_parameters

An array of values with as many elements as there are bound parameters in the SQL statement being executed. All values are treated as PDO::PARAM_STR.

You cannot bind multiple values to a single parameter; for example, you cannot bind two values to a single named parameter in an IN() clause.

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.

返回值

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

更新日志

版本 说明
5.2.0 The keys from input_parameters must match the ones declared in the SQL. Before PHP 5.2.0 this was silently ignored.

范例

Example #1 Execute a prepared statement with bound variables

<?php
/* Execute a prepared statement by binding PHP variables */
$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 Execute a prepared statement with an array of insert values (named parameters)

<?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');
$sth->execute(array(':calories' => $calories, ':colour' => $colour));
?>

Example #3 Execute a prepared statement with an array of insert values (placeholders)

<?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 < ? AND colour = ?');
$sth->execute(array($calories, $colour));
?>

Example #4 Execute a prepared statement with question mark placeholders

<?php
/* Execute a prepared statement by binding PHP variables */
$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();
?>

注释

Note:

Some drivers require to close cursor before executing next statement.

参见

PDOStatement
在线手册：中文 英文
PHP手册
PHP手册 - N: Executes a prepared statement

用户评论:

richard at securebucket dot com (25-Nov-2011 09:16)

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.

ElTorqiro (05-Apr-2011 10:51)

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();

?>

Robin Millette (11-Jan-2011 02:30)

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.

Tony Casparro (08-Jul-2010 06:09)

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;
    }
?>

gx (24-Apr-2010 06:05)

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 ^^).

Ant P. (08-Aug-2008 06:33)

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.

Jean-Lou dot Dupont at jldupont dot com (09-Mar-2008 06:38)

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.

albright atat anre dotdot net (19-Jan-2008 04:33)

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));
?>

Daniel (30-Aug-2007 01:20)

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

simon dot lehmann at gmx dot de (08-Aug-2007 11:17)

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.

narcis at narcisradu dot com (08-Jan-2007 09:16)

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.

VolGas (23-Dec-2006 06:38)

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!

dbrucas (21-Nov-2006 06:54)

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

inghamn at bloomington dot in dot gov (24-Oct-2006 09:53)

Just a note, I'm currently using 5.1.6.  After debugging for a good amount of time, I've realized that the $array cannot be an associative array.

For instance, the following code will fail on the execute.
<?php
    $fields = array();
    $fields['name'] = "Someone";

    $query = $PDO->prepare("insert table set name=?");
    $query->execute($fields);
?>

For my code, I had to convert the $fields array into a non-associative array.  Something like this:
<?php
    $fields = array();
    $fields['name'] = "Someone";

    $values = array();
    foreach($fields as $value) { $values[] = $value; }

    $query = $PDO->prepare("insert table set name=?");
    $query->execute($values);
?>

russel at sunraystudios dot com (15-Oct-2006 10:42)

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.

Nei (07-Feb-2006 07:40)

This does not return TRUE or FALSE. It seems to just not return anything.