filtering mysql queries

can't quite work out how this should be written

[whatson]

I am trying to create a search filter. So I have a GET form.
One of the fields is price range, e.g. under $10, $10-20, $20-30, etc.
When one of these are chosen, then I only want to show results that are between the selected price range.

1. What values would you suggest for the drop down box inputs?
2. What mysql query do I used to obtain results that have this price range.

My current values are e.g. 10-20, I then explode the values to obtain 10 and 20. Make them variables $hi and $lo.
Then SELECT * FROM products WHERE price > $lo AND price < $hi.

This gets quite complex when adding even more filters. I am sure there must be an easier, industry standard method. Should I use the BETWEEN operator for example?

[swa66]

From what you explain, you seem to use the input coming from the browser to get your hi and lo values ? If that's indeed the case: I'd suggest extreme caution. It's not because you can't select something in a browser that even a wannabe hacker can't give you *any* string they like.

So instead of the explode, I'd suggest to compare the string you get as input from the browser and compare it to the strings you know are in your form, and then in a switch statement set the high and low values yourself, and deal with the option that something else came in just as well.

[jadebox]

Have the HTML return a value for the selected range - for example return 1 for < $10, 2 for $10 to $20, 3 for $20 to $30, etc.

In PHP do something like:

switch($range) 
 { 
 case 1: 
 $range = "price < 10"; 
 break; 
 case 2: 
 $range = "price >= 10 AND price < 20"; 
 break; 
 ' ... and so on 
 } 

Then use $range as the "WHERE" part of the SQL query.

[whatson]

isn't BETWEEN a better operator for ranges?
Also, why do you need to use the switch statements, can't you just set the $range's as the initial value of the drop down list?

[omoutop]

<=, => and BETWEEN have some performance differences, but only noticeable in very large data sets (as far as i understand it)

as for the switch statement, it's just another if-else statement expressed differently

[whatson]

Yes, I understand what a switch statement is, but is it necessary? Can't the actual value of the drop down list be that in the first place, rather than having to go through a switch statement?

[brotherhood of LAN]

Yes, you can have them as values in your form, but you'll have to validate the values anyway so it doesn't make too much difference.

[jadebox]

"Yes, I understand what a switch statement is, but is it necessary? Can't the actual value of the drop down list be that in the first place, rather than having to go through a switch statement?"

As the previous poster said, you'd have to verify that the values you receive are correct otherwise a hacker could use "SQL Injection" to cause some harm.

For example, if your web page returns something like "price < 10" as the value for one of the options, someone could call your script with the value set to something like "1=1; DROP TABLE users".

If your script just used the value without checking it, then it would execute a query like "SELECT * FROM products WHERE 1=1; DROP TABLE users" which could really mess up things on your site.

[whatson]

Oh I see, so it can only be validated through these cases, anything else will be ignored.

[whatson]

What about search then? How is that validated?

[omoutop]

basic validation of anything coming from users, include mysql_real_escape0string() function.

for more advance filtering, you can use restrictions such us: verify if it is a number (integer or float), check length of text/word, check for alphanumeric strings and remove unwanted symbols/characters, etc

it all depends on what kind of search you allow your users to perform

[whatson]

Right, so something like preg_replace('/[^A-Za-z0-9-]/','', $search) should be good validation

[swa66]

preg_replace('/[^A-Za-z0-9-]/','', $search) sounds quite harsh on those who might need to support accented letters (read: non-english text).

But essentially the whitelist approach is the right way: only allow in what you know you can deal with, reject all the rest.

[whatson]

I found a better way, I just set the value as the name, and then used the name to look up the id, and used the id as the variable to insert into the mysql.