[SOLVED] maximum ID value from any table

[paul2463]

Hello Everyone

I hope the festive season is going well and many litres of alcohol is being consumed as we type(well sort of anyway) the thought is there.

a simple question but one that is spinning me around inside

I want to be able to pull the max auto incremented id value from any table, when i say any table I mean not one that I have just inserted into so I could use last_insert_id(), I mean at any time by using a query call the last maximum id value

I have tried

[code]
$query = "SELECT MAX(idmess) FROM mess";
$result = mysql_query($query)or die ('Error in query: $query. ' . mysql_error());
[/code]

it does not through an error but I cannot seem to pull that information from the results set

any help gratefully recieved, I will consume a glass of port for the person who provides the help...many thanks

[fenway]

What makes you think there's nothing in the resultset?  You haven't used a column alias, though, so I'm guessing that you're simply not pulling the right key out of the hash.

[hvle]

let me suggest this:
let say you have 2 tables: mess1 and mess2 each have autoincrement column idmess1 and idmess2 respectively.

select max(mess1.idmess1), max(mess2.idmess2) from mess1, mess2;

this will return 1 single row like this:

max(mess1.idmess1) | max(mess2.idmess2)
53                        |  43

so, you will retrieve this result in an array, and use max_array to find the max id.

If you don't get the idea, let me know

[paul2463]

am I corect in  my thinking then that if I use my origonal query with some extra lines
[code]
<?php
$query = "SELECT MAX(idmess) FROM mess";  // answer is lets say 52
$result = mysql_query($query)or die ('Error in query: $query. ' . mysql_error());
$row = mysql_fetch_assoc($result);
$max = $row['MAX(idmess)'];
echo $max; //output = 52
?>
[/code]

is this the way I would retrieve the data I want??

thanks for the help so far

[hvle]

yes indeed, that's one correct way to get max value.
an easier way:
$row = mysql_fetch_row($result);
$max = $row[0];
this is much faster and you don't have to type that annoying 'MAX(idmess)'


another way:
$query = "select max(idmess) as max_id from mess";
$result = mysql_query($query)
$row = mysql_fetch_assoc($result);
$max = $row['max_id'];
echo $max;

[paul2463]

a very many thanks