How to query the max id from mysql?

Question

I have search some resources, but I still unable to make it. I would like to retrieve the data from the max id. I have some codes in .php to make query

<?php

$DB_HostName = "mysql8.000webhost.com";
$DB_Name = "";
$DB_User = "";
$DB_Pass = "";
$DB_Table = "";

$con = mysql_connect($DB_HostName,$DB_User,$DB_Pass) or die(mysql_error()); 
mysql_select_db($DB_Name,$con) or die(mysql_error()); 

$query = "SELECT MAX(id),recommendData,room,level  FROM $DB_Table";

$res = mysql_query($query,$con) or die(mysql_error());

mysql_close($con);

$rows = array();

while ($r = mysql_fetch_assoc($res))
{
    $row["maxid"] = array(
        "level" => $r["level"],
        "recommendData" => $r["recommendData"],
        "room" => $r["room"],
        "id" => $r["MAX(id)"]
    );
}

header('Content-type: application/json');

echo json_encode($row);
die;
?>

the result shows like this:

{"maxid":{"level":"2","recommendData":"8","room":"4F","id":"4"}}

the the id is right, but data of level, recommendData, room are from the first id. Do I make something wrong??

`SELECT id,recommendData,room,level FROM $DB_Table order by id DESC LIMIT 1` — , Mar 13 '14 at 02:43
Obligatory Suggestion, [**Don't use `mysql_*` functions in new code**](http://bit.ly/phpmsql). They are no longer maintained [and are officially deprecated](http://j.mp/XqV7Lp). See the [**red box**](http://j.mp/Te9zIL)? Learn about [*prepared statements*](http://j.mp/T9hLWi) instead, and use [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli) - [this article](http://j.mp/QEx8IB) will help you decide which. If you choose PDO, [here is a good tutorial](http://j.mp/PoWehJ). — Lawrence Cherone, Mar 13 '14 at 02:46
Sidenote: `000webhost.com` has both `mysqli_*` and PDO functions available; you should use them. — Funk Forty Niner, Mar 13 '14 at 02:55

score 2 · Accepted Answer · edited May 23 '17 at 12:05

2

You can try

$query = "SELECT id,recommendData,room,level  FROM $DB_Table ORDER BY id DESC LIMIT 1";

Because currently you are fetching the max id plus the other info

--

Please do not use mysql_* functions.

Use MySQLi or PDO instead

You can read on why more here

edited May 23 '17 at 12:05

Community

1
1

answered Mar 13 '14 at 02:43

Nikola

2,093
3
22
43

score 1 · Answer 2 · answered Mar 13 '14 at 02:44

1

The right way to do the query is:

SELECT id, recommendData, room, level
FROM $DB_Table
ORDER BY id desc
LIMIT 1;

That is, don't do an aggregation at all.

answered Mar 13 '14 at 02:44

Gordon Linoff

1,242,037
58
646
786

How to query the max id from mysql?

2 Answers2