Welcome to WebmasterWorld Guest from 107.20.54.98

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)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Apr 21, 2002
posts: 1541
votes: 0


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)

Senior Member

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

joined:Aug 7, 2003
posts:4783
votes: 0


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)

Junior Member

10+ Year Member

joined:June 24, 2004
posts:145
votes: 0


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)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Apr 21, 2002
posts: 1541
votes: 0


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)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Oct 15, 2004
posts:941
votes: 0


<=, => 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)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Apr 21, 2002
posts: 1541
votes: 0


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)

Moderator from GB 

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

joined:Jan 30, 2002
posts:4842
votes: 1


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)

Junior Member

10+ Year Member

joined:June 24, 2004
posts:145
votes: 0


"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)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Apr 21, 2002
posts: 1541
votes: 0


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)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Apr 21, 2002
posts: 1541
votes: 0


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

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Oct 15, 2004
posts:941
votes: 0


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)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Apr 21, 2002
posts: 1541
votes: 0


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

Senior Member

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

joined:Aug 7, 2003
posts:4783
votes: 0


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)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:Apr 21, 2002
posts: 1541
votes: 0


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.