homepage Welcome to WebmasterWorld Guest from 54.167.182.201
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

    
filtering mysql queries
can't quite work out how this should be written
whatson

WebmasterWorld Senior Member 10+ Year Member



 
Msg#: 4516699 posted 7:21 am on Nov 7, 2012 (gmt 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?

 

swa66

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



 
Msg#: 4516699 posted 8:41 am on Nov 7, 2012 (gmt 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.

jadebox

10+ Year Member



 
Msg#: 4516699 posted 6:20 pm on Nov 7, 2012 (gmt 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.

whatson

WebmasterWorld Senior Member 10+ Year Member



 
Msg#: 4516699 posted 6:38 pm on Nov 7, 2012 (gmt 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?

omoutop

WebmasterWorld Senior Member 10+ Year Member



 
Msg#: 4516699 posted 7:08 am on Nov 8, 2012 (gmt 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

whatson

WebmasterWorld Senior Member 10+ Year Member



 
Msg#: 4516699 posted 7:58 pm on Nov 8, 2012 (gmt 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?

brotherhood of LAN

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



 
Msg#: 4516699 posted 9:25 pm on Nov 8, 2012 (gmt 0)

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

10+ Year Member



 
Msg#: 4516699 posted 9:43 pm on Nov 8, 2012 (gmt 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.

whatson

WebmasterWorld Senior Member 10+ Year Member



 
Msg#: 4516699 posted 12:30 am on Nov 9, 2012 (gmt 0)

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

whatson

WebmasterWorld Senior Member 10+ Year Member



 
Msg#: 4516699 posted 8:28 am on Nov 9, 2012 (gmt 0)

What about search then? How is that validated?

omoutop

WebmasterWorld Senior Member 10+ Year Member



 
Msg#: 4516699 posted 12:37 pm on Nov 9, 2012 (gmt 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

whatson

WebmasterWorld Senior Member 10+ Year Member



 
Msg#: 4516699 posted 8:03 pm on Nov 9, 2012 (gmt 0)

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

swa66

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



 
Msg#: 4516699 posted 10:51 pm on Nov 9, 2012 (gmt 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.

whatson

WebmasterWorld Senior Member 10+ Year Member



 
Msg#: 4516699 posted 8:13 am on Nov 10, 2012 (gmt 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.

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