Jump to content


Photo

Getting last record -- Newbie question


  • Please log in to reply
5 replies to this topic

#1 creeker

creeker
  • New Members
  • Pip
  • Newbie
  • 5 posts

Posted 26 October 2006 - 07:17 PM

I'm trying to make a query to get the last record from a DB. When I use "LAST_INSERT_ID()" I always get 0 as the query.

Is there something wrong with this function or do I have to use it combined with an INSERT or UPDATE?

Does this make sense?



#2 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,023 posts

Posted 26 October 2006 - 10:51 PM

You need to call LAST_INSERT_ID() immediately after an insert query to retrive the value of the auto_increment column in the table. It it returns the last id generated for the current connection.

I you come back to the table with a new connection (new page) then you can get the last record by

SELECT * FROM tablename ORDER BY id DESC LIMIT 1
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#3 creeker

creeker
  • New Members
  • Pip
  • Newbie
  • 5 posts

Posted 27 October 2006 - 12:05 AM

Ah ha. I thought it could be called later.

I came to the code you suggested. In an older topic someone said it didn't comform to ACID.
Do you know what that is and if it makes a difference?

Thanks.

#4 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,023 posts

Posted 27 October 2006 - 06:32 AM

Basically, don't rely on that latter method finding the last id generated in order to generate the next one yourself. Let the auto_increment do it, it's safer.
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#5 creeker

creeker
  • New Members
  • Pip
  • Newbie
  • 5 posts

Posted 27 October 2006 - 12:29 PM

I'm not using it to auto increment. I'm calling in from a site to find the last added record.
So "SELECT * FROM tablename ORDER BY id DESC LIMIT 1" seems a good method since the ID is auto_incremented.
I guess another method would be using timestamps.

#6 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 28 October 2006 - 04:00 PM

I'm not using it to auto increment. I'm calling in from a site to find the last added record.
So "SELECT * FROM tablename ORDER BY id DESC LIMIT 1" seems a good method since the ID is auto_incremented.

You mean someone else inserted the record, and you're trying to find the last one?  Date/time would be more preferable, IMHO.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users