expanding html table with rows and columns

[alwaysconfused]

Hi,

I'm trying to build a table which shows the results from a series of races. At the moment I can get it to show the total results for the series for each competitor. I'm stuck where it comes to getting the table to expand the number of columns to show each race number and then shows the individual results in the rows below.

Here's the table code as is.....

      <table border="1" align="center">
      <tr>
        <td align="right" valign="middle">Series:</td>
        <td><?php echo $row_seriesresults['series']; ?> </td>
        </tr>
        <tr>
        <td align="right">Races:</td>
        <td><?php echo $row_seriesresults['races']; ?> </td>
        </tr>
        <tr align="center" valign="middle">
        <td>Helm</td>
        <td>Crew</td>
        <td>Sail Number</td>
        <td>Total Points</td>
        <td>Position</td>
        <td><?php echo $row_seriesresults['rnumber']; ?></td> //this is the column I want to expand. I've tried moving it into the loop but it didn't do as planned. I've also tried putting it into it's own while loop but that just killed the table (page shown but no table).
</tr>

  <?php do { $i++;?>    
      <tr>
      <td><?php echo $row_seriesresults['serieshelm']; ?>&nbsp; </td>
      <td><?php echo $row_seriesresults['seriescrew']; ?>&nbsp; </td>
      <td><?php echo $row_seriesresults['seriessailnumb']; ?>&nbsp; </td>
      <td> <?php echo $row_seriesresults['totalpoints']; ?>&nbsp; </td>
      <td><? echo $i;?></td>
      <td><?php echo $row_seriesresults['totalpoints']; ?></td>
    </tr>
    <?php } while ($row_seriesresults = mysql_fetch_assoc($seriesresults)); ?>
  </table>

Any suggestions of how to get this to go??

Thanks
Dave

[dylanmhulderman]

Can you post a copy of your MySQL table format? So I can see a list of all of the field names, field types and so forth?

[alwaysconfused]

Thanks for the reply.

The main table I'm pulling the info from is tbl results code....

CREATE TABLE `tblresults` (
	`resultid` INT(15) NOT NULL AUTO_INCREMENT,
	`race_id` INT(10) NOT NULL DEFAULT '0',
	`boatcrewid` INT(10) NOT NULL DEFAULT '0',
	`hours` INT(10) NOT NULL DEFAULT '0',
	`mins` INT(10) NOT NULL DEFAULT '0',
	`secs` INT(10) NOT NULL DEFAULT '0',
	`laps` INT(11) NOT NULL DEFAULT '0',
	`reason` VARCHAR(50) NULL DEFAULT '0',
	`corrected` FLOAT NOT NULL,
	`seriesid` INT(10) NULL DEFAULT NULL,
	`points` INT(10) NULL DEFAULT NULL,
	PRIMARY KEY (`resultid`),
	INDEX `racekey` (`race_id`),
	INDEX `crewkey` (`boatcrewid`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
ROW_FORMAT=DEFAULT
AUTO_INCREMENT=4403

here's the query i'm trying to build the display table from on the page - the info is dragged in from a number of tables. My issue is trying to get the columns to expand to the right number of races (i.e. the occurrences of raceid per series id in the table above). Please note, the query below doesn't quite match the display code from earlier as I've been fiddling with it all day and have re-written it a few times!!.

$serieswanted_seriesresults1 = "-1";
if (isset($_POST['serieschoose'])) {
  $serieswanted_seriesresults1 = $_POST['serieschoose'];
}
mysql_select_db($database_sailingdb, $sailingdb);
$query_seriesresults1 = sprintf("SELECT tblresults.race_id AS rid, tblresults.boatcrewid AS bcid, tblresults.reason AS rsn, tblresults.corrected AS crtd, tblresults.seriesid AS sid, tblresults.points AS points, tblseries.series AS series, tblraces.race_number AS rnumb, tblcompetitors.racecrewid AS rcid, tblcompetitors.helm AS hlm, tblcompetitors.crew AS crw, tblcompetitors.boat_id AS btid, tblcompetitors.sailnumber AS snumb, tblm1.name AS helm, tblm2.name AS crew, tblboats.boat_type AS boat FROM tblresults LEFT JOIN tblseries ON tblresults.seriesid = tblseries.seriesid LEFT JOIN tblraces ON tblresults.race_id = tblraces.raceid LEFT JOIN tblcompetitors ON tblcompetitors.racecrewid = tblresults.boatcrewid LEFT JOIN tblmember AS tblm1 ON tblm1.memberid = tblcompetitors.helm LEFT JOIN tblmember AS tblm2 ON tblm2.memberid = tblcompetitors.crew LEFT JOIN tblboats ON tblcompetitors.boat_id = tblboats.boatid WHERE tblresults.seriesid = %s", GetSQLValueString($serieswanted_seriesresults1, "int"));
$seriesresults1 = mysql_query($query_seriesresults1, $sailingdb) or die(mysql_error());
$row_seriesresults1 = mysql_fetch_assoc($seriesresults1);
$totalRows_seriesresults1 = mysql_num_rows($seriesresults1);

[dylanmhulderman]

Okay, I think I know exactly what you're talking about. The fix is really easy, it's going to be a one line code... something like this:

<?php for($a = 0;$a < count($whatever);$a++) { } ?>

The last thing I need to know before I tell you exactly what you need to do is for you to post an example of what the table would look like had everything worked out properly... That way I'll know exactly what you're trying to get the script to do.

[alwaysconfused]

Cheers, I was heading down the for loop path earlier but struggled to get the values in the right places.

Here's a quick mock up

<table align="center" border= "1">
<tr><td colspan = "4">&nbsp</td><td>Races</td></tr>
<tr>								
<td>Helm	</td>
<td>Crew</td>
<td>Boat</td>
<td>Sailnumber</td>
<td>1</td><td>2</td><td>3</td><td>4</td><td>Total</td></tr>
<tr>
<td>Mr</td><td>Mrs</td><td>sinker</td><td>12345</td><td>2</td><td>1</td><td>5</td><td>3</td><td>11</td><td></tr>
</table>

so roughly it will look like (sorry can't get it all to line up).......

Competitors Races
Helm Crew Boat Sailnumber 1 2 3 Total
Mr Mrs sinker 12345 1 5 3 11
swim faster sunk 6789 2 dnc 4 12

The trouble I'm having is getting both the competitors (helm, crew, boat and sail number columns) to expand down for the correct number of entrants whilst at the same time the race columns expand outwards to echo the result for each race - no two series have the same number of races so unfortunately it's not possible just to hard code the columns in. The final column after the race columns is the total number of points per competitor which is the easy bit!!

Thanks again for your help.

[dylanmhulderman]

Okay I understand everything you mean... but where in the table does it specify the races for each INSERT row that is going to have a different number of values for each INSERT row? You know what I mean? Like which variable name in your MySQL table tblresults is the one that is going to have a different number of entries per each user?

[alwaysconfused]

here's the full table code at the mo

<table border="1" align="center">
<!--  <tr>
  <td>&nbsp;</td>
  <td colspan="<?php //echo $i;?>">Race Number</td></tr>-->
  <tr>


<td>helm</td>
<td>crew</td>
<td>boat</td>
  [code=php]  <?php for ($b=1;$b<=$i;$b++) { echo "<td>".$b."</td>"; } 

//these are the race numbers which expand (the foreach below is an alternate method I tried)

//foreach($potentially as $k => $val) { echo "<td>".$k ."</td>"; }  ?>

<td>rsn</td>
<td>points</td>
</tr>
<?php do { ?>
<tr>

  <td>[code=php]<?php echo $row_seriesresults1['helm']; ?>[/code]&nbsp; </td>
  <td>[code=php]<?php echo $row_seriesresults1['crew']; ?>[/code]&nbsp; </td>
  <td>[code=php]<?php echo $row_seriesresults1['boat']; ?>[/code]&nbsp; </td>

<!--And this is the group of corresponding points values which needs to match both the race numbers from the column headers above and the competitors to the left-->

 [code=php] <?php $pppp = array(); $pppp[] = ($row_seriesresults['points'];)

foreach ( $pppp as $k => $val) { echo "<td>".$val ."</td>"; } ?>[/code]&nbsp;

  <td>[code=php]<?php echo $row_seriesresults1['rsn']; ?>[/code]&nbsp; </td>
  <td>[code=php]<?php echo $row_seriesresults1['points']; ?>[/code]&nbsp; </td>
</tr>
[code=php]<?php } while ($row_seriesresults1 = mysql_fetch_assoc($seriesresults1)); ?>[/code]

</table>[/code]

Thanks again for your help.