ORDER BY variable/array within a foreach loop

PHP
1

I need to order the mysql query by total score which is stored in a variable/array. I understand a foreach loop is basically performing the query for each result, so ORDER BY won’t work. How can I order the query results by score?


<?
if (isset($_POST['submit'])) {
		
	    $total = array();
		
	       foreach($_POST["category"] as $key => $value):

	           if(!array_key_exists($value,$total)): $total[$value] = ""; endif;
	           $total[$value] += $_POST["answer"][$key];

	       endforeach;

              $i = 1;
	
	       foreach($total as $cat => $score): 		
			
			   $getData = mysql_query("SELECT * FROM lt_category WHERE cat_id = '$cat' ORDER BY {$score} DESC");
			
			   if($getData === FALSE) {
			       die(mysql_error());
			   }
			
			   else {
			
			   	while ($result = mysql_fetch_array($getData)) {  ?>
					
					
					<div class="result <? echo $result['name']; ?>">
						
						<span class="category"> <? echo  $i . "."  . $result['name'] . "."  . $score; ?> </span>
						
						<span class="desc"> <? echo $result['desc']; ?>  </span><!-- description of category -->
						
						</div>

This code does not return any errors, but it doesn’t order by score either.

2

Don’t use short tags.e.g. <? Use full tags. <?php
Move away from mysql queries. PDO is a good option.
Avoid making queries in loops.

OK with that said, have you tried using arsort() on your $total array?

			arsort($total);
           foreach($total as $cat => $score):

               $getData = mysql_query("SELECT * FROM lt_category WHERE cat_id = '$cat'");
3

I’m not sure how many questions or categories you are dealing with but you should avoid looped queries when you can. Something like this might be an option.

&lt;?php
if (isset($_POST['submit'])){

	$data = array();
	//desc is a reserved word in php.  Use back ticks.
	$sql = "SELECT cat_id, name, `desc` FROM lt_category";
	$result = mysql_query($sql) or die(mysql_error());
	while ($row = mysql_fetch_array($result)) {
		$data[$row['name']]['name'] = $row['name'];
		$data[$row['name']]['desc'] = $row['desc'];
	}
	
	$total = array();
	
	foreach($_POST["category"] as $key =&gt; $value):
	
		if(!array_key_exists($value,$total)): $total[$value] = ""; endif;
		$total[$value] += $_POST["answer"][$key];
	
	endforeach;
	
	$i = 1;
	arsort($total);
	foreach($total as $cat =&gt; $score):
?&gt;
	
	
		&lt;div class="result &lt;?php echo $data[$cat]['name']; ?&gt;"&gt;
			
			&lt;span class="category"&gt; &lt;?php echo  $i . "."  . $data[$cat]['name'] . "."  . $score; ?&gt; &lt;/span&gt;
			
			&lt;span class="desc"&gt; &lt;?php $data[$cat]['desc']; ?&gt;  &lt;/span&gt;&lt;!-- description of category --&gt;
		
		&lt;/div&gt;	
	
&lt;?php
		$i++;			
	endforeach;
	
}	
?&gt;
4

Thanks Drummin.

<? arsort($total);  ?>

did the trick.

Can you tell me why I should move from mysql queries to PDO? I haven’t done anything from scratch with PHP for a long time, so I’m out
of the loop with the latest.

5

There’s more detail here: http://www.sitepoint.com/forums/showthread.php?1182768-STOP-using-mysql-migrating-to-procedural-mysqli

6