+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forumdisplay.php?fid=5)
+--- Forum: Best Practices (https://forum.codeigniter.com/forumdisplay.php?fid=12)
+--- Thread: Insert-or-update function (/showthread.php?tid=62924)
Insert-or-update function - Rashid - 09-08-2015
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 it, otherwise 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?
RE: Insert-or-update function - RobertSF - 09-08-2015
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.
RE: Insert-or-update function - mwhitney - 09-08-2015
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).