if query is not update

Question

I would like to know best way to make my query's into a if statement. because I would like to use one function for my codeigniter edit main function,

So it combines both query's and makes it if query is not a update then is a insert way is best way

public function addUserGroup($data) {
    $this->db->query("INSERT INTO " . $this->db->dbprefix . "user_group SET 
    name = " . $this->db->escape($data['name']) . ", 
    permission = " . (isset($data['permission']) ? $this->db->escape(serialize($data['permission'])) : '') . " ");
}

public function editUserGroup($user_group_id, $data) {
    $this->db->query("UPDATE " . $this->db->dbprefix . "user_group SET 
    name = " . $this->db->escape($data['name']) . ", 
    permission = " . (isset($data['permission']) ? $this->db->escape(serialize($data['permission'])) : '') . " WHERE user_group_id = '" . (int)$user_group_id . "'");
}

you can adapt what is used here http://stackoverflow.com/questions/15085990/can-you-have-if-then-else-logic-in-sql. Update the record, add `IF @@rowcount = 0 INSERT ......` — RST, Sep 24 '14 at 09:57

score 0 · Answer 1 · answered Sep 24 '14 at 09:50

try to save values in variable and pass var to query and try to add proper quoting to query vars

public function addUserGroup($data) {
  $perm = (isset($data['permission']) ? $this->db->escape(serialize($data['permission'])) : '');
  $this->db->query("INSERT INTO " . $this->db->dbprefix . "user_group SET name = '" . $this->db->escape($data['name']) . "', permission = '" .$perm. "' ");

}

score 0 · Answer 2 · answered Sep 24 '14 at 09:51

You could do this with one query pretty neat:

"INSERT INTO " . $this->db->dbprefix . "user_group SET name = " . 
$this->db->escape($data['name']) . ", 
permission = " . (isset($data['permission']) ? $this->db->escape(serialize($data['permission'])) : '') . 
" ON DUPLICATE KEY UPDATE ". 
$this->db->dbprefix . "user_group SET name = " . 
$this->db->escape($data['name']) . ", 
permission = " . (isset($data['permission']) ? $this->db->escape(serialize($data['permission'])) : '') . " 
WHERE user_group_id = '" . (int)$user_group_id . "'");

The only thing required is a primary key on the table

score 0 · Accepted Answer · answered Sep 24 '14 at 09:57

You can set if/else condition using "user_group_id". I think this must be coming from post array like $this->input->post('user_group_id').

So you can create one function which will check these conditions like below:

function my_function(){
   if($this->input->post('user_group_id')){
    // call update query
   }else{
    // call insert query
   }
}

if query is not update

3 Answers3