How do I fetch the last record in a MySQL database table using PHP?

Question

I'm wanting to fetch the last result in MySQL database table using PHP. How would I go about doing this?

I have 2 Columns in the Table, MessageID(auto) & Message.

EDIT: I'm connected to the database.

Answer 1

The MySql query would look like this:

select MessageID, Message
from Table
order by MessageID desc
limit 1;

I am too rusty with PHP to give you the right syntax for executing this.

This query works because you have an auto-incrementing identifying field (MessageID). By ordering the results by that field in descending (largest to smallest) order we are effectively returning the records in the table in reverse order. The limit 1 clause simply limits the result set to one record - the last one in the table.

Answer 2

Records in a relational database do not have an intrinsic "order" so you cannot fetch the "last" record without some kind of ORDER BY clause.

Therefore, in order to fetch the "last" record, simply reverse the ORDER BY clause (change ASC to DESC or vice versa) then select the first result.

If you have an auto-increment field and you just want to find the last value that was inserted, you can use the fact that the auto-increment fields are ever-increasing (therefore the "last" one will be the one with the highest value) and do something like this:

SELECT *
FROM my_table
ORDER BY id_field DESC
LIMIT 1

Answer 3

Use mysql_query:

<?php
$result = mysql_query('SELECT t.messageid, t.message 
                         FROM TABLE t 
                     ORDER BY t.messageid DESC 
                        LIMIT 1') or die('Invalid query: ' . mysql_error());

//print values to screen
while ($row = mysql_fetch_assoc($result)) {
  echo $row['messageid'];
  echo $row['message'];
}

// Free the resources associated with the result set
// This is done automatically at the end of the script
mysql_free_result($result);

?>

The SQL query:

  SELECT t.messageid, t.message 
    FROM TABLE t 
ORDER BY t.messageid DESC 
   LIMIT 1

...uses the ORDER BY to set the values so the highest value is the first row in the resultset. The LIMIT says that of all those rows, only the first is actually returned in the resultset. Because messageid is auto-increment, the highest value is the most recent one...

Answer 4

What do you mean by "the last result"? You need to precise a bit more.

Do you mean "the last entry I registered"?

In this case you should use the appropriate method (depending on the extension you are using) mysqli->insert_id OR mysql_insert_id.

If you mean "the latest entry in the table", an SQL query such as Andrew Hare's is just what you need.

Answer 5

Do you mean the last record or do you need the id of the most recently inserted record? For that you would use the PHP mysql_insert_id() function. Or if you are using the myusqli extension use $mysqli->insert_id.