0

I'm trying to get the last row id from a table in MySQL, using PHP and I have the following code:

$query = "SELECT MAX(id) FROM #__djcf_items";   
$db->setQuery($query);
$id = $db->loadResult()+1;

And it works, but if I delete last row then that code will return what I want -1...

How can I get the "global" last row id even after deleting row(s)?

Zariweya
  • 295
  • 3
  • 13
  • 2
    I think a good question to ask here is what you need it for? If (as you most likely should), you're using auto_increment, the database will handle it automatically at next insert. Getting/storing it outside the database and using it later may make things fail if other inserts are going on simultaneously. – Joachim Isaksson May 03 '14 at 13:55
  • Yeah, I'm using auto_increment, but I need the id to insert something in the db before that id is generated. I know I can take the id after the "other" code generates it, but I'm doing things in a diferent (and I think, easier) way and I just need that id. – Zariweya May 03 '14 at 13:58
  • Consider the case of you getting the id and preparing to insert something. In the mean time, another query inserts something which makes your (under preparation) insert have an id that is a duplicate. This will make your insert fail and your system fail randomly under load. – Joachim Isaksson May 03 '14 at 13:59
  • What difference could it make if the activity takes place before or (as it should) *after* the row is created – Strawberry May 03 '14 at 14:01
  • Uhm...nice point @JoachimIsaksson. You are right. Thank you very much. – Zariweya May 03 '14 at 14:06
  • @Strawberry it is just a matter of files. I thought was easier doing "the thing" in a specific file that is called just before the file that generates the id. – Zariweya May 03 '14 at 14:07

2 Answers2

0

You can find solution on this topic.

SELECT TABLE_ROWS
FROM information_schema.tables 
WHERE table_name='the_table_you_want' -- Can end here if only 1 DB 
  AND table_schema = DATABASE();      -- See comment below if > 1 DB
Community
  • 1
  • 1
Alexey Palamar
  • 1,440
  • 1
  • 10
  • 16
-2

You can run another select on the database and query it for the last id of the last row of the database:

SELECT * FROM your_table ORDER BY id DESC LIMIT 1
jme11
  • 17,134
  • 2
  • 38
  • 48
  • That won't handle deleted rows, which he is asking for. – Joachim Isaksson May 03 '14 at 14:01
  • Maybe I don't understand the question, but I read it to be 'get me the last row id left in the database after I've deleted a / some rows'. Either way, I agree with the others that the entire approach is misguided. – jme11 May 03 '14 at 14:12
  • Nope, sorry if i did not explain myself properly. I want the last row id added, even after deleting rows. So...if I had just inserted a new row and that row has id=58 and I delete that row, I want to get that id=58 anyway. – Zariweya May 03 '14 at 14:18
  • But anyway, it does not matter anymore. I'm doing it after the item is inserted in the db. Thank you to everyone. – Zariweya May 03 '14 at 14:19