Variable in SQL Query

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
marcdd2
Forum Newbie
Posts: 4
Joined: Tue Apr 06, 2010 7:58 pm

Variable in SQL Query

Post 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 ');
User avatar
flying_circus
Forum Regular
Posts: 732
Joined: Wed Mar 05, 2008 10:23 pm
Location: Sunriver, OR

Re: Variable in SQL Query

Post 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 '); 
Last edited by Benjamin on Wed Apr 07, 2010 12:38 am, edited 1 time in total.
Reason: Please use [syntax=php]...[/syntax] tags. [code] tags have been depreciated.
Post Reply