[eluser]Chillahan[/eluser]
I have a few related questions. First let me pose my question, then let me show some abbreviated code that I am using which I believe achieves the goal stated.
Question:
1. Is testing result of $this->db->insert() or update() reliable for making decisions on whether operation failed? Chiefly, as to whether insert() failed due to constraint of unique key on a certain field? Also, if update() failed because a where() parameter was not matched (i.e., record not found to be updated)? My point is, I am trying to ensure that each user who performs an order gets a unique code, and that someone else doesn't get the same code while also using the site (and happens to update the record to point to their purchase instead of the original user). I looked into using start_transaction but I don't think that is really meant to "lock" a record to a certain update request, more to ensure multiple updates all occur (or all get rolled back). Furthermore I wanted to avoid using a table lock in case of unwanted side effects.
Problem and my Solution:
Here is more info. Basically, I have a table of codes already defined. In the event there are no more codes for the product the user is ordering, I need to generate one on the fly. Also, when I take an unused one from the table, I sort it alphabetically, so if two people order at the same time, it IS possible they both pull the same code, so when they go to update it with their id, I have to ensure it is still assigned at time of update. Likewise, if I generate a new code on the fly, I want to ensure someone else didn't just generate the same one (unlikely, as there are 30 million combinations, but I might as well address the chance of this happening).
Here's my model code for doing this logic:
Code:
// add new order
$this->db->insert('orders', $newOrderData);
$newOrderId = $this->db->insert_id();
$codeAssigned = FALSE;
// idea here is to loop until we know we've found or generated
// a new code and successfully assigned it to this user
while ($codeAssigned == FALSE)
{
$this->db->select('*');
$this->db->where('product_id', $newOrderData['product_id']);
$this->db->where('status', 'new');
$this->db->order_by('code', 'asc');
$query = $this->db->get('codes', 1);
if ($query->num_rows() > 0)
{
// use the returned code - but make sure another session
// didn't take it first by testing result of update
$row = $query->row();
$code = $row->code;
$this->db->where('product_id', $newOrderData['product_id']);
// this below is important because it ensure someone else didn't already
// assign this same code for themselves!
$this->db->where('status', 'new');
$this->db->where('code', $code);
$this->db->set('order_id', $newOrderId);
$this->db->set('status', 'purchased');
$result = $this->db->update('codes');
if ($result == TRUE)
{
$codeAssigned = TRUE;
// this means we got an unused code and were able to assign it
}
}
else
{
// generate a new code on the fly
// (THE REASON I use a separate while loop is because we already KNOW
// there are no codes in the table of existing codes - so rather than
// running that query over and over (the main one above), we can just loop herein)
$uniqueCodeFound = FALSE;
while ($uniqueCodeFound == FALSE)
{
$code = genRandomCode();
// do a select to see if code is used - if the insert below
// would fail properly if unique constraint not met, I could
// rely on that, but I decided to do my own check here, even
// though it's still possible someone else could happen to
// generate same random code before I insert mine
$this->db->select('*');
$this->db->where('code', $code);
$query = $this->db->get('codes', 1);
if ($query->num_rows() == 0)
{
// insert new code
$this->db->set('product_id', $newOrderData['product_id']);
$this->db->set('order_id', $newOrderId);
$this->db->set('code', $code);
$this->db->set('status', 'purchased');
$result = $this->db->insert('codes');
if ($result == TRUE)
{
$uniqueCodeFound = TRUE;
$codeAssigned = TRUE;
}
}
}
}
}
// add the code to the orders record
$this->db->where('id', $newOrderId);
$this->db->set('code', $code);
$this->db->update('orders');
return $newOrderId;
So BASICALLY I am wondering if the two instances I test $result == TRUE are going to catch the times someone else does update before the current session does. It's hard to test since I'd have to update the record while the query runs. I guess I could use a delay or something then go and update database manually to see if it recovers, but the point is, I think it works, because code works now, and that means it is returning TRUE. Would be odd if insert/update were returning TRUE even if they failed, but one never knows.
