Querying multiple tables

[Lionel]

Why would I get the same result over and over here (http://www.haitiwebs.com/haitianforums/soccerscoreboard.php) for the below code (the magnifier image)? Looks like once it finds one it puts it in all the others afterwards. (In order to make this thread short, I removed the excess ... )Depending of the first query results, it determines which table to use in the second query.

Thanks in advance for any help.

$get_matches = $DB_site->query("blabla");
$i=0;
while($data = $DB_site->fetch_array($get_matches))
{
if($i == 0)
{
$temp = $data['date'];
}
if($data['date'] <= $temp)
{
$date = $data['date'];
if($data['hometeam'] == "AAA"){
$thefolderhome = "aaa";
$thedbhome = "FROM asc_matches";
$thedbhomepic = "FROM asc_picturegallery";
}
else if($data['hometeam'] == "BBB"){
$thefolderhome = "bbb";
$thedbhome = "FROM aiglenoir_matches";
$thedbhomepic = "FROM aiglenoir_picturegallery";
}

//repeated 14 more times ....

if($data['awayteam'] == "AAA"){

$theteam = "aaa";

$thefolderaway = "asc";

$thedbaway = "asc_opponents";

$thedbawaypic = "FROM asc_picturegallery";

}

else if($data['awayteam'] == "BBB"){
$theteam = "bbb";
$thefolderaway = "aiglenoir";
$thedbaway = "aiglenoir_opponents";
$thedbawaypic = "FROM aiglenoir_picturegallery";
//repeated 14 more times ....
$getreport = $DB_site->query("SELECT
blabla $thedbhome M, $thedbaway O
WHERE M.MatchDateTime < CURRENT_TIMESTAMP AND M.MatchReport != '' AND
O.OpponentID = M.MatchOpponent
");
while($hasreport = $DB_site->fetch_array($getreport))
{
$reportid= $hasreport['id'];
$thereport = "<a href=\"football/$thefolderhome/matchdetails.php?id=$reportid\"><img src=\"/football/preview.gif\" alt=\"match report\" border=\"0\"></a>";

$havepics =$DB_site->query_first("SELECT count(PictureID) AS pic $thedbhomepic
WHERE PictureMatchID = $reportid");
if ($havepics[pic] > 0)
{
$reportgallery = "<a href=\"<A href="football/$thefolderhome/picturegallery.php?id=$reportid\"><img src=\"/haitianforums/football/camera.gif\" alt=\"Pictures from the match\" border=\"0\" align=\"right\"></a>";
}
else
{
$reportgallery = '';
}
}

eval('$scoreboardbits .= "' . fetch_template('soccerscoreboardbits') . '";');
}
else
{
break;
}
$i++;
}
$DB_site->free_result($get_matches);
$DB_site->free_result(getreport);

[Marco van Herwaarden]

Very difficult to anwer without the complete code and samples of the data, but i think the following query is the problem:
$getreport = $DB_site->query("SELECT
blabla $thedbhome M, $thedbaway O
WHERE M.MatchDateTime < CURRENT_TIMESTAMP AND M.MatchReport != '' AND
O.OpponentID = M.MatchOpponent
");


You are running this query inside a loop where you are processing all matches. In the above query you however don't select on the 'matchid' (or whatever that is called). The result is that you will get the reports of all matches, and you will be using the $reportid from the first match over and over.

PS Next time please use the [ php ] tags, makes it a lot easier to read.