Extracting data from sql using max(column)
Moderator: General Moderators
-
Grahamhart
- Forum Commoner
- Posts: 27
- Joined: Wed Jun 11, 2008 6:05 am
Extracting data from sql using max(column)
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?
$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.
- aceconcepts
- DevNet Resident
- Posts: 1424
- Joined: Mon Feb 06, 2006 11:26 am
- Location: London
Re: Extracting data from sql using max(column)
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)
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
- aceconcepts
- DevNet Resident
- Posts: 1424
- Joined: Mon Feb 06, 2006 11:26 am
- Location: London
Re: Extracting data from sql using max(column)
Oh ok.
Try using mysql_fetch_array()
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)
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.
- aceconcepts
- DevNet Resident
- Posts: 1424
- Joined: Mon Feb 06, 2006 11:26 am
- Location: London
Re: Extracting data from sql using max(column)
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)
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 
- aceconcepts
- DevNet Resident
- Posts: 1424
- Joined: Mon Feb 06, 2006 11:26 am
- Location: London
Re: Extracting data from sql using max(column)
Okey dokey,
use mysql_error() to check for sql errors:
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)
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.
- aceconcepts
- DevNet Resident
- Posts: 1424
- Joined: Mon Feb 06, 2006 11:26 am
- Location: London
Re: Extracting data from sql using max(column)
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)
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)
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.
making it into an object still returned blank.
Re: Extracting data from sql using max(column)
MAX(`mycolumn`) will be returned as a 'column' called "MAX(`mycolumn`)" if you don't alias it using AS.
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.
Code: Select all
SELECT MAX(`mycolumn`) AS mycolumn FROM `mytable` GROUP BY `mycolumn`-
Grahamhart
- Forum Commoner
- Posts: 27
- Joined: Wed Jun 11, 2008 6:05 am
Re: Extracting data from sql using max(column)
works great thanks