Conditional Insert using codeigniter

Question

I want to insert row in a table if not already exists else update it. How can I rephrase this query in codeignitor?

INSERT INTO fa_seat_details (mall_id, cineplex_name, audi_number, number_of_rows, number_of_columns) VALUES(15, "PVR", 1, 18, 16) ON DUPLICATE KEY UPDATE cineplex_name = "some name", number_of_rows=10, number_of_columns=20;

This is the table structure,

+-------------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | mall_id | int(11) | NO | MUL | NULL | | | cineplex_name | varchar(64) | NO | | NULL | | | audi_number | int(11) | NO | | NULL | | | number_of_rows | int(11) | NO | | NULL | | | number_of_columns | int(11) | NO | | NULL | | +-------------------+-------------+------+-----+---------+----------------+

may be duplicate of http://stackoverflow.com/questions/3361490/how-would-i-use-on-duplicate-key-update-in-my-codeigniter-model and many such this — siddhesh, Oct 26 '15 at 13:07
combination of mall_id and audi_number is unique in this table — Abhishek Sharma, Oct 26 '15 at 17:31
thanks siddhesh for that link, i might have missed that link in search results — Abhishek Sharma, Oct 26 '15 at 17:35

score 1 · Answer 1 · answered Oct 26 '15 at 13:46

Try this

public function FunctionName($value)//pass data to here
{
    $query = $this->db->query("SELECT * FROM category WHERE aa = 'bbbbb'");
    $result = $query->result_array();
    $count  = count($result);

    if (empty($count)) {

        # insert code
        $data = array(
           'title' => 'My title' ,
           'name' => 'My Name' ,
           'date' => 'My date'
        );

        $this->db->insert('mytable', $data); 
    }
    elseif ($count >1 ) {
        # Having Multiple data on it
        echo "Wrong data selected";
    }
    else{

        #update code
        $data = array(
           'title' => $title,
           'name' => $name,
           'date' => $date
        );

        $this->db->where('id', $id);
        $this->db->update('mytable', $data); 
    }
}

score 0 · Answer 2 · answered Oct 26 '15 at 14:55

Maybe you want something like this:

$data['mall_id'] = 15;
$data['cineplex_name'] = "PVR";
$data['audi_number'] = 1;
$data['number_of_rows'] = 18;
$data['number_of_columns'] = 16;

$sql = $this->db->insert_string('fa_seat_details', $data) . ' ON DUPLICATE KEY UPDATE cineplex_name = "some name", number_of_rows=10, number_of_columns=20';
$this->db->query($sql);

Conditional Insert using codeigniter

2 Answers2