Filter MySQL Select results

Admin · Oct 31, 2013

What's the best way to filter MySQL Select results?

Basically I have a PHP page that has return some results but now i want to filter them further using radio button selection options or check-boxes for different fields

many thanks

Admin

Aegean · Oct 31, 2013

I dont know if I am understanding you correctly, but if I am then these are just AND conditions.

So, lets say we have the following base query:

SELECT * FROM outdoors WHERE type = 'bikes';

This would send back all bikes from the outdoors database, but to filter it further to, lets say road bikes, it would be:

SELECT * FROM outdoors WHERE type = 'bikes' AND kind = 'road';

You can utilise a user check-box to filter results in this way. Apologies in advance if I have misunderstood you.

Admin · Oct 31, 2013

Thanks for the response. I was looking for some sample code of the most efficient way to create the AND statement you describe, do I have to build a new page for each one or can you pass the filter over your existing SELECT statement in some way?

Many thanks

Admin

Jamie101 · Oct 31, 2013

On my phone so can't go in to great detail, but it might be easier to explain to you if you explain exactly what you're trying to do.

As mentioned above, the WHERE clause is all you need really. Or you could have something like:

Select * from customers where creditLimit between 1000 and 2000;

Look up SQL on W3Schools, it explains pretty much everything. Only thing is that MySQL is a tiny tiny bit different syntax at times.

SQL / MySQL is one of my favourite languages really, since it's nearly like English!

Sent from my GT-I9300 using Tapatalk 2

Skinner · Oct 31, 2013

For stuff like this, I use an php if selector,

$radio = defang($_POST['rdBox']);
if($radio==?){
$q = "select a, b, c from x where y = ?;";
} else {
$q = "select a, b, c from x where y = ? and z = ??;";
}

then load the query as normal

Admin · Nov 1, 2013

thanks guys, sorted it.

Admin

monaghan · Nov 1, 2013

Also, be aware of SQL injection if you use any of the POST/GET values in the SQL query.

If you have a small data set, then select * is OK, however, if there s a lot of data, just select the actual data you intend to use on the page and make use of the LIMIT clause so you are not pulling back data that will never actually be displayed, this will keep the server load in check.