Return MAX value from array

[macman1]

Hello all -

I need to return the county with the greatest score with the following long query:

SELECT county, SUM(score) AS score FROM `students` WHERE county = 'Blackstone Valley' GROUP BY county UNION SELECT county, SUM(score) AS score FROM `students` WHERE county = 'South County' GROUP BY county UNION SELECT county, SUM(score) AS score FROM `students` WHERE county = 'West Bay' GROUP BY county UNION SELECT county, SUM(score) AS score FROM `students` WHERE county = 'Northwest' GROUP BY county UNION SELECT county, SUM(score) AS score FROM `students` WHERE county = 'East Bay' GROUP BY county UNION SELECT county, SUM(score) AS score FROM `students` WHERE county = 'Providence' GROUP BY county UNION SELECT county, SUM(score) AS score FROM `students` WHERE county = 'Southeastern Massachusetts' GROUP BY county

This returns something that looks like this:

county ¦ score
--------------------------
Blackstone Valley ¦ 5
South County ¦ 17
West Bay ¦ 19
Northwest ¦ 25
East Bay ¦ 4
Providence ¦ 8

I need some php code that will return "Northwest" because score is 25. How would I do this?

Thanks

[joelgreen]

$max_value = $array_name[0];
$index = 0;
foreach ($array_name as $key => $item) {
// $item[0] - county, $item[1] - score
if ( $item[1] > $max_value ) {
$max_value = $item[1];
$index = $key;
}
}

echo 'County ' . $array_name[$index] . ' Max value = '. $max_value;

[macman1]

Thanks Joelgreen but here is stupid question. How do I attach my query to the code you provided? Should I do something like this: $array_name = mysql_fetch_array($result)?

I know this is basic but I am lost.
Thanks...

[borntobeweb]

Hi macman, here's another possible solution:

$qh = mysql_query("SELECT county, SUM(score) AS score FROM `students` WHERE county = 'Blackstone Valley' or county = 'South County' or county = 'West Bay' or county = 'Northwest' or county = 'East Bay' or county = 'Providence' or county = 'Southeastern Massachusetts' GROUP BY score ORDER BY score DESC"); // Order by clause returns rows with highest scores first.

$row = mysql_fetch_array($qh); // Fetch first row, i.e. the one with highest score.

$county_with_greatest_score = $row['county'];

You should also be able to replace all the conditions by a single "WHERE county IN (...)" clause but for some reason I kept getting mix charset errors in phpmyadmin when I tried it.

[joelgreen]

It depends on whether you have array already generated or not. If you have array already generated then above code should help, otherwise put this code just after myqsl_query statement

$max_value = false;
$county = '';
while($row = mysql_fetch_assoc()) {
if ( ($max_value === false) ¦¦ ($row['score'] > $max_value) ) {
$max_value = $row['score'];
$county = $row['county'];
}
}

echo 'County ' . $county . ' Max value = '. $max_value;

[joelgreen]

borntobeweb's gave a better solution if it works for you.

[macman1]

Hi Joelgreen,

When using your code, I get the following error.

Warning: Wrong parameter count for mysql_fetch_assoc() in "location" on line 236
County Max value =

The simpler solution above doesn't return correct sums for some reason. I don't see how it is possibly getting the information it says it has.

Thanks again, Steve

[joelgreen]

you have to pass resource id from mysql_query to mysql_fetch_assoc

$resourceid = mysql_query('here goes your query.....

$max_value = false;
$county = '';
while($row = mysql_fetch_assoc($resourceid)) {
if ( ($max_value === false) ¦¦ ($row['score'] > $max_value) ) {
$max_value = $row['score'];
$county = $row['county'];
}
}