need more hellp hehe

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

[bastien]

need to change your query. it is currently a cartesian join....

can you show table structure to see where the join should be correctly done....

[rizzah00]

i have it joined with

WHERE t.team_id = r.awayteam AND t.team_id = r.hometeam

i guess i just didnt update my post, but now i get no results at all

here are my tables
TABLE teams
team_id int(25) NOT NULL default '0',
location varchar(255) NOT NULL default '',
name varchar(255) NOT NULL default '',
pre varchar(255) NOT NULL default '',
wins int(25) NOT NULL default '0',
loses int(25) NOT NULL default '0',
PRIMARY KEY (team_id),
KEY team_id (team_id)

and
TABLE results (
id int(25) NOT NULL auto_increment,
week int(10) NOT NULL default '0',
email varchar(32) NOT NULL default '',
hometeam int(25) NOT NULL default '0',
awayteam int(25) NOT NULL default '0',
homescore int(25) NOT NULL default '0',
awayscore int(25) NOT NULL default '0',
qb1name varchar(32) NOT NULL default '',
qb1yards int(25) NOT NULL default '0',
hteamname varchar(25) default NULL,
ateamname varchar(25) default NULL,
PRIMARY KEY (id)

i want to be able to use the t.name to give me the names of the r.awayteam and r.hometeam...which is what i was trying to do with the code at the top

[bastien]

the team ids are the same for both (ie the team name must meet both home and away...since the team cannot play itself, there is no result set...

[quote]

WHERE t.team_id = r.awayteam AND t.team_id = r.hometeam

[/quote]

needs to be

WHERE t.team_id = r.awayteam OR t.team_id = r.hometeam

[rizzah00]

okay, that fixed my 30 entries but i still have one and its doubling it...

[bastien]

show complete sql code please

[rizzah00]

hehe nevermind i worked around it, just fyi for anyone else though

<?
function team_array() {
   $r = mysql_query("SELECT * FROM `teams`");
   $o = array();
   while ($row = mysql_fetch_assoc($r)) {
      $id = $row['team_id'];
      $o[$id] = $row;
   }
   return $o;
}
?>
html here
$query = "select r.id, r.week, r.awayteam, r.hometeam, t.team_id FROM results r, teams t WHERE r.awayteam = t.team_id OR r.hometeam = t.team_id";
$result = mysql_query($query);

$teams = team_array();
$echoed = array();
while ($row = mysql_fetch_assoc($result)) {
   if (!in_array($row['id'], $echoed)) {
      $echoed[] = $row['id'];
      $aid = $row['awayteam'];
      $hid = $row['hometeam'];
	  $weekid = $row['id'];
      echo <<<EOF
<tr>