There are times when you want to insert records in multiple tables. At the same time, you also want to ensure consistency. CodeIgniter simplifies MySQL transactions to perform Database operations on either all or none basis.
There are many use cases of consistency, for example needs to insert a new record in a table and retrieve the inserted id. Further, insert a row in another table using this id as a foreign key. In case the second insertion fails, the first insertion should roll back.
Transactions in CodeIgniter to Insert Records in Multiple Tables
Similarly, you might wish to delete or update records in multiple tables and undo them if either one fails. Many inserts, updates and delete operations in databases seek such kind of needs. MySQL offers transactions to accommodate the same.
However, writing transactions in SQL queries is a tedious task. But if you’re using CodeIgniter to build your application then it is super easy for you. CodeIgniter offers automatic as well as manual transactions using its query builder class.
In this article, we’re explaining how can you insert records in multiple tables with the help of transactions in CodeIgniter. After reading this article you will have a better understanding of transactions in CodeIgniter.
Also, for consistency, you should create primary and foreign keys in the database table. Must read how can you set up foreign keys in phpMyAdmin visually or using a query. Additionally, here is an awesome article for inserting records if not exists else update in a single query.
A General Function to Insert Records in Multiple Tables
Generally, we create and use a common function in the CI model for this purpose. Further, we keep the common column name same across all the tables in the database. It gives us the flexibility to write easier queries inside functions in CodeIgniter model.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | /* $first_table Array ('table' => 'string', 'data' => 'Key value array to insert') $tables Array of Arrays (('table' => 'string', 'data' => 'Key value array to insert')) $column string Common column name among tables */ function multi_table_insert($first_table, $tables, $column) { $ids = array(); $this->db->trans_start(); $this->db->insert($first_table['table'], $first_table['data']); $ids[$first_table] = $this->db->insert_id(); foreach($tables as $t) { $t['data'][$column] = $ids[$first_table]; $this->db->insert($t['table'], $t['data']); $ids[$t['table']] = $this->db->insert_id(); } $this->db->trans_complete(); return ($this->db->trans_status() === TRUE ? $ids : FALSE); } |
So here is the function with three parameters. The first parameter is an associative array having two keys and respective values. The first key “table” consists of the main table name and the later has the “data” key with an associative array of columns and values.
This table returns the inserted record id or the primary key. We will add this id in other tables referring columns. The second parameter in the function is quite similar to the first one. Just it holds multiple table names and their records to insert.
Additionally, as we don’t know the inserted id from the main table so can skip this column name and value for the “data” array in the second parameters. Finally, the third parameter is a string consisting of the common column name across all the tables.
Add Records in Multiple Tables Using CodeIgniter Transaction
Now the transaction should safely insert records in all tables as the code written. If any insertion at any stage fails then CI will remove all the previously respective entered rows in tables. Remember that all the query insertions and retrieval should be performed inside the transaction start and complete methods.
At last basis upon transaction status, we are returning either an associative array or boolean FALSE. The array consists of table names as keys and inserted ids as values. And below is the code from the CodeIgniter controller.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | $tbl_first = array('table' => 'table1', 'data' => array('col1' => 'val1', 'col2' => 'val2', 'col3' => 'val3')); $tbl_others = array( array('table' => 'table2', 'data' => array('col21' => 'val21', 'col22' => 'val22', 'col23' => 'val23')), array('table' => 'table3', 'data' => array('col31' => 'val31', 'col32' => 'val32')), array('table' => 'table4', 'data' => array('col41' => 'val41', 'col42' => 'val42', 'col43' => 'val43', 'col44' => 'val44')) ); $ids = $this->model_name->multi_table_insert($tbl_first, $tbl_others, 'col'); if($ids) { //Transaction successful $first_table_insert_id = $ids['table1']; $third_table_insert_id = $ids['table3']; } else { //Transaction failed } |
So you see how easy is using transactions in CodeIgniter. Similar to this example, you can perform insert, delete, update or combined queries as well using transactions. This example illustrates inserting records in multiple tables in CodeIgniter with help of transactions.
Do you have any question or something related to CodeIgniter? Or want to find CodeIgniter way of performing SQL queries on databases. Do let us know in comments.