Outputting an HTML Table from PHP/MySQL?

Question

I have a database with three tables for a football pool website. The three tables (with fields in parenthesis)...

-USERS (userid, username, firstname, lastname)

-TEAMS (teamid, teamname)

-PICKS (teamid, userid, week#, winner)

Anyway, I know how to have people submit their picks and whatnot, but as far as retrieving the data and displaying it how I want, I am having trouble. Look at the following website to see how my output looks right now...

http://gdoot.com/examplestandings.php

In the pool, users pick 4 winners each week. In the example, Gary has made his Week 2 picks already and I would like that 2nd set of 4 picks to be listed under Week 2. But as you can see, because of my current code, it's wrapping underneath his name field in the HTML table.

This is the code I currently have. Assuming this is enough information, any idea how I can change the code so that each subsequent set of 4 picks for each specific user appears under the next week?

<?php

$user_set = mysql_query("SELECT * FROM users", $connection);

if (!$user_set) {

die("Database query failed: " . mysql_error());

}

while ($users = mysql_fetch_array($user_set)) {

echo "<tr><td class='name' rowspan='4'>{$users["firstname"]}" . " " . "{$users["lastname"]}</td>";

$pick_set = mysql_query("SELECT teamname FROM teams, picks WHERE picks.userid = {$users["userid"]} and picks.teamid = teams.teamid", $connection);

if (!$pick_set) {

die("Database query failed: " . mysql_error());

}

while ($picks = mysql_fetch_array($pick_set)) {

echo "<td class='pick' rowspan='1'>{$picks["teamname"]}</td></t...

}

echo "</tr>";

}

?>

</tr>

</table>

</div>

Answer 1

I haven&#039;t tested this code, so there might be a few errors. Honestly,  I&#039;ve found dealing with rowspans to be a pretty grueling task. So basically this will hit your db 17 times for each user and build a table instead for every set of teams, which should look very close to what you&#039;re looking for. You&#039;ll probably have to tweak your css a little.

&lt;div&gt;

&lt;table&gt;

&lt;?php

//after you&#039;ve started the table

//and drawn the first header row do the following:

$user_set = mysql_query(&quot;SELECT * FROM users&quot;, $connection);

if (!$user_set) {

die(&quot;Database query failed: &quot; . mysql_error());

}

while ($users = mysql_fetch_array($user_set)) {

echo &quot;&lt;tr&gt;&lt;td class=&#039;name&#039;&gt;{$users[&quot;firstname&quot;]}&quot; . &quot; &quot; . &quot;{$users[&quot;lastname&quot;]}&lt;/td&gt;&quot;;

for($x = 1; $x &lt;= 17; $x++){

echo &#039;&lt;td&gt;&#039;;

$sql = &quot;SELECT teamname

FROM teams, picks

WHERE picks.userid = &#039;{$users[&quot;userid&quot;]}&#039;

AND picks.teamid = teams.teamid

AND picks.week# = &#039;$x&#039; &quot;;

$pick_set = mysql_query($sql, $connection);

if (!$pick_set) {

die(&quot;Database query failed: &quot; . mysql_error());

}

if(mysql_num_rows($res)){

echo &quot;&lt;table&gt;\n&quot;;

while ($picks = mysql_fetch_array($pick_set)) {

echo &quot;&lt;tr&gt;&lt;td class=&#039;pick&#039; rowspan=&#039;1&#039;&gt;{$picks[&#039;teamname&#039;]}&lt;/td&gt;

&lt;/tr&gt;\n&quot;;

}

echo &quot;&lt;/table&gt;\n&quot;;

}

else{

echo &#039;&amp;nbsp;&#039;;

}

echo &#039;&lt;/td&gt;&#039;;

}

echo &quot;&lt;/tr&gt;\n&quot;;

}

?&gt;

&lt;/table&gt;

&lt;/div&gt;

Answer 2

Nevermind.