20i Domains

Filter MySQL Select results

Discussion in 'Scripts and Coding' started by Admin, Oct 31, 2013.

Thread Status:
Not open for further replies.
  1. Admin

    Admin Administrator Staff Member

    Joined:
    Jun 2004
    Posts:
    10,981
    Likes Received:
    378
    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
     
  2. Domain Forum

    Acorn Domains Elite Member

    Joined:
    1999
    Messages:
    Many
    Likes Received:
    Lots
    articles.co.uk
     
  3. Aegean Greece

    Aegean Active Member

    Joined:
    Feb 2011
    Posts:
    751
    Likes Received:
    16
    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.
     
  4. Admin

    Admin Administrator Staff Member

    Joined:
    Jun 2004
    Posts:
    10,981
    Likes Received:
    378
    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
     
  5. Jamie101 Netherlands

    Jamie101 Well-Known Member

    Joined:
    Jan 2012
    Posts:
    1,073
    Likes Received:
    16
    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
     
  6. Skinner

    Skinner Well-Known Member

    Joined:
    Jul 2008
    Posts:
    4,660
    Likes Received:
    139
    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
     
  7. Admin

    Admin Administrator Staff Member

    Joined:
    Jun 2004
    Posts:
    10,981
    Likes Received:
    378
    thanks guys, sorted it.

    Admin
     
  8. monaghan United Kingdom

    monaghan Moderator Staff Member

    Joined:
    May 2007
    Posts:
    2,103
    Likes Received:
    71
    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.
     
  9. Admin

    Admin Administrator Staff Member

    Joined:
    Jun 2004
    Posts:
    10,981
    Likes Received:
    378
    Great info, thanks

    Admin
     
Thread Status:
Not open for further replies.