[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Mysql Query - Select where value could be anything

Posted on 2011-04-28
5
Medium Priority
?
339 Views
Last Modified: 2013-12-12
Hi

I am writing a mysql query like below:

SELECT * FROM properties WHERE price = '$price' AND type = '$type' AND tenure = '$tenure'

Some of the options allows the user to select ANY instead of selecting a specific value. So in a case when the user does select ANY how do I tell the query to select any/all results for that particular column?

I hope you know what I mean.

Thanks

Daz
0
Comment
Question by:DAZTOMKINS
  • 3
  • 2
5 Comments
 
LVL 19

Expert Comment

by:Greg Alexander
ID: 35484174
Perhaps consider this:
<?
if($price == 'any'){
	$price_query = "1 = 1";
}else{
	$price_query = mysql_real_escape_string($_POST['price']);
}

if($type == 'any'){
	$type_query = "1 = 1";
}else{
	$type_query = mysql_real_escape_string($_POST['type']);
}

if($tenure == 'any'){
	$tenure_query = "1 = 1";
}else{
	$tenure_query = mysql_real_escape_string($_POST['tenure']);
}

$query = "SELECT * FROM properties WHERE ".$price_query." AND ".$type_query." AND ".$tenure_query ."";
?>

Open in new window

0
 
LVL 19

Expert Comment

by:Greg Alexander
ID: 35484182
Sorry, actually this should work
<?
if($price == 'any'){
	$price_query = "1 = 1";
}else{
	$price_query = "price = '".mysql_real_escape_string($_POST['price'])."";
}

if($type == 'any'){
	$type_query = "1 = 1";
}else{
	$type_query = "type = '".mysql_real_escape_string($_POST['type'])."";
}

if($tenure == 'any'){
	$tenure_query = "1 = 1";
}else{
	$tenure_query = "tenure = '".mysql_real_escape_string($_POST['tenure'])."";
}

$query = "SELECT * FROM properties WHERE ".$price_query." AND ".$type_query." AND ".$tenure_query ."";
?>

Open in new window

0
 

Author Comment

by:DAZTOMKINS
ID: 35484252
Hi

Thanks for that, it does make sense however I cannot use it like that as im using Prepared Statements whereby of course the logic is seperated from the data.

I thought there might be a pre-defined word that tells the query to select all or select any result for that field? I havent found anything yet though.

Thanks

Daz
0
 
LVL 19

Accepted Solution

by:
Greg Alexander earned 2000 total points
ID: 35484401
Maybe try LIKE?

SELECT * FROM properties WHERE price LIKE '%$price%' AND type LIKE '%$type%' AND tenure LIKE '%$tenure%';

that way if it is blank, it will select all
0
 

Author Comment

by:DAZTOMKINS
ID: 35484786
Thanks that worked great.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this. Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it i…
The title says it all. Writing any type of PHP Application or API code that provides high throughput, while under a heavy load, seems to be an arcane art form (Black Magic). This article aims to provide some general guidelines for producing this typ…
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to count occurrences of each item in an array.
Suggested Courses
Course of the Month18 days, 14 hours left to enroll

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question