如何使用 PHP 获取 MySQL 数据库表中的最后一条记录？

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

提问by ritch

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

我想使用 PHP 获取 MySQL 数据库表中的最后一个结果。我该怎么做呢？

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

我在表中有 2 列，MessageID(auto) 和 Message。

I already know how to connect to the database.

我已经知道如何连接到数据库。

回答by OMG Ponies

Use mysql_query:

使用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:

SQL 查询：

  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 messageidis auto-increment, the highest value is the most recent one...

...使用 ORDER BY 设置值，因此最高值是结果集中的第一行。LIMIT 表示在所有这些行中，结果集中只实际返回第一行。因为messageid是自动递增，最高值是最近的...

回答by Dean Harding

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

关系数据库中的记录没有固有的“顺序”，因此如果没有某种ORDER BY子句，您将无法获取“最后”记录。

Therefore, in order to fetch the "last" record, simply reverse the ORDER BYclause (change ASCto DESCor vice versa) then select the firstresult.

因此，为了获取“最后一个”记录，只需反转ORDER BY子句（更改ASC为DESC或反之亦然）然后选择第一个结果。

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

回答by simon

Of course you can select the last row by sorting DESC in your query. But what if you want to select the first row and then the last. You can run a new query, but you can also use the function mysql_data_seek. check code below:

当然，您可以通过对查询中的 DESC 进行排序来选择最后一行。但是如果你想选择第一行然后选择最后一行呢？您可以运行新查询，但也可以使用函数mysql_data_seek。检查下面的代码：

$result = mysql_query('YOUR QUERY') or die('Invalid query: ' . mysql_error());
$row_first = mysql_fetch_array($result);
mysql_data_seek($result , (mysql_num_rows($result)-1));
$row_last =  mysql_fetch_array($result);

Hope this helps

希望这可以帮助

回答by Andrew Hare

The MySql query would look like this:

MySql 查询如下所示：

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.

我对 PHP 太生疏了，无法为您提供执行此操作的正确语法。

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 1clause simply limits the result setto one record - the last one in the table.

此查询有效，因为您有一个自动递增的标识字段 ( MessageID)。通过按该字段的降序（从大到小）对结果进行排序，我们实际上以相反的顺序返回了表中的记录。该limit 1子句只是将结果集限制为一条记录——表中的最后一条。

回答by The Mighty Rubber Duck

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.

在这种情况下，您应该使用适当的方法（取决于您使用的扩展）mysqli->insert_id 或 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.

如果您的意思是“表中的最新条目”，那么像 Andrew Hare 的 SQL 查询正是您所需要的。

回答by Icode4food

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.

您是指最后一条记录还是需要最近插入的记录的 ID？为此，您将使用 PHPmysql_insert_id()函数。或者，如果您使用的是 myusqli 扩展，请使用 $mysqli->insert_id。

回答by Icode4food

this code of php works fine

这段 php 代码工作正常

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

if you don't have concurrent entries going into some table.b'cause concurrent entries may not go in accordance of their insertion order.

如果您没有并发条目进入某个表。b'因为并发条目可能不会按照它们的插入顺序进行。

回答by JMASTER B

for some reason (which I don't know why), my boss force me to get the data in this way:

出于某种原因（我不知道为什么），我的老板强迫我以这种方式获取数据：

$message_arr = array();
while ($row = mysql_fetch_assoc($result)) {
$message_arr['messageid']= $row['messageid'];
$message_arr['message']= $row['message'];

}
return $message_arr;

Of course, you need everything from OMG Ponies'sanswer I'm just telling you another way to do it =)

当然，您需要OMG Ponies 的回答中的所有内容，我只是告诉您另一种方法 =)

I hope this help.

我希望这会有所帮助。

回答by saki_theflaki

You should use SELECT query. How SELECT works.

您应该使用 SELECT 查询。SELECT 的工作原理。

SELECT * FROM table- selects everything in a table (id, row 1, row 2,...) SELECT id FROM table- selects only particular row from table.

SELECT * FROM table- 选择表中的所有内容（id，第 1 行，第 2 行，...） SELECT id FROM table- 仅从表中选择特定行。

If you now know, how to select, you can use additional logic.

如果您现在知道如何选择，则可以使用其他逻辑。

SELECT * FROM table ORDER by id DESC LIMIT 1;

selects everything from table table, orders it by id - orders it DESCENDING and limits the query to only one result.

从表中选择所有内容，按 id 对其进行排序 - 将其排序为 DESCENDING 并将查询限制为仅一个结果。

If you would do it like this:

如果你会这样做：

SELECT * FROM table ORDER by id ASC limit 1;- you would get 1 entry into database.

SELECT * FROM table ORDER by id ASC limit 1;- 您将获得 1 个进入数据库的条目。

You can order it by any row you want.

您可以按您想要的任何行进行订购。

Hope it helps.

希望能帮助到你。

回答by Suleka_28

One thing to remember is that data does notget saved in the insertion orderin any MYSQL database. So in order to get the last entered record u will have to have an auto incrementfield. Since there is an auto increment field in this table we are good to go.

要记住的一件事是，数据不会按插入顺序保存在任何 MYSQL 数据库中。因此，为了获得最后输入的记录，您必须有一个自动增量字段。由于此表中有一个自动增量字段，我们很高兴。

The below script will help to get the last entered record

下面的脚本将有助于获取最后输入的记录

<?php    
    $sql = "SELECT * FROM table_name ORDER BY MessageID DESC LIMIT 2";
    $result_set = mysql_query($sql);

    if($result_set){
           while($row = mysql_fetch_array($result_set)) {
               echo "Message Id: ".$row['MessageID']."<br>";
               echo "Message: ".$row['Message']."<br>";
           }
          //creating alert
          echo "<script type=\"text/javascript\">alert('Data was Retrieved 
             successfully');</script>";
     }

     else{
         //creating alert
         echo "<script type=\"text/javascript\">alert('ERROR! Could Not Retrieve 
             Data');</script>";
     }
?>

The query selects all the records in the table and orders them according to the descending order of the MessageID(as it is the auto increment field) and limits the returned result to only one record. So since the table is ordered according to the descending order of the MessageIDonly the last entered record will be returned.

查询选择表中的所有记录，并根据MessageID的降序（因为它是自增字段）对它们进行排序，并将返回的结果限制为只有一条记录。因此，由于表是根据MessageID的降序排列的，因此只会返回最后输入的记录。

NOTE:if you are using a newer version you will have to use mysqli_query($connection_variable,$sql);instead of mysql_query($sql);and
mysqli_fetch_array($result_set)instead of mysql_fetch_array($result_set)

注意：如果您使用的是较新版本，则必须使用mysqli_query($connection_variable,$sql); 而不是mysql_query($sql); 和
mysqli_fetch_array($result_set)而不是mysql_fetch_array($result_set)