Last ID Number FFrom DataBase

[monkeybidz]

How do I call the last ID number from a table in MySql and echo it? This is what I have so far:

 

$query = mysql_query("SELECT idnum FROM ready_aclassft");

 

Maybe im comeing at it all wrong. :'(

[Rifts]

SELECT * FROM Persons ORDER BY age DESC

[monkeybidz]

Can I see more code? That got me a result of S instead of a number. Thanks in advance,

[systemick]

$result = mysql_query("SELECT MAX(idnum) as id from ready_aclassft");

$row = mysql_fetch_row  ($result );

echo $row['id'];

[monkeybidz]

Not getting any result still.

[litebearer]

might try....

 

(modify to fit your params)

$sql = mysql_query("SELECT id FROM table_name ORDER BY id DESC LIMIT 1");     
$row = mysql_fetch_assoc($sql);   
$item_number = $row['id']; 

[kickstart]

Hi

 

Why do you need this?

 

If you need it to determine the last parent record id to insert related children then getting the max id is not a very safe way of doing it.

 

Using mysql_insert_id.

 

If you do just want the max number then either of the previous 2 suggestions should work (although I prefer systemicks suggestion), and if they don't then it suggest a different issue. I would put an or die(mysql_error()); on the query line to check for an error.

 

All the best

 

Keith

 

[monkeybidz]

I need it to display a total amount of items to date which in my case would be the last id count and not rows. I tried both others ways, but still get :

 

 

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource

[kickstart]

Hi

 

The id can easily not be the count (do an insert and roll it back and you will land up with a gap in the ids). You might be OK at the moment but it would be very easy for this to change in the future.

 

Try this:-

 

$result = mysql_query("SELECT MAX(idnum) as id from ready_aclassft") or die(mysql_error());
$row = mysql_fetch_row($result );
echo $row['id'];

 

All the best

 

Keith

[monkeybidz]

Will not echo $row['id']

[PFMaBiSmAd]

mysql_fetch_row() won't return an associative array. You would need to use mysql_fetch_assoc()

 

Are you developing and debugging your code on a system with error_reporting set to E_ALL and display_errors set to ON so that all the errors php detects will be reported and displayed? You will save a ton of time.

[monkeybidz]

Wow, you did it. that was the problem. Thanks to all!

[vijdev]

this will work only if you are not going to delete any rows - depends on your application.

 

am assuming your field is an aut-inc field that you are trying to play with..

suppose your last was 100, and then a transaction deleted 100, then you get answer as 99, based on the current methods suggested...which truly is not the ultimately correct answer..u still need to account for the 100th value..

 

now if you need this to predict truly the next auto-inc, use below:

http://blog.jamiedoris.com/geek/560/

 

<?
$tablename 		= "tablename";
$next_increment 	= 0;
$qShowStatus 		= "SHOW TABLE STATUS LIKE '$tablename'";
$qShowStatusResult 	= mysql_query($qShowStatus) or die ( "Query failed: " . mysql_error() . "<br/>" . $qShowStatus );

$row = mysql_fetch_assoc($qShowStatusResult);
$next_increment = $row['Auto_increment'];

echo "next increment number: [$next_increment]";
?>

 

[PFMaBiSmAd]

You cannot 'get' the next available auto-increment value and use it, because between the point where you get it and the point where you use it, another query could be executed that uses that value and the value you 'got' is is no longer the next available value.

[vijdev]

You cannot 'get' the next available auto-increment value and use it, because between the point where you get it and the point where you use it, another query could be executed that uses that value and the value you 'got' is is no longer the next available value.

 

looks like i've been mistaken...

1.then, the max method is also not correct, if you have 20 deleted items between 1->100, and the latest(101) also deleted, the max will say 100, but in reality you have only 80

2. what are the chances that your next auto-inc value gets overtaken in a few micro-seconds?