Welcome to WebmasterWorld Guest from 54.162.226.212

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

filtering mysql queries

can't quite work out how this should be written

     
7:21 am on Nov 7, 2012 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



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?
8:41 am on Nov 7, 2012 (gmt 0)

WebmasterWorld Senior Member swa66 is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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.
6:20 pm on Nov 7, 2012 (gmt 0)

10+ Year Member



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.
6:38 pm on Nov 7, 2012 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



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?
7:08 am on Nov 8, 2012 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



<=, => 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
7:58 pm on Nov 8, 2012 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



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?
9:25 pm on Nov 8, 2012 (gmt 0)

WebmasterWorld Administrator brotherhood_of_lan is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month



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.
9:43 pm on Nov 8, 2012 (gmt 0)

10+ Year Member



"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.
12:30 am on Nov 9, 2012 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



Oh I see, so it can only be validated through these cases, anything else will be ignored.
8:28 am on Nov 9, 2012 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



What about search then? How is that validated?
12:37 pm on Nov 9, 2012 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



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
8:03 pm on Nov 9, 2012 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



Right, so something like preg_replace('/[^A-Za-z0-9-]/','', $search) should be good validation
10:51 pm on Nov 9, 2012 (gmt 0)

WebmasterWorld Senior Member swa66 is a WebmasterWorld Top Contributor of All Time 10+ Year Member



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.
8:13 am on Nov 10, 2012 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member



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.
 

Featured Threads

Hot Threads This Week

Hot Threads This Month