Setup Error Handling
After establishing your PDO connection, set the following attribute:
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
this will throw an exception on PDO errors. now, you can wrap everything in a nice try/catch block
try{
}catch(PDOException $e){
echo $e->message();
}
Do not use rowCount on select statements
from php.net manual page at http://ca3.php.net/manual/en/pdostatement.rowcount.php,
int PDOStatement::rowCount ( void )
PDOStatement::rowCount() returns the number of rows affected by the last DELETE, INSERT,
or UPDATE statement executed by the corresponding PDOStatement object.
If the last SQL statement executed by the associated PDOStatement was a SELECT statement,
some databases may return the number of rows returned by that statement. However, this
behaviour is not guaranteed for all databases and should not be relied on for portable
applications.
This really does result in unexpected results when used on a select query, often returning one when the actual number of returned rows is zero.
The solution is to call fetchColumn on a SELECT COUNT(*) FROM... statement instead.
In summary, do NOT:
//let $dbh be the database handle
$sql = "SELECT * FROM dog WHERE weight > 50";
if ($stmt = $dbh->query($sql)) {
/* Check the number of rows that match the SELECT statement */
if ($stmt->rowCount()) {
But do:
/let $dbh be the database handle
$sql = "SELECT COUNT(*) FROM dog WHERE weight > 50";
if ($stmt = $dbh->query($sql)) {
/* Check the number of rows that match the SELECT statement */
if ($stmt->fetchColumn()) {
PDO Transactions
To do DB transactions in PDO, first make sure your DB engine supports transactions. If you are using MySQL, then you need to alter your tables from the default engine to something like InnoDB.
ALTER TABLE <em>table_name</em> ENGINE = innodb;
Let $db be your database handle with the PEAR API, you would of done something like:
try{
$db->autoCommit(false);
...statements....
$db->commit();
}catch(Exception $e){
$db->rollback();
}
$db-><strong>autoCommit(true);</strong>
With PDO, it is just as easy:
try{
$db->beginTransaction();
...statements...
$db->commit();
}catch(PDOException $e){
$db->rollback();
}
Ensure that you pass the same $db handle to all statements within a PDO transaction using a singleton instance.
..
SampleTable::updateRecord($dbh);
SampleLogTable::insertRecord($dbh);
..
All Articles
Add new comment