Jump to content


help with query

  • Please log in to reply
3 replies to this topic

#1 rizzah00

  • New Members
  • Pip
  • Newbie
  • 4 posts

Posted 11 August 2003 - 06:30 PM

hello, i\'m quering the database
here is my code
<?phpmysql_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...

#2 DylanBlitz

  • Members
  • PipPipPip
  • Advanced Member
  • 99 posts
  • LocationOC Baby!

Posted 11 August 2003 - 08:35 PM

Try a select distinct...

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

#3 pallevillesen

  • Members
  • PipPipPip
  • Advanced Member
  • 135 posts
  • LocationDenmark

Posted 12 August 2003 - 12:13 PM

You have to join the two tables results and teams


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).

Palle Villesen, www.birc.dk [br]Bioinformatics Research Center

#4 michael yare

michael yare
  • Members
  • PipPip
  • Member
  • 25 posts
  • LocationLondon, UK

Posted 12 August 2003 - 02:29 PM


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.

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users