Query DB for column value that is in a given array

Question

I'm trying to query a table in my database for the rows in which both the columns home_team and away_team contain a value that is in an array. No such luck, however. My code is as follows.

$new_array = array();

for($i=0;$i<count($ncaa);$i++)
{
//  Since some of the values in this array contain apostrophes, escape each value 
$new_array[$i] = mysql_real_escape_string($ncaa[$i]);   
}

$list = join(",", $new_array);
$query = "SELECT * 
     FROM ncaa_games
     WHERE game_date_int >= '".$limit."' 
     AND (home_team = '".mysql_real_escape_string($team)."' OR away_team = '".mysql_real_escape_string($team)."') 
     AND home_team IN (".$list.")
     AND away_team IN (".$list.")
     ORDER BY game_date_int ASC 
     LIMIT 1";

$next = mysql_query($query)or die(mysql_error());

I keep in getting an error message reading "ou have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''UTSA Roadrunners") AND away_team IN (Albany Great Danes,Binghamton Bear' at line 5"

I have a feeling that this could be something rather simple. But, it's still managing to elude me. Any ideas?

Thanks,

Lance

is it possible that game_date_int is an int and you are trying to pass it a string? — pzirkind
– pzirkind, Commented Jan 6, 2013 at 21:44
What is this? AND (home_team = '".mysql_real_escape_string($team)."' OR away_team = '".mysql_real_escape_string($team)."') — Hamlet Hakobyan
– Hamlet Hakobyan, Commented Jan 6, 2013 at 21:44

Jim · Accepted Answer · 2013-01-06 21:43:21Z

2

$list = join(",", $new_array);

Will not enclose the teams in quotes. I.e. you have:

away_team IN (Albany Great Danes,Binghamton Bears)

When this should be:

away_team IN ('Albany Great Danes','Binghamton Bears')

Try using: $list = join("','", $new_array); and having:

 AND home_team IN ('".$list."')
 AND away_team IN ('".$list."')

(Make sure $list isn't empty).

answered Jan 6, 2013 at 21:43

Jim

22.7k6 gold badges55 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Lance Over a year ago

Thanks a bunch. I had a feeling is was a simple syntax error. Something trivial.

Jim Over a year ago

@Lance No problem. A fresh set of eyes always helps :)

Collectives™ on Stack Overflow

Query DB for column value that is in a given array

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related