Creating a where_clause with an array and loop

oh_maestro

I have a query that works fine when I know the exact number of Teams that will be included. This is the code snippet that works fine:

$query = "SELECT * FROM `basketball` WHERE Team1 LIKE '$Opp[1]' OR Team2 LIKE '$Opp[1]' OR Team1 LIKE '$Opp[2]' OR Team2 LIKE '$Opp[2]' OR Team1
 LIKE '$Opp[3]' OR Team2 LIKE '$Opp[3]' OR Team1 LIKE '$Opp[4]' OR Team2 LIKE '$Opp[4]' OR Team1 LIKE '$Opp[5]' OR Team2 LIKE '$Opp[5]' OR Team1
 LIKE '$Opp[6]' OR Team2 LIKE '$Opp[6]' OR Team1 LIKE '$Opp[7]' OR Team2 LIKE '$Opp[7]' OR Team1 LIKE '$Opp[8]' OR Team2 LIKE '$Opp[8]' OR Team1
 LIKE '$Opp[9]' OR Team2 LIKE '$Opp[9]' OR Team1 LIKE '$Opp[10]' OR Team2 LIKE '$Opp[10]'  ORDER BY `Date` ASC";

What I'd like to be able to do, is make this dynamic in length so the number of oppenents could be any number. I cannot build my where_clause correctly to include $Opp and an incrementing number to go through the previously established array.

This is what I have so far:
Assume the $num_opp represents the number of elements in the $Opp array.

$where_x = 1;
$where_clause = "Team1 LIKE '$Opp[1]' OR Team2 LIKE '$Opp[1]'"; //assuming there is at least one element

while ($where_x < $num_opp)	{
	$where_x++;
	$where_clause = " OR Team1 LIKE '$Opp[".$where_x."]' OR Team2 LIKE '$Opp[".$where_x."]'";
} //end where clause				

$query = "SELECT * FROM `basketball` WHERE $where_clause ORDER BY `Date` ASC";

Thanks in advance for any help!

bradgrafelman

If you've got an array of team ID values (which I'm guessing is what these Team1 and Team2 columns contain?) and you're simply trying to SELECT all rows where these values appear in either column, note you could make the SQL query a bit simpler using the IN () notation, e.g.:

SELECT col1, col2
FROM basketball
WHERE
    Team1 IN (1, 2, 3, 4, 5, ..., 999)
    OR Team2 IN (1, 2, 3, 4, 5, ..., 999)

which could be constructed as simply as:

$where = 'Team1 IN (' . implode(',', $array_of_team_IDs) . ") \n";
$where .= 'OR Team2 IN (' . implode(',', $array_of_team_IDs) . ") \n";

Also note that you could simplify the querying even further if you restructured the DB into something similar to:

Games
id
name
location
(more game-specific attributes)

Teams
id
name
(more team-specific attributes)

Game_Scores
game_id (foreign key to Games.id)
team_id (foreign key to Teams.id)
final_score
field_goals
free_throws
(more game- and team-specific attributes)

Thus for a given game, you'd insert two rows into Game_Scores - one per team. Now, querying the DB to find all the games involving a list of team IDs would be something like:

SELECT g.id, g.name, g.location
FROM Games g
WHERE g.id IN (
    SELECT gs.game_id
    FROM Game_Scores gs
    WHERE team_id IN (1, 2, 3, 4, ..., 999)
)

Derokorian

Hey brad,

Thanks for the basic DB table structure. I'm just about to start a project with tracks teams, games and scores and was wondering a good table structure. I will need more than this but this gives me a great basis to go on, and I'm sure will save me hassle later when I'm doing the same thing!

Sorry for the small high jack, but this helped me 😛

oh_maestro

This table is used to track basketball scores in our area. Each game is one record, and it determines the wins/losses etc. by the score that I enter. The home team is Team1 & Team2 is the away team.

This code snippet is a part of a scouting calendar. This code runs on each day of the calendar. I did not include the code: Date = '$thisdate' because it wasn't relevent to my problem.

So what it is really doing, is checking each day in a month, and if it is an opponent that plays a certain team, I want to list it. I already have the list populated in an array correctly. Here is the code.

		
		$OurTeam = "Lincoln";
		$query_Opp = "SELECT * FROM basketball WHERE Team1 = '$OurTeam' OR Team2 = '$OurTeam' ORDER BY `Date` ASC";
		$TOpp = mysql_query($query_Opp) or die(mysql_error());
		$row_Opp = mysql_fetch_assoc($TOpp);

	$x = 1;
do {
	$Team1 = $row_Opp['Team1'];
	$Team2 = $row_Opp['Team2'];

	if ($row_Opp['Team1'] == "$OurTeam") {
		$Opp[$x] = $row_Opp['Team2']; }
	else {
		$Opp[$x] = $row_Opp['Team1']; }
	$x++;
} while ($row_Opp = mysql_fetch_assoc($TOpp));

I have hand-written the code listed in my first post, because I knew have 20 (I shortened to 10 in the example for brevity) games. But, as we add games in the tournament we play, the number could be 21, 22, etc. It really isn't a huge deal, but I would like to see if it is possible to build my where_clause to include an incrementing array value.