[RESOLVED] Accessing values in MySQL Database

[yanks6rule]

Hello,

I am a relatively new to PHP programming. I had a question about accessing two teams names from the same database. I am trying to put together a site for a flag football league I am in. In one table (Teams) I have two fields teamID and team and this just lists out all the teams. In a second table (Week1games) I have 5 fields, gameID, hometeam, awayteam, homescore and awayscore. I put the teams into this table with the teamID so I am not storing a teamname over and over again. I want to access the table, Week1games and populate a table with the home team and away team. I wrote this SQL statement

SELECT teams.team, Week1games.awayteam, Week1games.hometeam, Week1games.homescore, Week1games.awayscore, FROM teams, Week1games WHERE Week1games.hometeam=teams.teamID

I can populate a table with a while statement using mysql_fetch_array except for the away team. The teams.team is tagged to the home team with the Week1games.hometeam=teams.teamID and I don't know how to put the actual name of the away team in the table. I am supplying my actual table below.

Thanks for the help,

include "login.php";
			$db = mysql_select_db("football", $my_db) or die(mysql_error());
			$sql = "SELECT teams.team, Week1games.awayteam, Week1games.hometeam,Week1games.homescore, Week1games.awayscore FROM teams, Week1games WHERE Week1games.hometeam=teams.teamid";
			$res = mysql_query($sql);
			if (mysql_num_rows($res) != "0") {
				while($row = mysql_fetch_array($res)) {
					echo "<tr>";
					echo "<td><p>". $row['team'] ."</p></td>";
					echo "<td><p>". $row['awayscore'] ."</p></td>";
					echo "<td><p>". $row['team'] ."</p></td>";
					echo "<td><p>". $row['homescore'] ."</p></td>";
					echo "<td><p>Winner</p></td>";
					echo "</tr>";
					}
				} else {
				echo "<td><p>We did not connect to the database</p></td>";
			}

[Piranha]

Give the table an alias, then you can handle multiple occurrences of the same table in one query.

select t1.name, t2.name, g.something
from game g
join team t1 on g.hometeam = t1.id
join team t2 on g.awayteam = t2.id

By the way, by the naming you use it seems like you have one table for the first weeks game, another for the second weeks game and so on. It should all be in one table, you can have a column to indicate what week game it is.

[yanks6rule]

Piranha,

This worked great. A little bit of struggling with some SQL syntax made me learn quite a bit about joins and aliases. I am not going to try and incorporate your suggestion of having one table for all games in a year instead of 12 (Number of games we have).

Thanks again and I am sure I will be back for more later.