homepage Welcome to WebmasterWorld Guest from 54.167.144.4
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




msg:4516701
 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




msg:4516716
 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




msg:4516835
 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




msg:4516844
 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




msg:4517035
 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




msg:4517288
 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




msg:4517307
 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




msg:4517310
 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




msg:4517344
 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




msg:4517457
 8:28 am on Nov 9, 2012 (gmt 0)

What about search then? How is that validated?

omoutop




msg:4517495
 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




msg:4517602
 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




msg:4517642
 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




msg:4517736
 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