| Vandegrift, Ken 2006-07-04, 6:04 pm |
| Just wanted to throw this out there to see if anyone may be able to help
with a PDO transaction that does not appear to be working. I want to
update an order record in the database and also insert giftcard numbers
associated with this same order into another table. So I am trying to
do one update in the customer order table and possibly several inserts
into a giftcard record table. I purposely use the same giftcard number
to trigger a primary key violation and attempt to rollback the
transaction if this occurs. I know users of the system will likely
enter the same giftcard numer at some point (although I will have
validation schemes in place to prevent db errors)
Running Windows 2003 Server IIS6 and PHP 5.1.2
Code example:
/**
* Updates order information in the database.
* Called from Order::dbSaveOrder
* @return boolean true on success
* @access protected
*/
protected function dbUpdateOrder() {
// VARIABLES TO TRACK PROPER SQL FORMATTING
$count = count($this->attributes);
$i = 1;
// BUILD ORDER TABLE UPDATE QUERY
$sql = "UPDATE " . ORDER_TABLE . " SET ";
foreach ($this->attributes as $attribute => $value) {
$attribute = strtoupper($attribute);
$sql .= (is_string($value) || is_null($value)) ? "{$attribute}=
'{$value}'" : "{$attribute} = {$value}";
if ($i < $count) {
$i++;
$sql .= ", ";
}
}
$sql .= " WHERE " . ORDER_TABLE_ID . " = :orderid;";
try {
$this->db->beginTransaction();
$stmt = $this->db->prepare($sql);
$stmt->execute(array(':orderid' => $this->attributes['order_id']));
$stmt = null;
// SAVE GIFT CARD NUMBERS RECORDED FOR THIS ORDER
if (!empty($this->gcnums)) {
$sql = null;
foreach ($this->gcnums as $itemid => $gcnumbers) {
foreach ($gcnumbers as $gcnum) {
$sql .= "INSERT INTO GIFTCARD_RECORD " .
"(" .
"ORDER_ID, " .
"ITEM_ID, " .
"GC_NUM " .
") " .
"VALUES" .
"(" .
"{$this->__get('order_id')}, " .
"{$itemid}, " .
"'{$gcnum}'" .
");";
} // End Inner FOREACH
} // End Outer FOREACH
$stmt = $this->db->prepare($sql);
$stmt->execute();
}
$this->db->commit();
return true;
}catch (PDOException $e) {
$this->db->rollBack();
Log::write(LOG_DIR, 'order',
"\r******** Order::dbUpdateOrder ********\r" .
"[UPDATE SQL] => " . $sql . "\r" .
"[DB ERROR] => " . $e->getMessage() . "\r" .
"**************************************"
);
$this->error = 'The system has encountered an error and is unableto
update this order. ' .
'Please contact the system administrator.';
$stmt = null;
return false;
}
}
Ken Vandegrift
|