How to retrieve the highest score from a set of array values of a 2D array.

[terungwa]

I have mysql select query shown below retrieving data from a database:

$sql = "
SELECT 
	student, sum( test_score ) as score
FROM 
	test
INNER JOIN 
	level 
ON 
	test.level_id = level.level_id
GROUP BY 
	student"
$result = $mysqli->query($sql, MYSQLI_STORE_RESULT);
if($result)
{
      while ($row = $result->fetch_array(MYSQLI_ASSOC))
       {
	    var_dump($row);
       }
}

On inspection of the retrieved array with var_dump(), i have these result below:

array (size=2)
  'John' => string 'English' (length=2)
  'Score' => string '20' (length=3)

array (size=2)
  'Mary' => string 'Math' (length=3)
  'Score' => string '35' (length=3)

array (size=2)
  'Samuel' => string 'Physics' (length=3)
  'Score' => string '5' (length=3)

 How do I get at and print out the highest score from this set of array values within this context.
 I tried this echo max($row['count']) but i understandably got this feedback:
Warning: max(): When only one parameter is given, it must be an array

 

Thanks.

[mac_gyver]

add ORDER BY score DESC to the query to get the rows in the result set ordered from highest to lowest score and if you only want the one row with the highest score, add LIMIT 1 to the end of that on the query.

[terungwa]

add ORDER BY score DESC to the query to get the rows in the result set ordered from highest to lowest score and if you only want the one row with the highest score, add LIMIT 1 to the end of that on the query.

Thanks mac_gyver for your response, but that is not what i need. I am retrieving all elements from the result set in a table. What I need in addition to the table is to write out a small summary stating that the student with the highest score is ......................................... So my task is to get at and extract the highest array value for this purpose.

 

[mac_gyver]

moving thread to the php help forum section as that's not a mysql question.

[mac_gyver]

assuming that the data in the array isn't already sorted as needed, the most general purpose way of ordering/finding values in a multiple dimensional array is to use usort() and write a function that orders the data the way you want.

[Jacques1]

Since terungwa just wants the highest score, there's no need for sorting the array. A simple loop is enough:

<?php

$student_scores = array(
	array(
	  'student' => 'John',
	  'score' => '20'
	),
	array(
	  'student' => 'Mary',
	  'score' => '35'
	),
	array(
	  'student' => 'Samuel',
	  'score' => '5'
	),
);

$max_score = null;
foreach ($student_scores as $student_score)
{
	if (is_null($max_score) || $student_score['score'] > $max_score)
	{
		$max_score = $student_score['score'];
	}
}

If the summary comes after the table, you can even do this in the main loop.

 

If you want “the” student with the highest score, you first need to define an additional unique criterion in case multiple students have the same score.