UPDATE when exists otherwise INSERT (with active Record) Best practice?

[eluser]uprocka[/eluser]
Hello Coders,

I try to figure out how to do the following the best way:

lets say I have a DB-Table named products (sku, label, price)
and an Importscript that imports productdata.

the SKU is unique and identifies the product.

now I want to import new products (sku does not exist in table products)
or update old products (sku already exists)

I once read that it is the fastest way to first select the row:

Code:

SELECT * FROM products WHERE sku = "1000"

than count the returned rows:

Code:

if it is 0 --> INSERT product with SKU "1000"
if it is 1 --> UPDATE product with SKU "1000"

Do you agree or is there a better way

I could find a method in the ACTIVE DB CLASS that fullfills this need.


Thanks for any hints.

greetz uprocka

[eluser]Michael Wales[/eluser]
I usually write a function in my model with something like this:

Code:

function save($sku = NULL, $label = NULL, $price = NULL) {
  // The SKU is our unique ID, if it doesn't exist - we're screwed
  if ($sku !== NULL) {
    // In this example, label and price are optional and can be inserted as NULL
    $record = array('sku'=>$sku, 'label'=>$label, 'price'=>$price);
    // Check if a record exists for this SKU
    $query = $this->db->get_where('products', array('sku'=>$sku), 1, 0);
    if ($query->num_rows() == 0) {
      // A record does not exist, insert one.
      $query = $this->db->insert('products', $record);
    } else {
      // A record does exist, update it.
      $query = $this->db->update('products', $record, array('sku'=>$sku));
    }
    // Check to see if the query actually performed correctly
    if ($this->db->affected_rows() > 0) {
      return TRUE;
    }
    return FALSE;
  }
  return FALSE;
}

I've even made this a bit lower-level in the past, bringing it up as an extension of the model. That extension would retrieve all of the columns of a table, flag the primary key as the required item to check against, and then required fields were identified within the constructor of the individual models (with all others assumed as optional).

At the time, that code was a horrid mess - I should rewrite it one of these days and share. For the time being, I find a model specific save() function is much easier to write and allows you to add in all sorts of goodies (hashing, salting, date/time translation, etc).

[eluser]uprocka[/eluser]
Hi Michael,
thank you for your fast and usefull answer!!

I totally had something like this in mind. thank you for your support.

[eluser]James V[/eluser]
Here's another possibility using mysql 4.1 and greater. There must be a primary key or unique index for this to work. In the below, 'id' is the primary key. See the mysql reference for more info.

Code:

$sql = 'INSERT INTO menu_sub (id, name, desc, misc)
        VALUES (?, ?, ?, ?)
        ON DUPLICATE KEY UPDATE
            name=VALUES(name),
            desc=VALUES(desc),
            misc=VALUES(misc)';

$query = $this->db->query($sql, array( $id,
                                       $this->validation->name,
                                       $this->validation->desc,
                                       $this->validation->misc
                                      ));

[eluser]xwero[/eluser]
That would work but it goes against the idea of the AR class to have cross database queries.

an alternative of Michaels code uses the sql count function result

Code:

function save($sku = NULL, $label = NULL, $price = NULL) {
  // The SKU is our unique ID, if it doesn't exist - we're screwed
  if ($sku !== NULL) {
    // In this example, label and price are optional and can be inserted as NULL
    $record = array('sku'=>$sku, 'label'=>$label, 'price'=>$price);
    // Check if a record exists for this SKU
    $this->db->where('sku',$sku);
    if ($this->db->count_all_results() == 0) {
      // the check can be chained for less typing
      // $this->db->where('sku',$sku)->count_all_results()
      // A record does not exist, insert one.
      $query = $this->db->insert('products', $record);
    } else {
      // A record does exist, update it.
      $query = $this->db->update('products', $record, array('sku'=>$sku));
    }
    // Check to see if the query actually performed correctly
    if ($this->db->affected_rows() > 0) {
      return TRUE;
    }
    return FALSE;
  }
  return FALSE;
}

[eluser]Michael Wales[/eluser]
Great modification xwero - I really need to look into all of the recent changes leading to, and including, 1.6. I've been so busy working on other projects I've yet had the chance to really dive into the upgrade.

[eluser]uprocka[/eluser]
yes thats true.

ON DUPLICATE KEY UPDATE does exactly what I want, but don't I commit my software to mysql then?

On the other hand it should be faster.

might be also nice to have a method within the active database class (for lazy coders like me)

[eluser]webthink[/eluser]
So there you have it. I suppose the question you have to ask yourself is how big will the table get and how crucial is performance. INSERT ON DUPLICATE... is going to be miles faster and far more efficient and will have the added bonus of reducing your code to something far cleaner than any of the examples above. If however DB portability is a concern to you stick with the read then write pattern.
Somehow I doubt that CI will provide an AR method for this because it isn't supported by every db. What you could do if you wanted was write one that uses the INSERT ON DUPLICATE construct in the mysql driver but the read+write pattern in other DB's.

[eluser]Yellow Duck[/eluser]
If you are only targetting MySQL and SQLite, you can use the REPLACE INTO syntax which works for both.

You can find more info on:
http://dev.mysql.com/doc/refman/5.1/en/replace.html
http://www.sqlite.org/lang_replace.html

pieter

[eluser]steelaz[/eluser]
Is it possible to use REPLACE INTO with AR?