help with query

[rizzah00]

hello, i\'m quering the database

here is my code

<?php



mysql_connect("localhost", "user", "pass");

mysql_select_db("database");

$query = "select r.id, r.week, r.awayteam, r.hometeam, t.team_id FROM results r, teams t";

$result = mysql_query($query); 

$numrows = mysql_num_rows($result); 

while($row = mysql_fetch_array($result)){ 

echo "<tr>";

  echo "<td>";

     echo "{$row[\'id\']}";

     echo "-";

     echo "</td>";

     echo "<td>";

  echo "week";

  echo "{$row[\'week\']}";

     echo "</td>";

  echo "<td>";

  echo "{$row[\'awayteam\']}";

     echo "</td>";

  echo "<td>";

  echo "{$row[\'hometeam\']}";

     echo "</td>";

}

?>

I get the query results and right now there are only two entries into that table, yet whenever i query the database i get like 30 some results but its just the two that are in the database alternating any help would be appreciated...

[DylanBlitz]

Try a select distinct...

 

[php:1:7ea81d73d8]<?php

$query = \"select r.id, distinct r.week, r.awayteam, r.hometeam, t.team_id FROM results r, teams t\";

?>[/php:1:7ea81d73d8]

[pallevillesen]

You have to join the two tables results and teams

 

like

 

WHERE t.team_id = r.team_id

 

or something similar...

 

I don\'t see why you get team_id anyway (since awayteam and hometeam probably are team ids anyway ?)

 

But if you insist, then you must join the two tables in a WHERE clause or do a straight JOIN... (rtfm on syntax).

 

P.

[michael yare]

Heheh.

 

He\'s gonna have a bitch of a time with this.

 

He has nowhere to link teamID. He has home team and away team in an un-normalised table you cannot use OR statements on a JOIN (where teamID = hometeam or teamID = awayteam.....).

 

3 options:

 

1. Get MySQL 4 so you can do UNIONs

 

2. Do 2 queries that INSERT INTO a temp table on hometeam, then awayteam, then SELECT from the temp table.

 

3. Normalise your tables. Add table team_match (team_matchID, hometeam, awayteam) then do complex queries linking team, match and team_match.