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 SUZ12So 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 ERNow 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 IHNWhat 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.
