Page 1 of 1

Variable in SQL Query

Posted: Tue Apr 06, 2010 9:21 pm
by marcdd2
I am trying to add a variable $date to my SQl query and its screwing up somewhere. It works if I use Current_Date( ), but I need to run functions on the date. I'm pretty sure it's the quotes somewhere. Can anyone help? Thanks.

$result = mysql_query('SELECT h.TeamAffiliate AS AwayTeam , a.TeamAffiliate AS HomeTeam , g.HomeTeamID , g.AwayTeamID ,
g.GameDate , g.GameLocation , g.GameTime '
. ' FROM MLBGameTable g '
. ' INNER JOIN TeamsTable h '
. ' ON g.HomeTeamID = h.TeamID '
. ' INNER JOIN TeamsTable a '
. ' ON g.AwayTeamID = a.TeamID '
. ' WHERE GameDate = $date '
. ' ORDER BY GameTime LIMIT 0, 30 ');

Re: Variable in SQL Query

Posted: Wed Apr 07, 2010 12:36 am
by flying_circus
It is a problem with the quotes. PHP interprets whatever is inside of a single quote as a literal. So in fact, your query is passing the string of text '$date'. If you use double quotes, PHP will process the variable and substitute its value, as you would expect.

In short: '$date' and "$date" are not the same.

There are two ways to get around this problem. The first is to convert to double quotes. The second, is to further concatenate the string:

Code: Select all

  $result = mysql_query('SELECT h.TeamAffiliate AS AwayTeam , a.TeamAffiliate AS HomeTeam , g.HomeTeamID , g.AwayTeamID , 
  g.GameDate , g.GameLocation , g.GameTime '
  . ' FROM MLBGameTable g '
  . ' INNER JOIN TeamsTable h '
  . ' ON g.HomeTeamID = h.TeamID '
  . ' INNER JOIN TeamsTable a '
  . ' ON g.AwayTeamID = a.TeamID '
  . ' WHERE GameDate = ' . $date
  . ' ORDER BY GameTime LIMIT 0, 30 ');