Welcome Guest, Not a member yet? Register   Sign In
question on testing result of update(), insert(), and whether my approach works in ensuring unique data
#1

[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. Smile
#2

[eluser]Cristian Gilè[/eluser]
Hi Chillahan,

there is a query helper function in the database class that displays the number of affected rows, when doing "write" type queries such as insert and update:

Code:
$this->db->affected_rows()

If the above function return 0 something went wrong!

The quick solution to your problem is to generate unique code on the fly without check the database for uniqueness. You can achieve this using a string like this:

Code:
CODE = INCREMENTAL_NUMBER + RANDOM_STRING

INCREMENTAL_NUMBER is a progressive number stored in DB in a separate table or if you want could be the primary key ID of an existing table. You can encode the number with base64.

RANDOM_STRING is a generated random string.

The resulting CODE is unique.


Cristian Gilè
#3

[eluser]Chillahan[/eluser]
Ah I see - I thougth affected_rows() returned an array of the rows or something! Was not apparent in docs that it was a simple counter return. I can see that I could have used that instead.

But do you know if just testing the update() and insert() methods for TRUE is also correct?

The incremental_number is a good idea. The only thing is, the codes have to be set beforehand, because I send the lists out before the product goes for sale (in advance). So unfortunately any real-time manipulation would only work for the on-the-fly generation, not the use of the pre-generated codes.
#4

[eluser]Cristian Gilè[/eluser]
Yes, update and insert methods return TRUE is the query has been executed or an error if something has gone wrong.

You need affected_rows() to know if the update has been performed. Could happening that an update is executed without errors but nothing is changed in the db due to the fact that a where clause doesn't satisfy the condition.

Cristian Gilè
#5

[eluser]Chillahan[/eluser]
Ah, you are right! That is what I was afraid of but wasn't thinking of. Update can be 100% "successful" just like selecting nothing can be.

But for insert() it wouldn't return true if it could not insert due to unique key constraint being violated, I assume - but, I guess it doesn't hurt to add a check for affected_rows() to that part of the code anyway. So just testing affected_rows() > 0 should be okay? I.e., if there's an error, affected_rows will still be available to be called, right (and won't throw a PHP error that breaks the flow).

Thanks a lot for the help too btw. I just wouldn't have thought to look more into affected_rows() otherwise!
#6

[eluser]Cristian Gilè[/eluser]
Like for update, you need affected_rows() to know if the insert has been performed. Any error stop query to be performed. In a production environment in your db configuration db_debug is set to FALSE so you can handle errors with a try...catch statement.

in your model:
Code:
function db_insert()
{
      $data = array(
               'field_name' => 'value'
            );

      if( $this->db->insert('tabel_name', $data) != TRUE)
      {
        throw new Exception('i can not insert');
      }
      else
      {
        return $this->db->affected_rows();
      }
}

in your controller:
Code:
try
{
      $this->your_model_name->db_insert();
      } catch (Exception $e) {
        echo 'Caught exception: ',  $e->getMessage(), "\n";
}
#7

[eluser]Chillahan[/eluser]
So basically you're saying I cannot just keep looping until affected_rows > 0, because maybe there's a real problem and the reason affected_rows() is not > 0 is due to that, and not because someone else snagged the unique code value we were trying to update/insert? I was about to just change it to look at affected rows only, like this:

Code:
$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 ($this->db->affected_rows() > 0)
                {
                    $uniqueCodeFound = TRUE;
                    $codeAssigned = TRUE;
                }
            }
        }

UPDATE: I decided to take the easier way out for now, and I just added a counter to my while statement so it won't try too many times (10 max. Then for the two places I test affected_rows(), I added an else statment to increment the counter:

Code:
if ($this->db->affected_rows() > 0)
                {
                    $uniqueCodeFound = TRUE;
                    $codeAssigned = TRUE;
                }
                else
                {
                    $attempts++;
                }

So if it doesn't get assigned after ten times, the app will just fail for whatever reason it was going to fail anyway (although if it got this far, somehow the user was able to interact with site to the point of being able to pay, so nothing lost).




Theme © iAndrew 2016 - Forum software by © MyBB