There are three ways to insert record in Codeigniter if it doesn’t exist else update the record if it exists. Here we’re using the Active Record as well as Query Binding features in Codeigniter to insert or update a record.
There is also similar article for the native PHP and MYSQL way. You can read that here.
#1 Insert/Update, the Long if-else Way
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | $this->db->where('id', $id); $q = $this->db->get('your_table_name'); $this->db->reset_query(); if ( $q->num_rows() > 0 ) { //$this->db->where('id', $id); //$this->db->update('your_table_name', $data); $this->db->where('id', $id)->update('your_table_name', $data); } else { //$this->db->set('id', $id); //$this->db->insert('your_table_name', $data); $this->db->set('id', $id)->insert('your_table_name', $data); } |
In the above approach, we’re first querying the database for existing record. If it doesn’t exist then we’re running an insert record query else updating that record. While the above code is an easy example, I don’t recommend this as it requires two database requests.
However, there is something to note. We are performing two actions on the database object in a single line of code. The line no. #8 and #9 can be combined into one line as no. #10. Similarly line no. #12 and #13 are executed on one line, no. #14.
#2 Insert Record with Replace Statement
Codeigniter Query Builder Class has replace() method. This is basically the SQL standard for (optional) DELETE + INSERT and uses PRIMARY and UNIQUE keys as the determining factor.
1 2 3 4 5 6 7 8 9 | $data = array( 'id' => 56, 'title' => 'My title', 'name' => 'My Name', 'dob' => 'Date of birth', 'updated' => date('Y-m-d H:i:s') ); $this->db->replace('your_table_name', $data); |
Here, the id column is our primary key. The replace() method will delete any row first if there exists with id value 56. Then it will insert a new record with the values you have supplied regardless of there was such row or not.
#3 Insert Record or Update with Query Bindings
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | $values = array( 'id' => 56, 'title' => 'My title', 'name' => 'My Name', 'dob' => 'Date of birth', 'updated' => date('Y-m-d H:i:s') ); $data = array_values($values); $data[] = $values['title']; $data[] = $values['name']; $data[] = $values['dob']; $data[] = $values['updated']; $sql = 'INSERT INTO your_table_name (id, title, name, dob, updated) VALUES (?, ?, ?, ?, ?) ON DUPLICATE KEY UPDATE title = VALUES(?), name = VALUES(?), dob = VALUES(?), updated = VALUES(?)'; $q = $this->db->query($sql, $data); |
We have used ON DUPLICATE KEY UPDATE clause of MYSQL and Query Bindings feature from Codeigniter here. The values specified in the $data array will replace the question marks in the query.
Binding simplifies your query syntax by letting the system put the queries together for you. Also, it escapes the values automatically to produce safer query. We have written about database security concerns and recommend you read that.
Hence, you don’t have to remember to manually escape data and the engine does it automatically for you. So all you need is too correctly specify values in the array as well as writing the correct query. Here, we are finishing the article. Let us know in the comments which method you like and use.
how to handle bulk insert?
You can now use upsert() and upsertBatch() methods.
Why
idis not included in array $data in 3rd way?@havilernanda:disqus
The ‘Id’ column is included from line number #9. If you run the following code below, you will get 56.
echo $data[0];