PDO::exec or PDO::execute?

Question

I use PDO to connect to my database and I don't know which method is better than the other one for UPDATE, DELETE and INSERT, PDO::exec or PDO::excute. Which should I use?

They are very different. If you need dynamic parameters like `DELETE FROM tbl WHERE col = :value` you need to use `prepare()/bindParam()/execute()`. The purpose of `exec()` is the simplest of static queries like `DELETE FROM tbl` — Michael Berkowski, Nov 10 '14 at 17:10
Without seeing what kind of queries you're using for `INSERT,UPDATE,DELETE`, my guess would be that you should be using `prepare()/execute()`, as most any modern application must perform those statements based on values input by users. — Michael Berkowski, Nov 10 '14 at 17:12
and whats the diffrence between returned value ? which one is better to know if Update,Insert,Delete has no errors? — Ramin, Nov 10 '14 at 17:18
That's [in the docs](http://php.net/manual/en/pdo.exec.php). If you only need to know how many rows were affected, that's what `exec()` returns. But for _any_ user input, you are advised to `prepare()/execute()` with param placeholders. Then you need to call `$stmt->affectedRows()` on the executed statement. See also [this question](http://stackoverflow.com/questions/4978481/pdoexec-or-pdoquery) addressing the difference between `exec()` and `query()`.. — Michael Berkowski, Nov 10 '14 at 17:28
I'm voting to close this question as off-topic because it is asking which is better a car or a cow. — PeeHaa, Jan 26 '15 at 19:23

score 5 · Accepted Answer · answered Jan 26 '15 at 19:15

Although both methods have a similar naming (exec,execute) they're meant to be used in different scenarios:

exec is used to get the number of affected rows.

/**
 * (PHP 5 &gt;= 5.1.0, PECL pdo &gt;= 0.1.0)<br/>
 * Execute an SQL statement and return the number of affected rows
 * @link http://php.net/manual/en/pdo.exec.php
 * @param string $statement <p>
 * The SQL statement to prepare and execute.
 * </p>
 * <p>
 * Data inside the query should be properly escaped.
 * </p>
 * @return int <b>PDO::exec</b> returns the number of rows that were modified
 * or deleted by the SQL statement you issued. If no rows were affected,
 * <b>PDO::exec</b> returns 0.
 * </p>
 * This function may
 * return Boolean <b>FALSE</b>, but may also return a non-Boolean value which
 * evaluates to <b>FALSE</b>. Please read the section on Booleans for more
 * information. Use the ===
 * operator for testing the return value of this
 * function.
 * <p>
 * The following example incorrectly relies on the return value of
 * <b>PDO::exec</b>, wherein a statement that affected 0 rows
 * results in a call to <b>die</b>:
 * <code>
 * $db->exec() or die(print_r($db->errorInfo(), true));
 * </code>
 */
public function exec ($statement) {}

Example:

$myQuery = "UPDATE users SET email = 'testing'";
$affectedRows = $db->exec($myQuery);

execute is used when you want to pass an array of parameters to be bind in the query.

/**
 * (PHP 5 &gt;= 5.1.0, PECL pdo &gt;= 0.1.0)<br/>
 * Executes a prepared statement
 * @link http://php.net/manual/en/pdostatement.execute.php
 * @param array $input_parameters [optional] <p>
 * An array of values with as many elements as there are bound
 * parameters in the SQL statement being executed.
 * All values are treated as <b>PDO::PARAM_STR</b>.
 * </p>
 * <p>
 * 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.
 * </p>
 * <p>
 * You cannot bind more values than specified; if more keys exist in
 * <i>input_parameters</i> than in the SQL specified
 * in the <b>PDO::prepare</b>, then the statement will
 * fail and an error is emitted.
 * </p>
 * @return bool <b>TRUE</b> on success or <b>FALSE</b> on failure.
 */
public function execute (array $input_parameters = null) {}

Example (of course this might be an UPDATE or DELETE query as well):

$myQuery = 'SELECT * FROM users WHERE username = :username';
$params = array(':username' => 'admin');
$db->query($myQuery)->execute($params);

query returns a PDOStatement object

/**
 * (PHP 5 &gt;= 5.1.0, PECL pdo &gt;= 0.2.0)<br/>
 * Executes an SQL statement, returning a result set as a PDOStatement object
 * @link http://php.net/manual/en/pdo.query.php
 * @param string $statement <p>
 * The SQL statement to prepare and execute.
 * </p>
 * <p>
 * Data inside the query should be properly escaped.
 * </p>
 * @return PDOStatement <b>PDO::query</b> returns a PDOStatement object, or <b>FALSE</b>
 * on failure.
 */
public function query ($statement) {}

For more information you can visit the PHP Docs or in case you're using PHPStorm you can go though the source code of the PDO.php class.

PDO::exec or PDO::execute?

1 Answers1

Linked