How to get max(id) in CodeIgniter

Question

I want to get the maximum id of row data. In my table first column is id then firstname and etc. this is the sql command I used to get the max(id) of row data.

$maxid = $this->db->query("SELECT MAX(id) FROM `emplyee_personal_details`");
print_r($maxid);

but it prints bunch of data as it is a array. But I need only the maximam id of row data for validation before data inserting and updating.

How can I get the max id? I use the CodeIgniter framework.

$this->db->select_max('id')->get('emplyee_personal_details')->row('id'); this will return the greatest id or null if there is an empty table. — mickmackusa
– mickmackusa ♦, Commented 15 hours ago

Shomz · Accepted Answer · 2012-12-22 16:28:41Z

10

Try this:

$maxid = $this->db->query('SELECT MAX(id) AS `maxid` FROM `emplyee_personal_details`')->row()->maxid;

UPDATE

This will work even if your table is empty (unlike my example above):

$maxid = 0;
$row = $this->db->query('SELECT MAX(id) AS `maxid` FROM `emplyee_personal_details`')->row();
if ($row) {
    $maxid = $row->maxid; 
}

edited Dec 22, 2012 at 16:28

answered Dec 22, 2012 at 16:06

Shomz

37.7k4 gold badges61 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

mickmackusa 18 hours ago

@Shomz you were right the first time. Even with an empty table a single-row result will be returned with the object property holding a null value. Your script is right (though you can write the column name as a parameter of row()).

Derek Nutile · Accepted Answer · 2012-12-22 16:24:29Z

5

The problem with using a raw query like "SELECT MAX(id) ..." is it is not abstract and may not work for every SQL engine. I think the active record way to do it is like this:

$this->db->select_max('id');
$query = $this->db->get('emplyee_personal_details');
// Produces: SELECT MAX(id) as age FROM emplyee_personal_details

See: http://ellislab.com/codeigniter/user-guide/database/active_record.html

edited Dec 22, 2012 at 16:24

answered Dec 22, 2012 at 16:18

Derek Nutile

2113 silver badges10 bronze badges

4 Comments

kiriappa Over a year ago

I want to get the final row of id in order to decide to update or insert data in my application. there may be 10 rows but final id may be 15 since some rows can be deleted.

Derek Nutile Over a year ago

Then you want the last inserted id? Like this? $this->db->insert_id(); See: ellislab.com/codeigniter/user-guide/database/helpers.html

Shomz Over a year ago

He's not inserting anything, but deciding whether to insert or update... which is why that won't work.

Carmela Over a year ago

how to put $this->db->select_max('id') in $this->db->where() condition?

Shahzad Barkati · Accepted Answer · 2017-02-25 06:02:56Z

1

SELECT id FROM table ORDER BY id DESC LIMIT 1

That will get you the highest id value, and when I read this, "it prints bunch of data as it is a array" I get the sense that what you really want is a part of that array. DB queries always return complex structures like arrays or objects. So if you wanted just the scalar value (the number as an integer) you might use something like this:

$maxid = (int)$maxid['id'];

or like this (if you have an object):

$maxid = (int)$maxid->id;

HTH, ~Ray

edited Feb 25, 2017 at 6:02

Shahzad Barkati

2,5506 gold badges28 silver badges34 bronze badges

answered Dec 22, 2012 at 16:06

Ray Paseur

2,2042 gold badges14 silver badges17 bronze badges

4 Comments

Niet the Dark Absol Over a year ago

See my comment on Samuel's answer.

Ray Paseur Over a year ago

@Kolink: I understand your point and that may be quite true, but as a practical matter, I also understand the following... the table is probably a list of employees, thus it does not have very many rows. The difference in elapsed time between any of the query solutions here will be impossibly small, perhaps below the threshold of measurement. Some things should not be optimized because it takes longer to make the change than all the value the change will ever produce!

Niet the Dark Absol Over a year ago

Well, your answer is suggesting they change the whole query. Shomz's answer explains how to use the result of the query they have.

Kamran Ahmed Over a year ago

@Ray Paseur Looking at your Reputation, you may have considered formatting the code in you answer.

mickmackusa · Accepted Answer · 2025-11-29 08:50:41Z

1

return $this->db
    ->select_max('id')
    ->get('your_table_name')
    ->row()
    ->id;

edited 18 hours ago

mickmackusa♦

49.2k13 gold badges98 silver badges165 bronze badges

answered Feb 2, 2018 at 5:08

Raham

4,9613 gold badges26 silver badges29 bronze badges

Collectives™ on Stack Overflow

How to get max(id) in CodeIgniter

4 Answers 4

1 Comment

4 Comments

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

4 Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related