[SOLVED] Help with displaying data.

[Siggles]

I have some code that should grab all predicitions from the database. When the fixture for the prediction changes it should start a new DIV and display the predictions under a new heading. I got this working once but now for the life of me cannot get it working. The page is at http://www.independentmillwall.com/prediction/pbygame.php and there are predictions for a second game. Please help.

 

$result = mysql_query("SELECT predictions.resultmfc, predictions.resultother, predictions.score, fixtures.opponent, fixtures.resultus, fixtures.resultthem, fixtures.homeaway, predictions.id, predictions.username, DATE_format( fixtures.dateplayed, '%M %e, %Y' ) AS newdate, predictions.predictionid, SUM( predictions.score ) AS Total_Score FROM fixtures RIGHT JOIN predictions ON predictions.id = fixtures.id GROUP BY predictions.username ORDER BY dateplayed, username");
$i=1;
while($row = mysql_fetch_array($result))
		{
		$divname = $row['predictionid'];
		$opponent = $row['opponent'];
		$username=$row['username'];
		$homeaway=$row['homeaway'];
	   $resultmfc=$row['resultmfc'];
		$resultother=$row['resultother'];
		$resultus=$row['resultus'];
		$resultthem=$row['resultthem'];
		$score=$row['score'];
		$total=$row['Total_Score'];
		$newdate=$row['newdate'];

		 if ($opponent != $lastopponent) {
		echo "<br>";
		?>
		</div><br><div class="eg-bar""><span id="faq<? echo $i; ?>-title" class="iconspan"><img src="images/collapse.gif" width="10" height="10"></span> <span class="heading1"><? echo "$opponent ($homeaway) - $newdate"; ?></span></div>
<div id="faq<? echo $i; ?>" class="icongroup1"><br>
<table class="border" width="75%" cellpadding="4" cellspacing="0">
		<tr style="background:#DDDDDD;"><td width="52%"><u>Username</u></td><td width="16%"><u>Prediction</u></td><td width="16%"><u>Result</u></td><td width="16%"><u>Points</u></td></tr>	</table> 
		<?
		}
		?>

		<table width="75%"><tr valign="top" style="background: <? echo "$tb"; ?> ">
<td width="52%" class="style1" ><? echo "$username"; ?></td>
<td align="center" width="16%" class="style1" ><? echo "$resultmfc"; ?>-<? echo "$resultother"; ?></td>
<td align="center" width="16%" class="style1" ><? echo "$resultus"; ?>-<? echo "$resultthem"; ?></td>
<td align="center" width="16%" class="style1" ><? echo "$score"; ?></td>
</tr>

	<?
	echo "</table>";

	$lastopponent=$opponent;
	  	$i++;
	  	}

[GingerRobot]

The basic idea:

 

$sql ="SELECT field1,field2,field3 FROM yourtable ORDER BY field1";
$result = mysql_query($sql) or trigger_error(mysql_error(),E_USER_ERROR);
$prev = '';
while($row = mysql_fetch_assoc($result)){
if($row['field1'] != $prev){//the value has changed since last time the loop ran
	echo $row['field1'].'<br />';
	$prev = $row['field1'];
}
echo $row['field2'].$row['field3'].'<br />';
}

[Siggles]

I think the lastopponent varaible works. It is the group by clause that doesn't work. Because I have it GROUP BY username, I think it only shows the username once and that is why it does not show the second predictions. If I change it to group by fixtures, it shows one fixture for Oldham and Southend games.

 

in other words, if I change it to GROUP BY fixtures the page works but only shows one prediction underneath each heading. So what GROUP BY to use, that is the question. I need one though as the SQL statement errors without it cos of the SUM.

[GingerRobot]

That is exactly what a GROUP BY clause does. If your query is not returning the required information, this would be better off in the mysql section, along with more details about your table structure and what you are trying to achieve.

[Siggles]

That is exactly what a GROUP BY clause does. If your query is not returning the required information, this would be better off in the mysql section, along with more details about your table structure and what you are trying to achieve.

 

I think you are right, it is a SQL query prob and so I will post there. The page works without the SUM and GROUP BY but it means I cannot show people's score for their predictions.

 

http://www.independentmillwall.com/prediction/pbygame.php