PHP problem or MySQL problem?

[AndyGroom]

I've got a database with 3 records and a field called "id" which is an auto-incrementing field. If I print the contents of the database it correctly lists records with ids of 1, 2 and 3.

However when I try to use the MAX function to return the highest value for id it always returns 1. As I'm new to both PHP and MySQL, can someone with patience and experience point me in the right direction?!

// $conn is a handle to the database connection

$result = mysql_query("SELECT id, MAX(id) FROM topics", $conn) or die(mysql_error($conn));
$row = mysql_fetch_array($result);
$lastrec = $row[0];
echo "Most recent record number is " . $lastrec;

Output is:
Most recent record number is 1

- Andy
___________________________________________________________________
If you think nobody cares you're alive, try missing a couple of mortgage payments

 

[jpadie]

it's a mysql question.

the query you are using is incorrect. to return the highest auto increment in the table there are a number of way

select id from topics order by id desc limit 1

or perhaps

select max(id) from topics

i think that the first is less computationally expensive.

 

[vacunita]

Your query returns 2 fields. id, and Max(id). PHP is requesting the first field (id) which will always return the first id the query finds. However your actual Max id value is in the second field.

You have two choices:

use $row[1] rather than 0, or remove the id field from your list of fields so it only returns the value of the MAX function.

SELECT MAX(id) FROM topics

----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

Web & Tech

 

[AndyGroom]

Thanks, I thought I'd tried every combination of things but both those suggestions work fine.

- Andy
___________________________________________________________________
If you think nobody cares you're alive, try missing a couple of mortgage payments