8 Replies - 7720 Views - Last Post: 05 May 2009 - 07:48 AM

#1 mrvis  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 04-May 09

Placing a variable in mysql SELECT WHERE CLAUSE

Posted 04 May 2009 - 07:51 AM

	
$city = 'Columbia'
$featuredSQL = 
		mysql_query(
			"SELECT *, (SELECT `location` FROM `photos` WHERE `ptid` = `i`.`id` LIMIT 1) AS `image`
			   FROM `items`  `i`
			  WHERE `active` = 'Yes'
				AND `city` = "$city"
				

			  ORDER BY `featured` DESC
			  LIMIT " . (4 - count($a))
		)



Cant figure out the sytax to pass the variable $city in the AND `city` = "$city"

Is This A Good Question/Topic? 0
  • +

Replies To: Placing a variable in mysql SELECT WHERE CLAUSE

#2 kyrotomia  Icon User is offline

  • D.I.C Head

Reputation: 3
  • View blog
  • Posts: 72
  • Joined: 05-May 07

Re: Placing a variable in mysql SELECT WHERE CLAUSE

Posted 04 May 2009 - 09:02 AM

is that php?
if so, you would have something like this :
$featuredSQL =
		mysql_query(
			"SELECT *, (SELECT `location` FROM `photos` WHERE `ptid` = `i`.`id` LIMIT 1) AS `image`
			   FROM `items`  `i`
			  WHERE `active` = 'Yes'
				AND `city` = '". $city . "'
			   

			  ORDER BY `featured` DESC
			  LIMIT " . (4 - count($a))
		)




Changes I've made :
added single quote before and after the double quotes to specify a string in your sql query;
added . before and after $city to tell php it has to concatenate your $city string with your query.

Let me know if it worked.
Was This Post Helpful? 0
  • +
  • -

#3 mrvis  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 04-May 09

Re: Placing a variable in mysql SELECT WHERE CLAUSE

Posted 04 May 2009 - 10:05 AM

View Postmrvis, on 4 May, 2009 - 06:51 AM, said:

	
$city = 'Columbia'
$featuredSQL = 
		mysql_query(
			"SELECT *, (SELECT `location` FROM `photos` WHERE `ptid` = `i`.`id` LIMIT 1) AS `image`
			   FROM `items`  `i`
			  WHERE `active` = 'Yes'
				AND `city` = "$city"
				

			  ORDER BY `featured` DESC
			  LIMIT " . (4 - count($a))
		)



Cant figure out the sytax to pass the variable $city in the AND `city` = "$city"



Thank you for your response. The changes produces the same results of an empty set like AND `city` = ' '

I am assigning the variable prior with

$city = 'Columbia' yet it is acting like its empty
Was This Post Helpful? 0
  • +
  • -

#4 xerxes333  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 31
  • View blog
  • Posts: 504
  • Joined: 05-July 07

Re: Placing a variable in mysql SELECT WHERE CLAUSE

Posted 04 May 2009 - 11:46 AM

try changing to this AND 'city' = ".$city."
Was This Post Helpful? 0
  • +
  • -

#5 mrvis  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 04-May 09

Re: Placing a variable in mysql SELECT WHERE CLAUSE

Posted 04 May 2009 - 12:26 PM

View Postxerxes333, on 4 May, 2009 - 10:46 AM, said:

try changing to this AND 'city' = ".$city."


Thanks
This created a error syntax

I can put the `city` = 'Columbia' and get the right results -yet I need to use the
variable
Was This Post Helpful? 0
  • +
  • -

#6 xerxes333  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 31
  • View blog
  • Posts: 504
  • Joined: 05-July 07

Re: Placing a variable in mysql SELECT WHERE CLAUSE

Posted 04 May 2009 - 01:34 PM

Oops my fault I read it wrong try this

$city = 'Columbia'
$featuredSQL =
		mysql_query(
			"SELECT *, (SELECT location FROM photos WHERE ptid = i.id LIMIT 1) AS image
			   FROM items  i
			  WHERE active = 'Yes'
				AND city = '$city'
			  ORDER BY featured DESC
			  LIMIT" . (4 - count($a))
		)



A couple of things to keep in mind, ` is not the same as '. When using double quotes (") for your strings, you wont have escape the single quotes (') and you won't have to concatenation the string (.) to access the variable.

This post has been edited by xerxes333: 04 May 2009 - 01:34 PM

Was This Post Helpful? 0
  • +
  • -

#7 mrvis  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 04-May 09

Re: Placing a variable in mysql SELECT WHERE CLAUSE

Posted 04 May 2009 - 02:57 PM

View Postxerxes333, on 4 May, 2009 - 12:34 PM, said:

Oops my fault I read it wrong try this

$city = 'Columbia'
$featuredSQL =
		mysql_query(
			"SELECT *, (SELECT location FROM photos WHERE ptid = i.id LIMIT 1) AS image
			   FROM items  i
			  WHERE active = 'Yes'
				AND city = '$city'
			  ORDER BY featured DESC
			  LIMIT" . (4 - count($a))
		)



A couple of things to keep in mind, ` is not the same as '. When using double quotes (") for your strings, you wont have escape the single quotes (') and you won't have to concatenation the string (.) to access the variable.



Thanks again - but for some reason this produces the same results as have no city data specified -like $city is empty. While 'Columbia' produces desired reults.
Was This Post Helpful? 0
  • +
  • -

#8 kweejee  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 3
  • Joined: 05-May 09

Re: Placing a variable in mysql SELECT WHERE CLAUSE

Posted 05 May 2009 - 07:00 AM

Try displaying the query string first to see if it includes the variable. I changed the code to save the string and echo it before calling mysql_query()

	
$city = 'Columbia';
echo $query = "SELECT *, (SELECT `location` FROM `photos` WHERE `ptid` = `i`.`id` LIMIT 1) AS `image`
	FROM `items`  `i`
	WHERE `active` = 'Yes'
		AND `city` = '$city'
	ORDER BY `featured` DESC
	LIMIT " . (4 - count($a))";
$featuredSQL = mysql_query($query);


Was This Post Helpful? 0
  • +
  • -

#9 xerxes333  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 31
  • View blog
  • Posts: 504
  • Joined: 05-July 07

Re: Placing a variable in mysql SELECT WHERE CLAUSE

Posted 05 May 2009 - 07:48 AM

It worked fine for me. Be sure to terminate your code with ;
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1