hornyy hornyy - 6 months ago 9
SQL Question

SQL PHP Select columns with several conditions in same query

I can't figure out how to make a selection of the matches between 2 teams even if is at home or away.

Example:

I have this table:

MatchID | status | date | short (home) | opponent (Away)
1 ENDED XXX TEAM A TEAM B
2 ENDED XXX TEAM B TEAM A
3 ENDED XXX TEAM C TEAM B
4 ENDED XXX TEAM D TEAM A


I have a lot of matches and I want to make a module where I can show all previous matches between team A and team B. (even if is at home or away).

Right now this is my code, but is only showing 1 match of 2 possible matches.

$ergebnis = safe_query("SELECT * FROM table WHERE status='ENDED' AND ((opponent = '".$opp_match."' AND short = '".$short_match."') OR (opponent = '".$short_match."' AND short = '".$opp_match."')) ORDER BY date LIMIT 0,5");


And I want to limit 0,5. Just want the last 5 matches between the 2 teams.

"opp_match" and "short_match" are connections to the other module. When I'm check the match I can check home team and away team with those.

With or without the limits I can't show more than one result.

I just want to show matchID 1 and 2. But right now I'm just getting matchID 1.

EDIT: I tried another way but I'm loading all ended matches.

$ergebnis = safe_query("SELECT * FROM ".PREFIX."upcoming WHERE status='ENDED'");
$i=1;
while($ds=mysql_fetch_array($ergebnis)) {

if($ds['short']==$short_match AND $ds['opponent']==$opp_match) {
eval ("\$matches = \"".gettemplate("matches")."\";");
echo $matches;
}
elseif($ds['opponent']==$short_match AND $ds['short']==$opp_match) {
eval ("\$matches = \"".gettemplate("matches")."\";");
echo $matches;
}
else echo '';


$i++;
}

Answer

Will this work?

SELECT *
FROM table
WHERE status = "ENDED"
AND 
(
    (opponent = "team1" AND short = "team2")
    OR
    (opponent = "team2" AND short = "team1")
)
ORDER BY MatchID DESC
LIMIT 5;

This assumes that if one match has a smaller ID than another, it has been played before the other.

Comments