homepage Welcome to WebmasterWorld Guest from 54.205.197.66
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

    
search for all results in mysql
not possible - so how is it done
whatson




msg:4519142
 8:20 pm on Nov 14, 2012 (gmt 0)

So if you have a search feature, and drop down boxes are included, but you want to list an option in the drop down box as ALL, displaying all e.g. price ranges, and in your mysql query you have:
SELECT * FROM `products` WHERE `price`=$priceRange AND `locaction`=$location

Then what if you want to choose all? Obviously this will not work. So how is this usually done? Do you have to use switch statements for if isset each variable, and the combination of each, as you must also enter the AND into the query too.
And what if you have 10 or even more search parameters? There are too many combinations.

How is this done? I can't work out the logic.

 

LifeinAsia




msg:4519145
 8:27 pm on Nov 14, 2012 (gmt 0)

You'll need to include some sort of conditional logic in building the SELECT statement. Use the "`price`=$priceRange" unless all is selected. (I don't know PHP so I can't give you the exact way to write it.) Include the same logic for all your other conditionals.

If every conditional can be selected for all, you'll also need an initial "1=1" conditional. Something like the following in pseudo code:
SELECT *
FROM `products`
WHERE 1=1
IF ($priceRange<>'*') AND `price`=$priceRange
IF ($location<>'*') AND `locaction`=$location
...

(NOTE: the psuedocode is used to build the SQL statement in PHP.)

swa66




msg:4519185
 11:08 pm on Nov 14, 2012 (gmt 0)

You can extend the logic to detect if there's none, 1 or more options to include and that way avoid that 1=1 - it feels to much like a hacker got to you.

whatson




msg:4519197
 11:49 pm on Nov 14, 2012 (gmt 0)

oh awesome I can do an if statement in the mysql, but then I have the issue of the 'AND', what if it becomes WHERE AND `price`=$priceRange?

vincevincevince




msg:4519208
 1:09 am on Nov 15, 2012 (gmt 0)

Either write the statement in PHP and then send it, or have the if:false option as 1:
"WHERE 1 AND `price`=$pricerange"

swa66




msg:4519399
 5:02 pm on Nov 15, 2012 (gmt 0)

In pseudo code:


...
$sql = "SELECT field1, field2, ... fieldN FROM tablename";
$counter=0;
if (test to decide you want to output a price restriction) {
$sql .= "WHERE `price`= $price";
$counter++;
}
if (test to decide you want to output a location restriction) {
if ($counter ==0) {
$sql .= "WHERE ";
} else {
$sql .= "AND ";
}
$sql .= "`location`= $location";
$counter++;
}
...


And that way you have no 1=1 that will scare any security minded person to soil their pants.

now that said .. I'd still prefer prepared statements over this.

whatson




msg:4519436
 6:39 pm on Nov 15, 2012 (gmt 0)

prepared statements? How do you mean?

swa66




msg:4519516
 11:54 pm on Nov 15, 2012 (gmt 0)

Prepared statements: [php.net...]

The huge advantage is that you do not need to worry about mysql mixing commands and data (and start to execute what you think is data as if it were commands.)

The disavantage is that you need a slightly bit more effort to be able to tell mysql what is data and what is the command.

vincevincevince




msg:4519679
 12:22 pm on Nov 16, 2012 (gmt 0)

swa66- what's the security risk of "WHERE 1 AND xyz"?

swa66




msg:4519736
 4:33 pm on Nov 16, 2012 (gmt 0)

There's no direct risk in it as such.

It's just that if you /a security minded person / anomaly detection software sees that going to the database, there's an almost automatic assumption some SQL injection is happening.

There's a (small) indirect risk in that it would dull the senses and make it harder than it needs to be to put in place anomaly detection (or make it less effective.

It's easy enough to output sql commands that do not have it in them, so why do it then ?

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved