PHP + MySql ordering

[doltmann]

Ok, i have no idea on how im going to do this, so maybie someone could help....

I need to pull this out of the Database:
$result = mysql_query("SELECT DISTINCT stats_rate.page FROM stats_rate GROUP BY $colomn $order",$db);

Then I need to average them using this:
$res3 = mysql_query("SELECT AVG(stats_rate.overall) FROM stats_rate WHERE page = '$Dispage'");

THEN I need to be able to order them in either DESC or ASC...

The problem is that there is other data in the same row that needs to be displayed on the same row as the "Orderd Averages"

Here is the data structure and a sample of the DATA input:

#

Table structure for table stats_rate

#

CREATE TABLE stats_rate (
id int(11) NOT NULL auto_increment,
page text NOT NULL,
content int(11) NOT NULL default '0',
design int(11) NOT NULL default '0',
navigation int(11) NOT NULL default '0',
overall int(11) NOT NULL default '0',
date text NOT NULL,
PRIMARY KEY (id)
) TYPE=MyISAM;

#

Dumping data for table stats_rate

#

INSERT INTO stats_rate VALUES (307, 'test4.php', 3, 4, 2, 2, '8/28/2003');

Im THINKING that the best possible way to do that is to insert the Averaged data into some sort of array with the Average in it and another "column" with either the rest of the row information or an identifier tag of some sort in it that could get the info out of a varible... (but even still, dont know how i would go about that one... The script pretty much wont allow it...)

Here is a snippet of code where I THINK it would go:

while($myrow = mysql_fetch_array($result)) {

$Dispage = $myrow["page"];
 	$res1 = mysql_query("SELECT COUNT(stats_rate.page) FROM stats_rate WHERE page = '$Dispage'");
	$res2 = mysql_query("SELECT MAX(stats_rate.date) FROM stats_rate WHERE page = '$Dispage'");
	$res3 = mysql_query("SELECT AVG(stats_rate.overall) FROM stats_rate WHERE page = '$Dispage'");	

	if($res1 != nil || $res2 != nil || $res3 != nil){	
	$disCount = mysql_fetch_array($res1);
	$lastDate = mysql_fetch_array($res2);
	$overall = mysql_fetch_array($res3);
	}else{
	echo "Error!";
	}

		$peices = explode(".",$overall[0]);
		$peices[1] = substr($peices[1],0,2);
		if($peices[1] == ""){
		$overall[0] = $peices[0];
		}else{
		$overall[0] = $peices[0] . "." . $peices[1];
		}


		if($overall[0] < 2.50){
			$bgColor = "#FF0000";
			echo "<tr bgcolor=$bgColor>
					<td width='18'><a href='javascript:void(window.open(\"help/help.php?message=alert\", \"Help\", \"width=400,height=200,left=200,top=100, location=no, menubar=no, status=no, toolbar=no, scrollbars=no, resizable=yes\"))'><img src='images/alert.gif' border='0'></a></td>
					<td width='18'><a href='javascript:void(deletePres())'><img src='images/trash.gif' border='0'></a></td>						
					<td width='116'><div align='center'><a href='$PHP_SELF?page=$Dispage'>$Dispage</a></div></td>
					<td width='183'><div align='center'>$disCount[0]</div></td>
					<td width='210'><div align='center'>$overall[0]</div></td>
					<td width='226'><div align='center'>$lastDate[0]</div></td>
					</tr>";
		}else{				
			if($bgColor == "#0099FF"){
				$bgColor = "#00CCFF";
			} else {
				$bgColor = "#0099FF";
			}
			echo "<tr bgcolor=$bgColor>
					<td width='18'></td>
					<td width='18'><a href='javascript:void(deletePres())'><img src='images/trash.gif' border='0'></a></td>
					<td width='116'><div align='center'><a href='$PHP_SELF?page=$Dispage'>$Dispage</a></div></td>
					<td width='183'><div align='center'>$disCount[0]</div></td>
					<td width='210'><div align='center'>$overall[0]</div></td>
					<td width='226'><div align='center'>$lastDate[0]</div></td>
					</tr>";
		}

}[/COLOR]

You can take a look at the app that its going to be used on here:
(Youll see what im talking about)
Rate_Page_Test.php

Let me know if you need more info.

[sarahk]

I would have thought you could do the lot with

<script language='Javascript'>
function popAlert()
{
  window.open("help/help.php?message=alert", "Help", "width=400,height=200,left=200,top=100, location=no, menubar=no, status=no, toolbar=no, scrollbars=no, resizable=yes");
}
</script>
<?php
$sql = "SELECT `page`, COUNT(`page`) as cntpage, MAX(`date`) as maxdate,  AVG(`overall`) as avgoverall FROM `stats_rate` group by `page`"; 

$result = mysql_query($sql);

while($myrow = mysql_fetch_array($result)) 
{
  $dispage = $myrow['page'];
  $disCount = $myrow['cntpage']; 
  $lastDate = $myrow['maxdate']; 
  $overall = $myrow['avgoverall']; 

  if($overall < 2.50)
  { 
    $bgColor = "#FF0000"; 
    echo "<tr bgcolor={$bgColor}> 
    <td width='18'><a href='javascript:void(popAlert())'><img src='images/alert.gif' border='0'></a></td>"; 
  }
  else
  { 
    if($bgColor == "#0099FF")  $bgColor = "#00CCFF"; 
    else $bgColor = "#0099FF"; 

echo "<tr bgcolor={$bgColor}>
<td width='18'></td>";
  } 
  echo "<td width='18'><a href='javascript:void(deletePres())'><img src='images/trash.gif' border='0'></a></td>
    <td width='116' align='center'><a href='{$PHP_SELF}?page={$Dispage}'>{$Dispage}</a></td>
    <td width='183' align='center'>{$disCount}</td> 
    <td width='210' align='center'>{$overall}</td> 
    <td width='226' align='center'>{$lastDate}</td> 
    </tr>"; 
} 
?>

Now you can add the other info in there but it will probably get the value from the first record.

i'll leave you to test the query and see if it works for you. Would be much simpler.

I've also moved some of the code around to make it more maintainable.

I couldn't get what you were doing with $pieces. Were you trying to limit to 2 decimal places? try

    $overall = number_format( $myrow['avgoverall'], 2);