Hello all,
i have a table that is grouped by bowtype, gender and then name.. and shows the sum of scores for an archery league. however this groups all the results (rows) into one table and this looks untidy. what i would like is either to display each group by (bowtype & gender) as seperate tables or to have a blank row displayed whenever these groups change.. is this possible?
i know i could have several select statements and several tables.. but with the groupings there a possible 20 tables, and some may remain empty.. so having it created on the fly would be better.
hopefully someone can point me in the right direction.. im pretty new to php..
in the old sqlplus you could use break on field skip n (where n is number of lines) but this isn't valid for mysql.. but something similiar would do...
many thanks
my code currently.
mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="SELECT bowtype, gender, count(*) as pld, name, sum(score) as ttlscore, sum(maximum),round(sum(score)/sum(maximum),3) handicap, sum(hits) as ttlhits, sum(golds) as ttlgolds, sum(inners), sum(petticoats), sum(points) as ttlpoints FROM scores group by bowtype, gender, name order by bowtype, gender, ttlpoints desc, ttlscore desc, ttlhits desc, ttlgolds desc, inners, petticoats";
$result=mysql_query($query);
$num=mysql_numrows($result);
mysql_close();
echo "<b><center>Challenge Cup Table</center></b><br><br>";
?>
<center>
<table border="1" cellspacing="2" cellpadding="2" class="league">
<th><font face="Verdana">bowtype</font></th>
<th><font face="Verdana">gender</font></th>
<th width="200"><font face="Verdana">Name</font></th>
<th><font face="Verdana">pld</font></th>
<th><font face="Verdana">Score</font></th>
<th><font face="Verdana">Max</font></th>
<th><font face="Verdana">H/C</font></th>
<th><font face="Verdana">Hits</font></th>
<th><font face="Verdana">G</font></th>
<th><font face="Verdana">I</font></th>
<th><font face="Verdana">P</font></th>
<th><font face="Verdana">Pts</font></th>
</tr>
<?
$i=0;
while ($i < $num) {
$bowtype=mysql_result($result,$i,"bowtype");
$gender=mysql_result($result,$i,"gender");
$pld=mysql_result($result,$i,"pld");
$name=mysql_result($result,$i,"name");
$score=mysql_result($result,$i,"ttlscore");
$maximum=mysql_result($result,$i,"sum(maximum)");
$handicap=mysql_result($result,$i,"handicap");
$hits=mysql_result($result,$i,"ttlhits");
$golds=mysql_result($result,$i,"ttlgolds");
$inners=mysql_result($result,$i,"sum(inners)");
$petticoats=mysql_result($result,$i,"sum(petticoats)");
$points=mysql_result($result,$i,"ttlpoints");
?>
<!---->
<tr>
<td><font face="Verdana"><? echo $bowtype; ?></font></td>
<td><font face="Verdana"><? echo $gender; ?></font></td>
<td width="200"><font face="Verdana"><a href=modules.php?name=Archer&uname=<? echo $name;?>><? echo $name; ?></a></font></td>
<td><font face="Verdana"><? echo $pld; ?></font></td>
<td><font face="Verdana"><? echo $score; ?></font></td>
<td><font face="Verdana"><? echo $maximum; ?></font></td>
<td><font face="Verdana"><? echo $handicap; ?></font></td>
<td><font face="Verdana"><? echo $hits; ?></font></td>
<td><font face="Verdana"><? echo $golds; ?></font></td>
<td><font face="Verdana"><? echo $inners; ?></font></td>
<td><font face="Verdana"><? echo $petticoats; ?></font></td>
<td><font face="Verdana"><? echo $points; ?></font></td>
</tr>
<?
$i++;
}
echo "</table></center>";
?>
