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:

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.

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.

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

"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 = ?";

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.

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.

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.

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" errorand a42000 ("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).

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

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

//OutputQuery 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.