Getting last record -- Newbie question

[creeker]

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?

[Barand]

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

[creeker]

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.

[Barand]

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.

[creeker]

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.

[fenway]

[quote author=creeker link=topic=112849.msg458528#msg458528 date=1161952187]
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.
[/quote]
You mean someone else inserted the record, and you're trying to find the last one?  Date/time would be more preferable, IMHO.