Returning a value from a MYSQL Query?

Kimochi · February 27th, 2007, 01:26 PM

I am trying to return the highest value for a column in one of my tables so I can insert a new rows with a value one higher.

This is the code I have:

Code:


			
$sql1 = mysql_query("SELECT MAX(chat_id) FROM archive");
    $maxid = $sql1+1;

Unfortunately, the variable $maxid is ALWAYS being returned as 6, even when the table archive is empty!

Any suggestions?

Accurax · February 28th, 2007, 07:38 AM

dont Mysql queries return an array when you do a select from the database ?

Therefore youd need to use:

Code:

$result = mysql_query($sql1)
        or die ("error");
$row = mysql_fetch_array($result);
$maxid = $row['max_id'];

then

Code:

$plusid = $maxid +1;

I could be wrong as ive not actually done exactly what your trying to do before, but this seems more logical to me at least.

Kimochi · February 28th, 2007, 10:21 AM

Quote:

Originally Posted by Accurax

dont Mysql queries return an array when you do a select from the database ?

Therefore youd need to use:

Code:

$result = mysql_query($sql1)
        or die ("error");
$row = mysql_fetch_array($result);
$maxid = $row['max_id'];

then

Code:

$plusid = $maxid +1;

I could be wrong as ive not actually done exactly what your trying to do before, but this seems more logical to me at least.

Yeah true... Not sure if it still applies seeing as I'm just using MAX(). Maybe the MAX() function returns the ROW with the highest value?

I'll give it a try

karinne · February 28th, 2007, 10:22 AM

You still need the fetch_array function tho...

grahame · February 28th, 2007, 11:05 AM

In other words, try:

$rs = mysql_query("SELECT MAX(chat_id) FROM archive");
$sql1 = mysql_fetch_row($rs);
$maxid = $rs[0]+1;

mysql_query is rather like an fopen - it returns a handle to the resource which is a reult set - in this case just one row with one element in it, which you then read.

Edit - see my later post - the above contains a very silly error on my part - lat line should read $sql1 and not $rs

Kimochi · February 28th, 2007, 11:06 AM

Quote:

Originally Posted by grahame

In other words, try:

$rs = mysql_query("SELECT MAX(chat_id) FROM archive");
$sql1 = mysql_fetch_row($rs);
$maxid = $rs[0]+1;

mysql_query is rather like an fopen - it returns a handle to the resource which is a reult set - in this case just one row with one element in it, which you then read.

Okay then. Thanks a lot

Kimochi · February 28th, 2007, 12:26 PM

Hmm it's still not working :/

The thing is, when I enter the same query in PHP-MA is returns the highest value.

grahame · February 28th, 2007, 04:09 PM

Quote:

Originally Posted by grahame

$rs = mysql_query("SELECT MAX(chat_id) FROM archive");
$sql1 = mysql_fetch_row($rs);
$maxid = $rs[0]+1;

Silly error on my part - sorry - should have read:

$rs = mysql_query("SELECT MAX(chat_id) FROM archive");
$sql1 = mysql_fetch_row($rs);
$maxid = $sql1[0]+1;