How to Get Last Inserted Id in CodeIgniter

On 7/27/2016 Be the first to comment!

Hi! In this post let's see how to get last inserted id in codeigniter. While working with database systems, it's not uncommon for developers to require the last insert id from the database. By 'id' we mean the auto-incremented primary key of any table. Immediately after inserting a new record into db, you can get the id of the latest record. Similar to core PHP, codeigniter framework too provides a separate function to get last insert id after database insert operation.

How to Get Last Inserted ID in CodeIgniter?

To get last insert in codeigniter you have to use the function $this->db->insert_id(); which is part of the active record class library.

<?php
// get form data
$user = array(
    'name' => $this->input->post('txt_name'),
    'email' => $this->input->post('txt_email'),
    'password' => md5($this->input->post('txt_password')),
    'regdate' => date('Y-m-d H:i:s'),
    'status' => '1'
);

//insert into db
if ($this->db->insert('tbl_users', $user))
{
    //get last insert id
    return $this->db->insert_id();
}
return false;
?>

Above codeigniter snippet depicts the usage of db->insert_id(); function which inserts form data into database and get the last inserted id from it.

That explains about how to get last insert id in codeigniter. I hope you find this quick code igniter tutorial useful. See you in the next post!

CodeIgniter Select Query Example

On 7/23/2016 Be the first to comment!

CodeIgniter Select Query Example: This tutorial shows you how to fetch data from database in codeigniter. SELECT operation is also called as READ and one among the database CRUD process. Codeigniter comes with a comprehensive query builder interface to build complex and database-independent queries. And its Active Records library offers a bunch of functions to form wide range of select queries to read data from database.

CodeIgniter Delete Query Example

On 7/21/2016 Be the first to comment!

CodeIgniter Delete Query Example: This tutorial will show you how to delete data from database in codeigniter. Delete operation is one among the primitive database process called CRUD. As the name suggests DELETE process is used to delete records/rows from database. Similar to database Insert and Update - Delete can be performed upon single record, multiple records, upon entire table or on multiple tables at once.

Also check for CodeIgniter Insert Query Example and CodeIgniter Select Query Example.

CodeIgniter Delete Query Example:

CodeIgniter Active Records Class provides the function $this->db->delete(); to write database independent delete queries which deletes the record(s) from database.

$this->db->delete(table_name, where_clause);

This method produces delete query string with the parameters provided and runs the query. The first parameter is the table name and the second one is the where clause condition.

MySQL Table - Employees

codeigniter-delete-query-example

Let's take this mysql table as an example and see how to write different types of delete queries in codeigniter.

How to Write Delete Query in CodeIgniter?

Below codeigniter delete query with where condition will delete record(s) from 'Employees' table which has the value 'Herrod Chandler' for 'EmpName' field.

$this->db->where('EmpName', 'Herrod Chandler');
$this->db->delete('Employees');

// Produces Delete Query:
// DELETE FROM Employees WHERE EmpName = 'Herrod Chandler';

Resultset:

how-to-write-delete-query-in-codeigniter

There is an alternative way to write delete query in codeigniter. You can either use separate where clause like above or pass it as a string to $this->db->delete() function just like codeigniter update query.

$this->db->delete('Employees', array('EmpName' => 'Herrod Chandler'));

// Produces Delete String:
// DELETE FROM Employees WHERE EmpName = 'Herrod Chandler';

Delete Multiple Records in Codeigniter:

You can delete multiple records at once in codeigniter. This below example will delete the employee records whose 'EmpID' value matches in the list provided.

$id = array(2, 4, 6);
$this->db->where_in('EmpID', $id);
$this->db->delete('Employees');

// Produces Delete SQL:
// DELETE FROM Employees WHERE EmpID IN (2, 4, 6);
delete-multiple-records-in-codeigniter

Delete All Rows from Table in CodeIgniter:

If you want to delete all rows/records from a table, you should use empty_table() or truncate() function.

$this->db->empty_table('Employees');

// Produces Query:
// DELETE FROM Employees;

Using $this->db->truncate() Method:

This query generates truncate SQL string and executes it.

$this->db->truncate('Employees');

// Produces SQL:
// TRUNCATE Employees;

Delete Multiple Tables in Codeigniter:

Wonder if you can delete records from multiple tables at once? Yep! You can. While using multiple delete() make sure to pass the where_clause as an array to the function. This is the perfect example for deleting rows from multiple tables at once in codeignitar.

$this->db->delete('Employees', array('DeptName' => 'HQ'));
$this->db->delete('Department', array('DepartmentName' => 'HQ'));

// Produces Delete Query:
// DELETE FROM Employees WHERE DeptName = 'HQ';
// DELETE FROM Department WHERE DepartmentName = 'HQ';

Related Read: Delete Data from Database using Codeigniter & MySQL

That explains all about codeigniter delete query example. Using the above examples you can easily delete data from database using codeigniter and mysql.

CodeIgniter Update Query Example

On 7/19/2016 1 Comment so far

Codeigniter Update Query Example: This tutorial will show you how to update data in database in codeigniter. Update is one among the CRUD function and a primary process performed on any database system. It is used to update/edit the existing records (entity) in database. Database update can be performed on single or batch level which allows you to update single record one at a time or multiple records all-at-once in the database.

CodeIgniter Update Query Example:

To write update query in codeigniter you have to use 'active record class' library which allows you to write database independent queries and later they are converted into query strings by the respective database adapter and executed.

In code igniter you should make of use the function $this->db->update() to update records/rows in database.

$this->db->update('table_name', data);

This function generates update string with the data provided through parameters and executes the query. It's first parameter holds the table name and the second one contains an array or object of values used for updating db.

MySQL Table: Employees

codeigniter-update-query-example-table-employees

Here we'll see some of the query examples using this 'Employees' table that'll show you how to update data in database in codeigniter.

CodeIgniter Update Query with Where Clause Example:

This update query in codeigniter uses where condition/clause to edit only a specific record/row from the 'Employees' table.

$data = array(
    'Designation' => 'Senior Accountant',
    'Salary' => 192300
);
$this->db->where('EmpID', 3);
$this->db->update('Employees', $data);

// Produces Update Query:
// UPDATE Employees SET Designation = 'Senior Accountant', Salary = 192300 WHERE EmpID = 3;

Resultset:

update-query-in-codeigniter-with-where-clause-example

While writing update query in codeigniter, you can either use where clause separately with where() function like above or just pass it as a string to the $this->db->update() function itself. Here is the example for the latter.

$data = array(
    'Designation' => 'Senior Accountant',
    'Salary' => 192300
);
$this->db->update('Employees', $data, 'EmpID = 3');

// Produces Update Query String:
// UPDATE Employees SET Designation = 'Senior Accountant', Salary = 192300 WHERE EmpID = 3;

Update Query with Multiple Conditions in Codeigniter:

This code igniter update query utilizes multiple where conditions to update a particular employee record.

$data = array(
    'Designation' => 'Office Manager'
);
$this->db->where('DeptName', 'HQ');
$this->db->where('Designation', 'Manager');
$this->db->update('Employees', $data);

// Produces SQL:
// UPDATE Employees SET Designation = 'Office Manager' WHERE DeptName = 'HQ' and Designation = 'Manager';
update-query-with-multiple-conditions-in-codeigniter

Update Multiple Rows in Codeigniter:

Unlike using multiple where conditions to update a single database row, you can also update multiple rows at once in codeigniter. You must use $this->db->update_batch() function instead of update() to do this.

The function update_batch() is more or like similar to update() except for the one additional parameter 'where_key' that tells which column should be used for where condition.

$data = array(
    array(
        'EmpID' => 6
        'Designation' => 'Sales Manager',
        'Salary' => 167260
    ),
    array(
        'EmpID' => 8
        'Designation' => 'Technical Lead',
        'Salary' => 382500
    )
);

$this->db->update_batch('Employees', $data, 'EmpID');

// Produces Update Query SQL:
// UPDATE Employees SET Designation = CASE
// WHEN EmpID = 6 THEN 'Sales Manager'
// WHEN EmpID = 8 THEN 'Technical Lead'
// ELSE Designation END,
// Salary = CASE
// WHEN EmpID = 6 THEN 167260
// WHEN EmpID = 8 THEN 382500
// ELSE Salary END
// WHERE EmpID IN (6, 8)

Resultset:

update-multiple-rows-in-codeigniter

As you can see, the above query takes up 'EmpID' for where condition and updates multiple records at once based on it.

Related Read: Update Form Data in database with Codeigniter & MySQL

That explains about codeigniter update query example. With the help of these queries you can easily update data in database using php codeigniter and mysql.

CodeIgniter Insert Query Example

On 7/16/2016 Be the first to comment!

Codeigniter Insert Query Example: This tutorial will show you how to insert data into database using codeigniter. Insert is one of the CRUD, a primitive operation of a Database System. Insert is otherwise called as Create process and is used to add new records (entity) to database tables. Databases allows single or batch process, which means you can insert a single record at a time or multiple records all at once into the database.

Display JSON Data in HTML Table using jQuery and AJAX

On 7/13/2016 1 Comment so far

Hi! This post will show you how to display json data in html table using jquery and ajax call. As you know, JSON format is widely used across platforms and languages and with AJAX you can send and receive HTTP requests asynchronously between client and server. Populating json data in html table using datatable plugin has been discussed a while before and this time we'll do it without involving any third-party plug-in - just with jQuery and AJAX itself.

CodeIgniter Order By Query Example

On 7/09/2016 Be the first to comment!

CodeIgniter Order By Query: In SQL queries, ORDER BY clause/keyword provides a way to sort the resultset in ascending or descending order based upon columns/fields. The resultset can be sorted using one or more columns too. Most of the database systems sort the records in ascending order by default if not specified. Here we'll see how to write order by query in codeigniter framework to sort the database records.

To sort query resultset in codeigniter you have to use order_by() method from 'Active Record Class' library. The method takes up two parameters, first one is the table column name and second one is optional and specifies if the column values should be sorted in ascending or decending order.

order_by(column_name, sort_type);

Contact Form

Name

Email *

Message *