3 Ways – Insert Record in Codeigniter If not exists Else Update

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.

Insert Record in Codeigniter

#1 Insert/Update, the Long if-else Way

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.

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

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.

You Might Interested In

2 COMMENTS

    1. Amit Sonkhiya says:

      @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];

      Reply

Leave a Reply

Enclose a code block like: <pre><code>Your Code Snippet</code></pre>.