Page 1 of 1

Extracting data from sql using max(column)

Posted: Mon Oct 13, 2008 10:00 am
by Grahamhart
Im using the following code to get the highest value in a column

$result = mysql_query("Select MAX(mycolumn) FROM mytable");
$row = mysql_fetch_assoc($result);
extract($row);
$file = $mycolumn;


problem is it always returns a blank value, even though i know that it should be a number.

I tryed using this without the bottom three lines at first and was only getting a row association result. What i want is the data that is the highest number in the column. could anyone help please?

Re: Extracting data from sql using max(column)

Posted: Mon Oct 13, 2008 10:04 am
by aceconcepts
Your field name "index" conflicts with SQL's use of this word. Rename your field name to something else like "str_index" etc...

Re: Extracting data from sql using max(column)

Posted: Mon Oct 13, 2008 10:07 am
by Grahamhart
sorry i should have been a little more clear here, index is just the example i used for the post, the field isnt called index in my table. ill edit this to save confusion

Re: Extracting data from sql using max(column)

Posted: Mon Oct 13, 2008 10:12 am
by aceconcepts
Oh ok.

Try using mysql_fetch_array()

Re: Extracting data from sql using max(column)

Posted: Mon Oct 13, 2008 10:18 am
by Grahamhart
hmm got the same result the value is just blank, it should be 25, i know this as i have run the command in my sql query launcher and got the result, just cant seem to extract the data.

Re: Extracting data from sql using max(column)

Posted: Mon Oct 13, 2008 10:21 am
by aceconcepts
Ok, try it this way:

Code: Select all

 
$result = mysql_query("Select MAX(mycolumn) FROM mytable");
$row = mysql_fetch_array($result);
echo $row['mycolumn'];
 

Re: Extracting data from sql using max(column)

Posted: Mon Oct 13, 2008 10:26 am
by Grahamhart
hmm good idea, still seems to come back blank, echos a white screen. thought this was gonna be the simplest bit of my app, didnt even consider it giving me this much of a headache :roll:

Re: Extracting data from sql using max(column)

Posted: Mon Oct 13, 2008 10:29 am
by aceconcepts
Okey dokey,

use mysql_error() to check for sql errors:

Code: Select all

$result = mysql_query("Select MAX(mycolumn) FROM mytable") or die(mysql_error());

Re: Extracting data from sql using max(column)

Posted: Mon Oct 13, 2008 10:34 am
by Grahamhart
nope returned no errors, ive already copyed the Select MAX(`mycolumn`) FROM mytable query direct to my database so i know the data is there and being returned.

Re: Extracting data from sql using max(column)

Posted: Mon Oct 13, 2008 10:39 am
by aceconcepts
Lets see if your actually getting any rows returned. Use the following:

Code: Select all

 
echo mysql_num_rows($result);
 

Re: Extracting data from sql using max(column)

Posted: Mon Oct 13, 2008 10:40 am
by dhrosti
Try assigning it to an alias...

Code: Select all

$result = mysql_query("Select MAX(mycolumn) AS maxvalue FROM mytable");
$object = mysql_fetch_object($result);
 
echo $object->maxvalue;

Re: Extracting data from sql using max(column)

Posted: Mon Oct 13, 2008 10:45 am
by Grahamhart
it is returning 1 row, which is correct as it is only selecting the highest value in the column.

making it into an object still returned blank.

Re: Extracting data from sql using max(column)

Posted: Mon Oct 13, 2008 10:45 am
by onion2k
MAX(`mycolumn`) will be returned as a 'column' called "MAX(`mycolumn`)" if you don't alias it using AS.

Code: Select all

SELECT MAX(`mycolumn`) AS mycolumn FROM `mytable` GROUP BY `mycolumn`
I've also added a GROUP BY so MySQL knows what to gather up and find the maximum value of. It's not strictly necessary if you're only fetching one column because MySQL will group by that column in order to find the MAX() value, but if you added other columns to the query it'd break so it's good practise to always use GROUP BY when you use a grouping function like MAX, MIN, COUNT, SUM, AVG, etc.

Re: Extracting data from sql using max(column)

Posted: Mon Oct 13, 2008 10:52 am
by Grahamhart
works great thanks