Extracting data from sql using max(column)

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
Grahamhart
Forum Commoner
Posts: 27
Joined: Wed Jun 11, 2008 6:05 am

Extracting data from sql using max(column)

Post 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?
Last edited by Grahamhart on Mon Oct 13, 2008 10:07 am, edited 1 time in total.
User avatar
aceconcepts
DevNet Resident
Posts: 1424
Joined: Mon Feb 06, 2006 11:26 am
Location: London

Re: Extracting data from sql using max(column)

Post 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...
Grahamhart
Forum Commoner
Posts: 27
Joined: Wed Jun 11, 2008 6:05 am

Re: Extracting data from sql using max(column)

Post 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
User avatar
aceconcepts
DevNet Resident
Posts: 1424
Joined: Mon Feb 06, 2006 11:26 am
Location: London

Re: Extracting data from sql using max(column)

Post by aceconcepts »

Oh ok.

Try using mysql_fetch_array()
Grahamhart
Forum Commoner
Posts: 27
Joined: Wed Jun 11, 2008 6:05 am

Re: Extracting data from sql using max(column)

Post 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.
User avatar
aceconcepts
DevNet Resident
Posts: 1424
Joined: Mon Feb 06, 2006 11:26 am
Location: London

Re: Extracting data from sql using max(column)

Post 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'];
 
Grahamhart
Forum Commoner
Posts: 27
Joined: Wed Jun 11, 2008 6:05 am

Re: Extracting data from sql using max(column)

Post 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:
User avatar
aceconcepts
DevNet Resident
Posts: 1424
Joined: Mon Feb 06, 2006 11:26 am
Location: London

Re: Extracting data from sql using max(column)

Post 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());
Grahamhart
Forum Commoner
Posts: 27
Joined: Wed Jun 11, 2008 6:05 am

Re: Extracting data from sql using max(column)

Post 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.
User avatar
aceconcepts
DevNet Resident
Posts: 1424
Joined: Mon Feb 06, 2006 11:26 am
Location: London

Re: Extracting data from sql using max(column)

Post by aceconcepts »

Lets see if your actually getting any rows returned. Use the following:

Code: Select all

 
echo mysql_num_rows($result);
 
User avatar
dhrosti
Forum Commoner
Posts: 90
Joined: Wed Jan 10, 2007 5:01 am
Location: Leeds, UK

Re: Extracting data from sql using max(column)

Post 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;
Grahamhart
Forum Commoner
Posts: 27
Joined: Wed Jun 11, 2008 6:05 am

Re: Extracting data from sql using max(column)

Post 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.
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Re: Extracting data from sql using max(column)

Post 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.
Grahamhart
Forum Commoner
Posts: 27
Joined: Wed Jun 11, 2008 6:05 am

Re: Extracting data from sql using max(column)

Post by Grahamhart »

works great thanks
Post Reply