CodeIgniter Forums

Full Version: Insert-or-update function
You're currently viewing a stripped down version of our content. View the full version with proper formatting.
Hello, i'm interested in a combined "insert-if-not-exists-else-update" model's function. How to make it effectively using CodeIgniter?

PHP Code:
public function set_settings($id$data)
{
 
  ... if specified $id row exists update itotherwise insert a new one ...


piece of code, i came up with:

PHP Code:
public function set_settings($id$data)
{
 
   $query $this->db->get_where('mytable', array('id' => $id));
 
   if ($query->row() != null)
 
   {
 
       $this->db->update('mytable'$data, array('id' => $id));
 
   }
 
   else
    
{
 
       $this->db->insert('mytable'$data);
 
   }


possibly make it more lean/good?  Huh
It's possible to make the code more compact, but at the expense of readability, and it would be the same algorithm anyway. However, you can push the decision to insert or update down to the SQL query itself. MySQL and similar have IF statements as well as WHERE NOT EXISTS. You generally get higher performance the more you shift data manipulation to the database engine, but the benefit is not obvious in most applications, so what you have should be good.
Additionally, if you do push it through to the database, you need to ensure that whatever method you use will be available wherever your code will run, and consider maintenance if your application will be around for a while.

Something else worth considering might be to make the $id the second parameter in your set_settings() method and make it optional. Then, if you don't have an $id you can skip the query and go straight to insert (assuming you have no other data which could retrieve the existing row for you).