Creating html tables with MySQL results

Anon

I am creating a new website for our local tenpin bowling league and am
keeping everyone's scores in a MySQL database. Each player has their own
table within the database and their own page. On the page, I want to create
a table displaying all results which the bowler has bowled.

The MySQL tables look like this:

mysql> select * from jfold order by date;
+------------+------+------+------+-------+-------+-------+--------+
| Date | Rnd | Gms | Hcap | Game1 | Game2 | Game3 | Series|
+------------+------+------+------+-------+-------+-------+--------+
| 2001-09-16 | 1 | 3 | 0 | 182 | 228 | 206
| 616 |
| 2001-09-23 | 2 | 3 | 0 | 157 | 198 | 184
| 539 |
| 2001-09-30 | 3 | 3 | 6 | 161 | 148 | 178
| 487 |
| 2001-10-07 | 4 | 3 | 13 | 126 | 166 | 186
| 478 |
| 2001-10-14 | 5 | 3 | 18 | 124 | 167 | 127
| 418 |
| 2001-10-21 | 6 | 3 | 30 | 176 | 159 | 137
| 472 |
| 2001-10-28 | 7 | 3 | 34 | 118 | 145 | 193
| 456 |
| 2001-11-04 | 8 | 3 | 36 | 163 | 158 | 117
| 438 |
| 2001-11-18 | 9 | 3 | 39 | 223 | 202 | 180
| 605 |
| 2001-11-25 | 10 | 3 | 27 | 148 | 185 | 173 |
506 |
| 2001-12-02 | 11 | 3 | 24 | 221 | 140 | 160 |
521 |
| 2001-12-09 | 12 | 3 | 21 | 168 | 214 | 194 |
576 |
+------------+------+------+------+-------+-------+-------+--------+
12 rows in set (0.00 sec)

Now, I want to produce something similar in a html table on the webpage. I
want all the results etc in colums.

Here is the current SQL code i'm using:

<?php

$db_name = "dbase";

$table_name = "jfold";

$connection = mysql_connect("localhost", "user", "pass") or
die(mysql_error());

$db = mysql_select_db($db_name, $connection);

$sql = "SELECT * FROM $table_name ORDER BY Date";

$result = mysql_query($sql,$connection) or die(mysql_error());

while ($row = mysql_fetch_array($result)) {
$Date = $row['Date'];
$Rnd = $row['Rnd'];
$Gms = $row['Gms'];
$Hcap = $row['Hcap'];
$Game1 = $row['Game1'];
$Game2 = $row['Game2'];
$Game3 = $row['Game3'];
$Series = $row['Series'];

$display_block .= "
$Date - $Rnd - $Hcap - $Game1 - $Game2 - $Game3 -
$Series";
}
?>

This code currently displays the results in a list on the page
(http://www.aquilla.org/temp/players/jfold.php) but as you can see, it is
meaningless, I would like to get the results in a table, and have headings
at the top, explaining what each column is.

Anon

Easy.... I've written you a sample code. I have NOT run this myself, so I don't know if it has any errors. In any case, the errors should be minor (i.e. forgetting a semicolon, or an ending brace, etc.)

Just modify it for your own purposes.

-----CODE STARTS HERE-----

<head>
<title>Scores</title>
</head>
<body>
Table Name: jfold 
<table border=1>
<tr>
<td>Date</td>
<td>Rnd</td>
<td>Gms</td>
<td>Hcap</td>
<td>Game1</td>
<td>Game2</td>
<td>Game3</td>
<td>Series</td>
</tr>
<?php

$db_name = "dbase";

$table_name = "jfold";

$connection = mysql_connect("localhost", "user", "pass") or die(mysql_error());

$db = mysql_select_db($db_name, $connection);

$sql = "SELECT * FROM $table_name ORDER BY Date";

$result = mysql_query($sql,$connection) or die(mysql_error());

$display_block .= "<tr><td>$Date</td><td>$Rnd</td><td>$Hcap<td>$Game1</td><td>$Game2</td><td>$Game3</td><td>$Series</td></tr>\n";
}

print $display_block;
?>
</table>
</body>

Anon

Many thanks Sridhar, I have used the code and it produces near enough what I want. I will tweak the script later on.

Stuart.