MySQL Troubles.

**kane** · 05-13-2003, 10:37 PM

Alright then, i am trying to make a online pool using MySQL and PHP so that me and my friends can play either hockey or football, whatever the season. Anyway, ive got everything to input and come out like i want it, but, being a newbie to php and mysql, im not sure how to get more than 1 team to show up. I would like it to look something like this but im not sure how to do it. I am going to need to put at least 10 teams, most likely somewhere around 50 like that.

this is what it looks like so far.

Here are some links to pictures of how my db is setup:
Image 1
Image 2

Now each team uses some of the same players, i think there is 60 or so players i have to setup in the db, but how do i choose 1 player here, and 1 player there to put them on a team. Hopefully someone knows what im talking about. I don't want to have to make 1 table per team, i want to use 1 table, with all the players in it. Im not sure how to explain it better.
Current Code Used:

Quote:

<html>
<body>
<table align=center border=0 width=60% cellspacing=0 cellpadding=0>
<?php

$db = mysql_connect("localhost", "un", "pw");

mysql_select_db("kane_hockeypool03",$db);

$query = "SELECT * FROM players";
echo mysql_error();
$result = mysql_query($query);
//$row = mysql_fetch_row($result);
$finaltotal = 0;
echo "<tr align=center><td width=10%>Team Of:</td><td width=10%>Allan</td><td width=10%> </td>
<td width=10%> </td><td width=10%> </td><td> </tr>\n";
echo "<tr align=center><td width=10%>Name</td><td width=10%>Goals</td><td width=10%>Assists</td>
<td width=10%>Wins</td>
<td width=10%>Shut-Outs</td><td width=10%>Total</tr>\n";
while($row = mysql_fetch_row($result))
{
echo "<tr align=center>\n";
$last = $row[0];
echo "<td align=left>$last</td>";
$goals = $row[1];
echo "<td>$goals</td>";
$assists = $row[2];
echo "<td>$assists</td>";
$wins = $row[3];
echo "<td>$wins</td>";
$shutouts = $row[4];
echo "<td>$shutouts</td>";
$total = ($goals *2) + $assists + ($wins * 2) + ($shutouts * 3);
echo "<td>$total</td>\n";
echo "</tr>\n";
$finaltotal += $total;
}
echo "<tr align=center>\n";
echo "<td width=10%> </td>";
echo "<td width=10%> </td>";
echo "<td width=10%> </td>";
echo "<td width=10%> </td>";
echo "<td width=10%> </td>";
echo "<td width=10%>$finaltotal pts.</td>\n";
echo "</tr>\n";

?>
</table>

</body>
</html>

Thanks guys, this is much appreciated.

**alchiba** · 05-13-2003, 11:12 PM

You should create a third table containing just the teams. Each team gets a unique numeric ID. This ID is entered in a field for each player in the players table, which will tie each player to a team. Then, give a unique ID to each player in the players table, and create a field in your stats table (1.gif) for this ID so you can tie each player to a group of statistics. Make each ID an autoincrement field so the number is automatically bumped when you add a team, player, whatever. Doing this should give you more of a relational database and you'll be able to build some decent queries: select/sort by team, player, assists, anything you like.

That's a start, anyway.

**kane** · 05-14-2003, 02:28 AM

alrighty, i'll have to try that...

lets hope i understand and can interpret what ur saying.

**david** · 05-14-2003, 06:52 AM

Hmm..... You mentioned that:

Quote:

Now each team uses some of the same players,

so you will probably need to set up your players in one table, teams in another, then have a relational table like this:

id, teamid, playerid

Then for each player in a team relate the team's id to the player's id. Then to select all the players in a team for example:

Code:

select t.teamname, p.playername from teams as t, players as p, relational as r where t.id=r.teamid and p.id=r.playerid where t.teamname='theteam';

Hope that makes some sense

**alchiba** · 05-14-2003, 11:03 AM

I tried to post a diagram but it didn't come out too well. I'll try again later.