retrieving the most recent record by date from MySQL

[alexh]

Each time a new record is inserted into a table I am using the MySQL function NOW() in the INSERT statement to keep a track of when the insertion happened. What SQL can I use in my SELECT statement to pull out only the most recent record?

I have tried several ways around this problem. I tried using the PHP function time() to create a timestamp and had the column in the database set to timestamp but the entries kept being inserted as just Zeros. Can anyone explain what is going on?

Thanks.

[Paul in South Africa]

Add ORDER BY $time LIMIT 1 to your query where $time is the name of the timestamp column in your table.

[alexh]

Thanks for your suggestion. I added the SQL to my statement but it failed to bring back the latest record. When I cleared the records in the db and ran the script it worked fine, but then next time around it pulled back the previous entry.

The script basically inserts a record into a table and then I need to pull out the id it created for that record. I am using the $time column to try and pull out the most recent.

$query2 = "SELECT email_id FROM massemail ORDER BY datetime LIMIT 1";

$result2 = mysql_query($query2);

$row = mysql_fetch_array($result2);
echo $row[0];

Any suggestions?

[dcrombie]

Have you tried using: mysql_insert_id();

[Paul in South Africa]

If your email_id is an auto incremented field you can use mysql_insert_id($result) to return the id generated by the insert statement.

<added>Slow typist</added>

[alexh]

That is exactly what I was after. Thanks very much both of you.