Jump to content

Archived

This topic is now archived and is closed to further replies.

rizzah00

help with query

Recommended Posts

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

Share this post


Link to post
Share on other sites

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]

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.