Hi

I have a problem that's been bugging me. I created a page to display all my data. At the same time, I also incorporated 'filters' by using dropdown menu selection. Code snippet attached:-

<?php

    session_start();
    require("db.php");

    if (isset($_SESSION['SESS_CHIA_LOGGEDIN']) == FALSE)
    {
        header("Location: " . $config_basedir);
    }
    else if( $_SESSION['SESS_CHIA_GROUP'] != 1 )
    {
        header("Location: " . $config_basedir);
    }

    $start_time = microtime(true);

    if (isset($_GET['org']) == TRUE) {
        $org_id = $_GET['org'];
    } else {
        $org_id = 0;
    }

    if (isset($_GET['assembly']) == TRUE) {
        $aid = $_GET['assembly'];
    } else {
        $aid = 0;
    }

    if (isset($_GET['proj']) == TRUE) {
        $proj_id = $_GET['proj'];
    } else {
        $proj_id = "ALL";
    }

    require("header.php");

    $sql = "SELECT COUNT(*) FROM ".$dbdatabase.".libraries;";
    $result = mysql_query($sql) or die("Could not connect: " . mysql_error());
    $row = mysql_fetch_array($result);

    echo "Total Number of Libraries: " . $row['0'] . "<p/>";

    echo "<table border = 1>\n";
    // Header
    echo "<tr>\n";
    echo "<th rowspan=2>Library</th>\n";
    echo "<th>Organism</th>\n";
    echo "<th>Assembly</th>\n";
    echo "<th>Project</th>\n";
    echo "<th rowspan=2>Description</th>\n";
    echo "<th rowspan=2 colspan=2>Action</th>";
    echo "</tr>\n";

    echo "<tr>\n";
    //-------------------------------------------- Filter 1 -----------------------------------------
    $sql = "SELECT * FROM ".$dbdatabase.".organisms;";
    $org_result = mysql_query($sql) or die("Could not connect: " . mysql_error());
    $numrows = mysql_num_rows($org_result) or die("Could not connect: " . mysql_error());

    echo "<td><select name=\"organism\" onchange=\"location = this.options[this.selectedIndex].value;\">\n";
    if ($org_id == 0)
    {
        echo "<option selected value='" . $config_basedir . "viewLibraries.php"'>-- Show all Organisms --</option> <br />";
    }
    else
    {
        echo "<option value='" . $config_basedir . "viewLibraries.php"'>-- Show all Organisms --</option> <br />";
    }

    for ($i = 0; $i < $numrows; $i++)
    {
        $org_row = mysql_fetch_assoc($org_result);
        if ($org_row['id'] == $org_id)
        {
            echo "<option selected value='" . $config_basedir . "viewLibraries.php?org=" . $org_row['id'] . "'>" . $org_row['name'] . "</option> <br />";
        }
        else
        {
            echo "<option value='" . $config_basedir . "viewLibraries.php?org=" . $org_row['id'] . "'>" . $org_row['name'] . "</option> <br />";
        }
    }

    //-------------------------------------------- Filter 2 -----------------------------------------
    $sql = "SELECT * FROM ".$dbdatabase.".assembly;";
    $ass_result = mysql_query($sql) or die("Could not connect: " . mysql_error());
    $numrows = mysql_num_rows($ass_result) or die("Could not connect: " . mysql_error());

    echo "<td><select name=\"assembly\" onchange=\"location = this.options[this.selectedIndex].value;\">\n";
    if ($aid == 0)
    {
        echo "<option selected value='" . $config_basedir . "viewLibraries.php'>-- Show all Assemblies --</option> <br />";
    }
    else
    {
        echo "<option value='" . $config_basedir . "viewLibraries.php'>-- Show all Assemblies --</option> <br />";
    }

    for ($i = 0; $i < $numrows; $i++)
    {
        $ass_row = mysql_fetch_assoc($ass_result);
        if ($ass_row['id'] == $aid)
        {
            echo "<option selected value='" . $config_basedir . "viewLibraries.php?assembly=" . $ass_row['id'] . "'>" . $ass_row['name'] . "</option> <br />";
        }
        else
        {
            echo "<option value='" . $config_basedir . "viewLibraries.php?assembly=" . $ass_row['id'] . "'>" . $ass_row['name'] . "</option> <br />";
        }
    }

    //-------------------------------------------- Filter 3 -----------------------------------------
    $sql = "SELECT DISTINCT name FROM ".$dbdatabase.".projects;";
    $proj_result = mysql_query($sql) or die("Could not connect: " . mysql_error());
    $numrows = mysql_num_rows($proj_result) or die("Could not connect: " . mysql_error());

    echo "<td><select name=\"projects\" onchange=\"location = this.options[this.selectedIndex].value;\">\n";
    if ( strcmp($proj_id, "ALL") == 0)
    {
        echo "<option selected value='" . $config_basedir . "viewLibraries.php'>-- Show all Projects --</option> <br />";
    }
    else
    {
        echo "<option value='" . $config_basedir . "viewLibraries.php'>-- Show all Projects --</option> <br />";
    }

    for ($i = 0; $i < $numrows; $i++)
    {
        $proj_row = mysql_fetch_assoc($proj_result);
        if ( strcmp($proj_row['name'], $proj_id) == 0 )
        {
            echo "<option selected value='" . $config_basedir . "viewLibraries.php?proj=" . $proj_row['name'] . "'>" . $proj_row['name'] . "</option> <br />";
        }
        else
        {
            echo "<option value='" . $config_basedir . "viewLibraries.php?proj=" . $proj_row['name'] . "'>" . $proj_row['name'] . "</option> <br />";
        }
    }

    echo "</tr>\n";

I omitted the remaining code for now since those will mainly display the data in tabular form.

So in the above, three filters are employed - Organism Filter, Assembly Filter and Project Filter. Let's assume the data below is the full unfiltered view (omitted unimportant columns):-

Library    Organism         Assembly        Projects
         -- Show All --   -- Show All --   -- Show All --
    A         Human             hg18                ER 
    B         Human             hg18                AR
    C         Human             hg17                ER
    D         Human             hg18                IHN
    E         Mouse               mm8               CTCF
    F         Mouse               mm8               SALL4
    G         Mouse               mm9               SALL4
    H         Mouse               mm9               SUZ12

So when I filter by "ER", the page only displays Library A and C. Similarly effect applies for the other two as well. The problem is when I want to filter by more than one column.

What happens is that say firstly, I filter by "ER" and get A and C.

Library    Organism         Assembly        Projects
           -- Show All --   -- Show All --      -- ER --
    A          Human             hg18                ER 
    C          Human             hg17                ER

Now when I filter by assembly, say "hg18", the result ends up:-

Library    Organism         Assembly        Projects
           -- Show All --     -- hg18 --      -- Show All --
    A          Human             hg18                ER 
    B          Human             hg18                AR
    D          Human             hg18                IHN

What happen is that the first filter got cleared out. In my code above, I did retrieve the value but when I echo it out, its reverted to default i.e. previous Project selection was not posted when I selected Assembly filter value. I'm guessing its something to do with the code piece:-

onchange=\"location = this.options[this.selectedIndex].value;\"

Anyone knows what changes I need to be done to make it work the way I want?
Thanks in advance.

I'm guessing its something to do with the code piece:-

onchange=\"location = this.options[this.selectedIndex].value;\"

Anyone knows what changes I need to be done to make it work the way I want?
Thanks in advance.

Yes, this is where the issue lies. The onchange attribute of each of your select boxes is set to redirect to a page as soon as an option is selected. The page is determined by the selected option's value attribute. Since you hard code the value you want that selection to "post" (it is actually a GET variable the way you are doing it) , when you immediately redirect the user as soon as he chooses an option, the only value that will be read is the value in the GET string of the selected option.

You should instead put all three select boxes in a single <form> element, and set the method to 'GET'. The action of the <form> should be left blank (if you are posting back to same page) or set to the name of the script that is doing the processing. Each of your 'value' attributes in the select options should be changed to the actual value you want to post, not a full URL.

Finally, in order to make the form refresh with the options each time a choice is made, change all your select 'onchange' attributes to :

<select onchange='this.form.submit()' >

Hi

Sorry for the late reply. Been busy with other issues -_-

Anyway, tried your idea and worked.
Now just to make the code neater :)

Thanks!

This article has been dead for over six months. Start a new discussion instead.