SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Zealot
    Join Date
    Jul 2008
    Posts
    190
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    creating a filter on a query

    Hey I have created a query that displays all records within a table. I am hoping to create some links next to this query that will allow the user to filter these records. How would I go about doing this? This is my query below. I was hoping to have links that would be http://www.mysite.com/master.php?Gen...sus&Division=4 where master.php is the page I am currently working on and Genus and Division are fields in the table. I know this is possible but just don't know how to start the process! Here is my current query:

    PHP Code:
    <?php
    include '../library/config.php';
    include 
    '../library/opendb.php';

    $query  "SELECT Product_ID, Genus, Common_name, Description FROM products ORDER BY Product_ID DESC ";
    $result mysql_query($query) or die('Error, query failed');
    $numofrows mysql_num_rows($result);

    for(
    $i 0$i $numofrows$i++) {
        
    $row mysql_fetch_array($result); 
        if(
    $i 2) { 
            echo 
    "<tr bgcolor='lightgrey'>";
        } else { 
            echo 
    "<tr bgcolor='white'>";
        }
        echo 
    "<td>".$row['Product_ID']."</td><td>".$row['Genus']."</td><td>".$row['Common_name']."</td><td>".substr($row['Description'], 045)."</td>";
        echo 
    "</tr>";
    }
    ?>
    Thanks
    Russ

  2. #2
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    The process would be:

    create links for each Genus by collecting all the Genii(?) from your database
    iterate through the results showing them as links

    Someone clicks on the link:
    /master.php?Genus=Lily&Division=4

    master.php then detects that $_GET['Genus'] has a value, and that value is permitted, or makes some security check on the value of Genus, and then pipes that into the sql statement like this:
    PHP Code:
    $genus_query  "SELECT Product_ID
    , Genus
    , Common_name
    , Description
    FROM products 
    where
    Genus = '" 
    .$_GET['Genus']. "' 
    ORDER BY Product_ID DESC "

    which you then display.
    Last edited by Cups; Dec 30, 2008 at 05:09. Reason: quoted string correctly

  3. #3
    SitePoint Zealot
    Join Date
    Jul 2008
    Posts
    190
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This looks perfect. I didn't realise you could use GET for this kind of function. One thing though. When this page is initially loaded up, simply as master.php, how would I set the query so that it shows all Entries?

  4. #4
    SitePoint Wizard silver trophy kyberfabrikken's Avatar
    Join Date
    Jun 2004
    Location
    Copenhagen, Denmark
    Posts
    6,157
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Note that when you use an externallly provided input inside an SQL-query, you should make sure to escape it. If you don't, a malicious user cut use it to hack your site, through SQL-injection (Google that). So, it should be:

    PHP Code:
    $genus_query  "SELECT Product_ID
    , Genus
    , Common_name
    , Description
    FROM products 
    where
    Genus = '" 
    .mysql_real_escape_string($_GET['Genus']). "' 
    ORDER BY Product_ID DESC "


  5. #5
    SitePoint Wizard silver trophy kyberfabrikken's Avatar
    Join Date
    Jun 2004
    Location
    Copenhagen, Denmark
    Posts
    6,157
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by slaterino View Post
    One thing though. When this page is initially loaded up, simply as master.php, how would I set the query so that it shows all Entries?
    You can build the query up conditionally, eg.:

    PHP Code:
    $genus_query  "SELECT Product_ID
    , Genus
    , Common_name
    , Description
    FROM products"
    ;
    if (isset(
    $_GET['Genus'])) {
      
    $genus_query .= " where Genus = '" mysql_real_escape_string($_GET['Genus']) . "'" ;
    }
    $genus_query .= " ORDER BY Product_ID DESC "

  6. #6
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    and that value is permitted, or makes some security check on the value of Genus
    As Kyberfabrikken has explained, you can choose to make sure no dangerous data is fed to your query, or, you could check that the $_GET['Genus'] value has not been tampered with by checking it against a "white-list" of permitted Genera - using something like this:

    PHP Code:
    // lets say this was passed
    $_GET['Genus'] = "Rose";

    // and this is the entire list of Genera you allow
    $genera = array( 
    "Narcissus",
    "Lily",
    );


    if( ! 
    in_array$_GET['Genus'],$genera ) ){

    echo 
    "Incorrect Genus passed";
    // or do default select

    }else{
    // go ahead and build your query


    Which way you choose to go might depend on how large your list is, and/or how often it changes.

    But if you are going to show the list of Genera on the page again, then you won't have wasted a database query to generate the $genera array.

    Either way the lesson is to never blindly use information coming from the web, even if it was you that generated the original html. It is so easy to tamper with it.
    Last edited by Cups; Dec 30, 2008 at 09:03. Reason: bah, http://encarta.msn.com/dictionary_1861674928/genus.html genera

  7. #7
    SitePoint Zealot
    Join Date
    Jul 2008
    Posts
    190
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hey, thanks for all the help. One more question mind; I am using the script above which is working fine for all the links except the main page where I am hoping to show all entries. Should there be an else statement somewhere in the query to make this work? I have tried the following but this doesn't work at all, is there a better way of doing it?

    PHP Code:
    $genus_query  "SELECT Product_ID, Genus, Common_name, Description FROM products";
    if (isset(
    $_GET['Genus'])) {
    $genus_query .=" WHERE Genus = '" .mysql_real_escape_string($_GET['Genus']). "'";
    } else {
    $genus_query .=" WHERE Genus = " " ";
    }
    $genus_query .="ORDER BY Product_ID DESC"

  8. #8
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    PHP Code:
    else {
    $genus_query .=" WHERE Genus = " " ";

    That is redundant, remove it.

    if Genus has not been submitted you want to end up with:

    $genus_query = "SELECT Product_ID, Genus, Common_name, Description FROM products";
    $genus_query .="ORDER BY Product_ID DESC";

    2 things.

    Echo $genus_query onto your page to figure out what could be going wrong.

    That'll likely show you that the sql query:

    "SELECT Product_ID, Genus, Common_name, Description FROM productsORDER BY Product_ID DESC";

    Is incorrect because you dont have a space between "products" and "ORDER BY".

    When you echo out the sql query - copy and paste it into your mysql manager in order to check the validity of your php-assembled query.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •