PDO::exec
PDO::exec() executes an SQL statement in a single function call, returning the number of rows affected by the statement.
PDO::exec() does not return results from a SELECT statement. For a SELECT statement that you only need to issue once during your program, consider issuing PDO::query() . For a statement that you need to issue multiple times, prepare a PDOStatement object with PDO::prepare() and issue the statement with PDOStatement::execute() .
Parameters
The SQL statement to prepare and execute.
Data inside the query should be properly escaped.
Return Values
PDO::exec() returns the number of rows that were modified or deleted by the SQL statement you issued. If no rows were affected, PDO::exec() returns 0 .
This function may return Boolean false , but may also return a non-Boolean value which evaluates to false . Please read the section on Booleans for more information. Use the === operator for testing the return value of this function.
The following example incorrectly relies on the return value of PDO::exec() , wherein a statement that affected 0 rows results in a call to die() :
Errors/Exceptions
Emits an error with level E_WARNING if the attribute PDO::ATTR_ERRMODE is set to PDO::ERRMODE_WARNING .
Throws a PDOException if the attribute PDO::ATTR_ERRMODE is set to PDO::ERRMODE_EXCEPTION .
Examples
Example #1 Issuing a DELETE statement
Count the number of rows deleted by a DELETE statement with no WHERE clause.
$dbh = new PDO ( ‘odbc:sample’ , ‘db2inst1’ , ‘ibmdb2’ );
/* Delete all rows from the FRUIT table */
$count = $dbh -> exec ( «DELETE FROM fruit» );
/* Return number of rows that were deleted */
print( «Deleted $count rows.\n» );
?>
The above example will output:
See Also
- PDO::prepare() — Prepares a statement for execution and returns a statement object
- PDO::query() — Prepares and executes an SQL statement without placeholders
- PDOStatement::execute() — Executes a prepared statement
User Contributed Notes 7 notes
This function cannot be used with any queries that return results. This includes SELECT, OPTIMIZE TABLE, etc.
It’s worth noting here, that — in addition to the hints given in docs up there — using prepare, bind and execute provides more benefits than multiply querying a statement: performance and security!
If you insert some binary data (e.g. image file) into database using INSERT INTO . then it may boost performance of parsing your statement since it is kept small (a few bytes, only, while the image may be several MiBytes) and there is no need to escape/quote the file’s binary data to become a proper string value.
And, finally and for example, if you want to get a more secure PHP application which isn’t affectable by SQL injection attacks you _have to_ consider using prepare/execute on every statement containing data (like INSERTs or SELECTs with WHERE-clauses). Separating the statement code from related data using prepare, bind and execute is best method — fast and secure! You don’t even need to escape/quote/format-check any data.
PDO::eval() might return `false` for some statements (e.g. CREATE TABLE) even if the operation completed successfully, when using PDO_DBLIB and FreeTDS. So it is not a reliable way of testing the op status.
PDO::errorInfo() can be used to test the SQLSTATE error code for ‘00000’ (success) and ‘01000’ (success with warning).
this function don’t execute multi_query
to get it see SQLITE_EXEC comments there is an pereg function that get all queries and execute all then an return the last one
Note that with MySQL you can detect a DUPLICATE KEY with INSERT (1 = INSERT, 2 = UPDATE) :
// MySQL specific INSERT UPDATE-like syntax
$sql = INSERT INTO customers
SET
> < $pdo ->quote ( $id )> ,
name = < $pdo ->quote ( $name )> ,
address = < $pdo ->quote ( $address )>
AS new
ON DUPLICATE KEY UPDATE
name = new.name,
address = new.address
SQL;
if ( $result === 1 ) // An INSERT of a new row has be done
> elseif ( $result === 2 ) // An UPDATE of an existing row has be done
>
For those that want an exec that handles params like prepare/execute does. You can simulate this with another function
class Real_PDO extends PDO <
public function execParams ( $sql , $params ) <
$stm = $this -> prepare ( $sql );
$result = false ;
if( $stm && $stm -> execute ( $params ) ) <
$result = $stm -> rowCount ();
while( $stm -> fetch ( PDO :: FETCH_ASSOC ) ) <
>
>
return $result ;
>
>
?>
Remember though, if you are doing a lot of inserts, you’ll want to do it the manual way, as the prepare statement will speed up when doing multiple executes(inserts). I use this so I can place all my SQL statements in one place, and have auto safe quoting against sql-injections.
If you are wondering about the fetch after, remember some databases can return data SELECT-like data from REMOVE/INSERTS. In the case of PostgreSQL, you can have it return you all records that were actually removed, or have the insert return the records after the insert/post field functions, and io trigger fire, to give you normalized data.
define ( «BLAH_INSERT» , «INSERT INTO blah (id,data) VALUES(. )» );
$pdo = new Real_PDO ( «connect string» );
$data = array( «1» , «2» );
$pdo -> execParams ( BLAH_INSERT , $data );
?>
You can’t use it not only with SELECT statement, but any statement that might return rows. «OPTIMIZE table» is such example (returns some rows with optimization status).
If you do, PDO will lock-up with the «Cannot execute queries while other unbuffered queries are active.» nonsense.
The PDOException class
Represents an error raised by PDO. You should not throw a PDOException from your own code. See Exceptions for more information about Exceptions in PHP.
Class synopsis
public Exception::__construct ( string $message = «» , int $code = 0 , ? Throwable $previous = null )
Properties
SQLSTATE error code. Use Exception::getCode() to access it.
User Contributed Notes 5 notes
Here is something interesting regarding a PDOException and it involves some of the annoyances that can be associated with PHP’s dynamic nature.
PDOException extends from RuntimeException, which in return extends from Exception. As such, it has access to the $code Protected Class Variable, which represents the Exception’s code as an Integer (duh!) and can be accessed externally using the Exception::getCode Method.
Here is the interesting part. PDOException actually redefines $code as a String and not an Integer because for its case, $code actually contains the Exception’s SQL State, which is composed of characters and numbers.
It is actually documented in the manual that $code is a String and not an Integer but it might not be immedietley clear because it is hidden by the fact that PDOException::getCode is documented to return an Integer and not a String!
Some developers like to catch a PDOException and rethrow it as a different Exception if they wrap their database calls in an external library. For example, consider the following code:
try $PDO = new PDO ( ‘. ‘ ); // PDO Driver DSN. Throws A PDOException.
>
catch( PDOException $Exception ) // PHP Fatal Error. Second Argument Has To Be An Integer, But PDOException::getCode Returns A
// String.
throw new MyDatabaseException ( $Exception -> getMessage ( ) , $Exception -> getCode ( ) );
>
?>
Be careful in that you have to typecast the value returned by PDOException::getCode to an Integer BEFORE you pass it as an Argument to your Exception’s Constructor. The following will work:
try $PDO = new PDO ( ‘. ‘ ); // PDO Driver DSN. Throws A PDOException.
>
catch( PDOException $Exception ) // Note The Typecast To An Integer!
throw new MyDatabaseException ( $Exception -> getMessage ( ) , (int) $Exception -> getCode ( ) );
>
?>
Hope this will save some developers some frustrating hours from an otherwise enjoyable job 🙂
Since PDOException returns the error code as a string, you need a constructor like the one below if you wish to rethrow the PDOException as a custom exception.
This constructor does not call the parent::__construct which will enforce the int type on the error code, but set the message and code as properties directly on the custom exception object.
class CustomException extends PDOException
/**
* Override constructor and set message and code properties.
* Workaround PHP BUGS #51742, #39615
*/
public function __construct ( $message = null , $code = null ) $this -> message = $message ;
$this -> code = $code ;
>
PDOException has two methods for retrieving information about an error. When interpreting the PDOException I run into a problem, the error code that is provided by getCode() is meaningless. I have come up with a method to make both the error code and message more usable.
A bad username or password would normally provide the following:
CODE : 0
Message : «SQLSTATE[28000] [1045] Access denied for user ‘user’@’example.com’ (using password: YES)»
Using my extended exception class provides:
CODE: «28000»
Message: «Access denied for user ‘user’@’example.com’ (using password: YES)»
class pdoDbException extends PDOException
public function __construct ( PDOException $e ) <
if( strstr ( $e -> getMessage (), ‘SQLSTATE[‘ )) <
preg_match ( ‘/SQLSTATE\[(\w+)\] \[(\w+)\] (.*)/’ , $e -> getMessage (), $matches );
$this -> code = ( $matches [ 1 ] == ‘HT000’ ? $matches [ 2 ] : $matches [ 1 ]);
$this -> message = $matches [ 3 ];
>
>
>
?>
To walk threw the method; first the beginning of the message is checked for the SQLSTATE text. If the text is present, message is then parsed to pull the ANSI code, the SQL specific code, and the message. The parsed values are stored in there respective variables. The error code variable stores the ANSI code, unless ANSI is ‘HT000’ (unmapped error code) then SQL specific code is used.
Using this class is easy; when interacting with PDO use a try catch set of blocks, as follows:
try <
$pdo = new PDO ( $dns , $username , $password , $options );
> catch ( PDOException $e ) <
throw new pdoDbException ( $e );
>
?>
Now you can use the normal error methods to retrieve the real error code and message.
echo $err -> getCode (); // Outputs: «28000»
echo $err -> getMessage (); // Outputs: «Access denied for user ‘user’@’example.com’ (using password: YES)»
?>
If you decide to use this code, be aware that the error code is a string (as apposed to PHP standard errors which are integers) as some error codes are alphanumeric.
In response to the 15-May-2010 07:45 note from: samuelelliot+php dot net at gmail dot com
Since the most base class Exception takes three arguments, and the previous exception is not the first, .
Since inherited classes to not implicitly call the parent constructor, .
I recommend changing his constructor to this:
public function __construct(string $message = «», int $code = 0, PDOException $e = null)
// in case they call: new MyException($somePDOException);
// instead of following the interface.
//
if (is_subclass_of($message, PDOException))
$e = $message;
$code = $e->getCode();
$message = $e->getMessage();
>
// Let PDOException do its normal thing
//
parent::__construct($message, $code, $e);
// Now to correct the code number.
//
$state = $this->getMessage();
if(!strstr($state, ‘SQLSTATE[‘))
$state = $this->getCode();
if(strstr($state, ‘SQLSTATE[‘))
preg_match(‘/SQLSTATE\[(\w+)\] \[(\w+)\] (.*)/’, $state, $matches);
$this->code = ($matches[1] == ‘HT000’ ? $matches[2] : $matches[1]);
$this->message = $matches[3];
>
>
the sqlstate is an hex integer (as string)
if you want to wrap pdoexception, use
throw new MyDBException($pdoe->getMessage(), hexdec($pdoe->getCode()), $pdoe);
- PDO
- Introduction
- Installing/Configuring
- Predefined Constants
- Connections and Connection management
- Transactions and auto-commit
- Prepared statements and stored procedures
- Errors and error handling
- Large Objects (LOBs)
- PDO
- PDOStatement
- PDOException
- PDO Drivers