{"id":2693,"date":"2018-01-05T22:32:35","date_gmt":"2018-01-05T17:02:35","guid":{"rendered":"http:\/\/fellowtuts.com\/?p=2693"},"modified":"2018-07-24T23:09:36","modified_gmt":"2018-07-24T17:39:36","slug":"insert-record-codeigniter-exists-update","status":"publish","type":"post","link":"https:\/\/fellowtuts.com\/codeigniter\/insert-record-codeigniter-exists-update\/","title":{"rendered":"3 Ways – Insert Record in Codeigniter If not exists Else Update"},"content":{"rendered":"

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\u00a0as well as\u00a0Query Binding features\u00a0in Codeigniter to insert or update a record.<\/p>\n

There is also similar article for the native PHP and MYSQL way. You can read that here<\/a>.<\/p>\n

#1 Insert\/Update, the Long if-else Way<\/h3>\n
$this->db->where('id', $id);\r\n$q = $this->db->get('your_table_name');\r\n$this->db->reset_query();\r\n\t\r\nif ( $q->num_rows() > 0 ) \r\n{\r\n\t\/\/$this->db->where('id', $id);\r\n\t\/\/$this->db->update('your_table_name', $data);\r\n\t$this->db->where('id', $id)->update('your_table_name', $data);\r\n} else {\r\n\t\/\/$this->db->set('id', $id);\r\n\t\/\/$this->db->insert('your_table_name', $data);\r\n\t$this->db->set('id', $id)->insert('your_table_name', $data);\r\n}<\/pre>\n
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.\u00a0While the above code is an easy example, I don’t recommend this as it requires two database requests.<\/p>\n
However, there is something to note. We are performing two actions on the database object in a\u00a0single 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.<\/p>\n
#2 Insert Record with Replace Statement<\/h3>\n
Codeigniter Query Builder Class<\/a> has replace() method. This is basically the SQL standard for (optional) DELETE + INSERT and uses PRIMARY and UNIQUE keys<\/a> as the determining factor.<\/p>\n
$data = array(\r\n\t\t'id'      => 56,\r\n        'title'   => 'My title',\r\n        'name'    => 'My Name',\r\n        'dob'     => 'Date of birth',\r\n\t\t'updated' => date('Y-m-d H:i:s')\r\n);\r\n\r\n$this->db->replace('your_table_name', $data);<\/pre>\n
Here, the id<\/code> column is our primary key. The replace()<\/em> method will delete any row first if there exists with id<\/code> value 56<\/code>. Then it will insert a\u00a0new record with the values you have supplied regardless of there was such row or not.<\/p>\n
#3 Insert Record or Update with Query Bindings<\/h3>\n
$values = array(\r\n\t\t'id'      => 56,\r\n        'title'   => 'My title',\r\n        'name'    => 'My Name',\r\n        'dob'     => 'Date of birth',\r\n\t\t'updated' => date('Y-m-d H:i:s')\r\n);\r\n\r\n$data = array_values($values);\r\n$data[] = $values['title'];\r\n$data[] = $values['name'];\r\n$data[] = $values['dob'];\r\n$data[] = $values['updated'];\r\n\r\n$sql = 'INSERT INTO your_table_name (id, title, name, dob, updated) VALUES (?, ?, ?, ?, ?) ON DUPLICATE KEY UPDATE title = VALUES(?), name = VALUES(?), dob = VALUES(?), updated = VALUES(?)';\r\n\r\n$q = $this->db->query($sql, $data);<\/pre>\n
We have used\u00a0ON DUPLICATE KEY UPDATE clause of MYSQL and Query Bindings feature from Codeigniter here. The values specified in the $data<\/code> array will replace the question marks in the query.<\/p>\n
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<\/a>\u00a0and recommend you read that.<\/p>\n
Hence, you don\u2019t 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.<\/p>\n","protected":false},"excerpt":{"rendered":"
There’re 3 ways to insert record in Codeigniter if it doesn’t exist else update the record if it exists. Here we’re using Active Record as well as Query Binding features\u00a0in Codeigniter to insert or update a record.<\/p>\n
Read More<\/a><\/p>","protected":false},"author":3,"featured_media":2711,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[78,19],"tags":[47,60,59],"class_list":["post-2693","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-codeigniter","category-mysql","tag-database","tag-query","tag-security"],"yoast_head":"\n3 Ways - Insert Record in Codeigniter If not exists Else Update<\/title>\n<meta name=\"description\" content=\"Use Codeigniter's Active Record & Query Binding to insert record or update. Insert record in Codeigniter if it doesn't exist else update in 3 ways.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/fellowtuts.com\/codeigniter\/insert-record-codeigniter-exists-update\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"3 Ways - Insert Record in Codeigniter If not exists Else Update\" \/>\n<meta property=\"og:description\" content=\"Use Codeigniter's Active Record & Query Binding to insert record or update. Insert record in Codeigniter if it doesn't exist else update in 3 ways.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/fellowtuts.com\/codeigniter\/insert-record-codeigniter-exists-update\/\" \/>\n<meta property=\"og:site_name\" content=\"Fellow Tuts\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/FellowTuts\/\" \/>\n<meta property=\"article:published_time\" content=\"2018-01-05T17:02:35+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2018-07-24T17:39:36+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/fellowtuts.com\/wp-content\/uploads\/2018\/01\/insert-record-codeigniter.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"600\" \/>\n\t<meta property=\"og:image:height\" content=\"600\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"Amit Sonkhiya\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@SonkhiyaAmit\" \/>\n<meta name=\"twitter:site\" content=\"@fellowtuts\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Amit Sonkhiya\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"3 Ways - Insert Record in Codeigniter If not exists Else Update","description":"Use Codeigniter's Active Record & Query Binding to insert record or update. Insert record in Codeigniter if it doesn't exist else update in 3 ways.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/fellowtuts.com\/codeigniter\/insert-record-codeigniter-exists-update\/","og_locale":"en_US","og_type":"article","og_title":"3 Ways - Insert Record in Codeigniter If not exists Else Update","og_description":"Use Codeigniter's Active Record & Query Binding to insert record or update. Insert record in Codeigniter if it doesn't exist else update in 3 ways.","og_url":"https:\/\/fellowtuts.com\/codeigniter\/insert-record-codeigniter-exists-update\/","og_site_name":"Fellow Tuts","article_publisher":"https:\/\/www.facebook.com\/FellowTuts\/","article_published_time":"2018-01-05T17:02:35+00:00","article_modified_time":"2018-07-24T17:39:36+00:00","og_image":[{"width":600,"height":600,"url":"https:\/\/fellowtuts.com\/wp-content\/uploads\/2018\/01\/insert-record-codeigniter.jpg","type":"image\/jpeg"}],"author":"Amit Sonkhiya","twitter_card":"summary_large_image","twitter_creator":"@SonkhiyaAmit","twitter_site":"@fellowtuts","twitter_misc":{"Written by":"Amit Sonkhiya","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/fellowtuts.com\/codeigniter\/insert-record-codeigniter-exists-update\/#article","isPartOf":{"@id":"https:\/\/fellowtuts.com\/codeigniter\/insert-record-codeigniter-exists-update\/"},"author":{"name":"Amit Sonkhiya","@id":"https:\/\/fellowtuts.com\/#\/schema\/person\/b9570d93920c6122e8d31c5827a3c494"},"headline":"3 Ways – Insert Record in Codeigniter If not exists Else Update","datePublished":"2018-01-05T17:02:35+00:00","dateModified":"2018-07-24T17:39:36+00:00","mainEntityOfPage":{"@id":"https:\/\/fellowtuts.com\/codeigniter\/insert-record-codeigniter-exists-update\/"},"wordCount":372,"commentCount":4,"image":{"@id":"https:\/\/fellowtuts.com\/codeigniter\/insert-record-codeigniter-exists-update\/#primaryimage"},"thumbnailUrl":"https:\/\/fellowtuts.com\/wp-content\/uploads\/2018\/01\/insert-record-codeigniter.jpg","keywords":["Database","Query","Security"],"articleSection":["CodeIgniter","MySQL"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/fellowtuts.com\/codeigniter\/insert-record-codeigniter-exists-update\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/fellowtuts.com\/codeigniter\/insert-record-codeigniter-exists-update\/","url":"https:\/\/fellowtuts.com\/codeigniter\/insert-record-codeigniter-exists-update\/","name":"3 Ways - Insert Record in Codeigniter If not exists Else Update","isPartOf":{"@id":"https:\/\/fellowtuts.com\/#website"},"primaryImageOfPage":{"@id":"https:\/\/fellowtuts.com\/codeigniter\/insert-record-codeigniter-exists-update\/#primaryimage"},"image":{"@id":"https:\/\/fellowtuts.com\/codeigniter\/insert-record-codeigniter-exists-update\/#primaryimage"},"thumbnailUrl":"https:\/\/fellowtuts.com\/wp-content\/uploads\/2018\/01\/insert-record-codeigniter.jpg","datePublished":"2018-01-05T17:02:35+00:00","dateModified":"2018-07-24T17:39:36+00:00","author":{"@id":"https:\/\/fellowtuts.com\/#\/schema\/person\/b9570d93920c6122e8d31c5827a3c494"},"description":"Use Codeigniter's Active Record & Query Binding to insert record or update. Insert record in Codeigniter if it doesn't exist else update in 3 ways.","breadcrumb":{"@id":"https:\/\/fellowtuts.com\/codeigniter\/insert-record-codeigniter-exists-update\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/fellowtuts.com\/codeigniter\/insert-record-codeigniter-exists-update\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/fellowtuts.com\/codeigniter\/insert-record-codeigniter-exists-update\/#primaryimage","url":"https:\/\/fellowtuts.com\/wp-content\/uploads\/2018\/01\/insert-record-codeigniter.jpg","contentUrl":"https:\/\/fellowtuts.com\/wp-content\/uploads\/2018\/01\/insert-record-codeigniter.jpg","width":600,"height":600,"caption":"Insert Record in Codeigniter"},{"@type":"BreadcrumbList","@id":"https:\/\/fellowtuts.com\/codeigniter\/insert-record-codeigniter-exists-update\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/fellowtuts.com\/"},{"@type":"ListItem","position":2,"name":"CodeIgniter","item":"https:\/\/fellowtuts.com\/codeigniter\/"},{"@type":"ListItem","position":3,"name":"3 Ways – Insert Record in Codeigniter If not exists Else Update"}]},{"@type":"WebSite","@id":"https:\/\/fellowtuts.com\/#website","url":"https:\/\/fellowtuts.com\/","name":"Fellow Tuts","description":"Application Development & Informative Tutorials by Fellow Tuts","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/fellowtuts.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/fellowtuts.com\/#\/schema\/person\/b9570d93920c6122e8d31c5827a3c494","name":"Amit Sonkhiya","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/fellowtuts.com\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/d92d9ad9c56711a79f1a10988743adb3?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/d92d9ad9c56711a79f1a10988743adb3?s=96&d=mm&r=g","caption":"Amit Sonkhiya"},"description":"Entrepreneur, multiple programming skills and technology lover.","sameAs":["https:\/\/x.com\/SonkhiyaAmit"],"url":"https:\/\/fellowtuts.com\/author\/amit\/"}]}},"jetpack_publicize_connections":[],"jetpack_featured_media_url":"https:\/\/fellowtuts.com\/wp-content\/uploads\/2018\/01\/insert-record-codeigniter.jpg","jetpack_shortlink":"https:\/\/wp.me\/p4hwPY-Hr","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/fellowtuts.com\/wp-json\/wp\/v2\/posts\/2693","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/fellowtuts.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/fellowtuts.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/fellowtuts.com\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/fellowtuts.com\/wp-json\/wp\/v2\/comments?post=2693"}],"version-history":[{"count":0,"href":"https:\/\/fellowtuts.com\/wp-json\/wp\/v2\/posts\/2693\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/fellowtuts.com\/wp-json\/wp\/v2\/media\/2711"}],"wp:attachment":[{"href":"https:\/\/fellowtuts.com\/wp-json\/wp\/v2\/media?parent=2693"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/fellowtuts.com\/wp-json\/wp\/v2\/categories?post=2693"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/fellowtuts.com\/wp-json\/wp\/v2\/tags?post=2693"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}