Hi all!
So, I have a website for a database which contains biological data (proteins). In this website, there is a text search page,
where the user can specify one or more search terms in order to get data from the database. The user can enter up to 5 search terms in each of the
available text boxes, as shown below
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>Text search</title>
<link rel="shortcut icon" type="image/x-icon" href="css/images/fav.ico">
<link rel ="stylesheet" href ="css/style.css">
<script type="text/javascript">
function setReadOnly(obj)
{
if(obj.value == "specific")
{
document.forms[0].mytext.style.backgroundColor = "#ffffff";
document.forms[0].mytext.readOnly = 0;
document.forms[0].mytext.value = "";
}
else
{
document.forms[0].mytext.style.backgroundColor = "#eeeeee";
document.forms[0].mytext.readOnly = 1;
document.forms[0].mytext.value = "50.00";
}
}
function addLoadEvent(func) {
var oldonload = window.onload;
if (typeof window.onload != 'function') {
window.onload = func;
} else {
window.onload = function() {
oldonload();
func();
}
}
}
function prepareInputsForHints() {
var inputs = document.getElementsByTagName("input");
for (var i=0; i<inputs.length; i++){
// test to see if the hint span exists first
if (inputs[i].parentNode.getElementsByTagName("span")[0]) {
// the span exists! on focus, show the hint
inputs[i].onfocus = function () {
this.parentNode.getElementsByTagName("span")[0].style.display = "inline";
}
// when the cursor moves away from the field, hide the hint
inputs[i].onblur = function () {
this.parentNode.getElementsByTagName("span")[0].style.display = "none";
}
}
}
// repeat the same tests as above for selects
var selects = document.getElementsByTagName("select");
for (var k=0; k<selects.length; k++){
if (selects[k].parentNode.getElementsByTagName("span")[0]) {
selects[k].onfocus = function () {
this.parentNode.getElementsByTagName("span")[0].style.display = "inline";
}
selects[k].onblur = function () {
this.parentNode.getElementsByTagName("span")[0].style.display = "none";
}
}
}
}
addLoadEvent(prepareInputsForHints);
</script>
</head>
<body>
<div align="center">
<table class="whole">
<tbody>
<tr>
<td> <?php include 'css/header.html';?> </td>
</tr>
<tr>
<td> <?php include 'css/navbar.php';?> </td>
</tr>
<form enctype="multipart/form-data" method="post" action="retrieve_text.php">
<tr>
<td>
<div align="center">
<p class="headcell"><br/>Text search</p>
<dl>
<dt>
Protein Name/Description
</dt>
<dd>
<input type="text" name="prot_name" size="35" />
<span class="hint">e.g. ompa_ecoli, transmembrane, porin, adhesion<span class="hint-pointer"> </span>
</dd>
<dt>
Gene name
</dt>
<dd>
<input type="text" name="prot_gene" size="35" />
<span class="hint">e.g. ompA, porB, oprF, PMSV_1498<span class="hint-pointer"> </span>
</dd>
<dt>
Organism name
</dt>
<dd>
<input type="text" name="org_name" size="35" />
<span class="hint">e.g. Escherichia coli, Vibrio sp. (strain Ex25)<span class="hint-pointer"> </span>
</dd>
<dt>
Organism taxonomy
</dt>
<dd>
<input type="text" name="taxid" size="35" />
<span class="hint">e.g. 595536, 1051646<span class="hint-pointer"> </span>
</dd>
<dt>
Cross-references
</dt>
<dd>
<input type="text" name="crossref" size="35" />
<span class="hint">e.g. F2HUT9, PF01389, IPR006665, 1A0S<span class="hint-pointer"> </span>
</dd>
<dt>
Literature references (PMID)
</dt>
<dd>
<input type="text" name="pmid" size="35" />
<span class="hint">e.g. 9294435, 17114266, 8759855<span class="hint-pointer"> </span>
</dd>
<dt>
Signal peptide information
</dt>
<dd>
<select name="signal">
<option value="ALL" selected>All available</option>
<option value="Experimentally verified">Experimentally verified</option>
<option value="Experimentally verified [by similarity]">Experimentally verified [by similarity]</option>
<option value="Prediction methods">SignalP 4.0</option>
<option value="No information">No information</option>
</select>
</dd>
<dt>
Perform search in
</dt>
<dd>
<select name="family">
<option value="0" selected>All families</option>
<?php
require_once("css/connection.php");
$query = "SELECT families.families_id, families.families_name FROM families";
$result = mysql_query($query) or die("<br><br><br>Please contact the administrator of the website.<br><br><br><br><br><br><br><br></td></div></tr>");
while ($line = mysql_fetch_row($result))
{echo "<option value=".$line[0].">".$line[1]."</option>\
";}
?>
</select>
</dd>
<dt>
Combine search criteria using
</dt>
<dd>
<input type="radio" name="operator" value="combined" checked /> AND
<input type="radio" name="operator" value="separate" /> OR
</dd>
<dt>
Incude protein fragments in the results
</dt>
<dd>
<input type="checkbox" class="check" name="fragment" checked />
</dd>
<dd>
<input type="hidden" name="order" value="id" />
</dd>
</dl>
</div>
</td>
</tr>
<tr>
<td><br>
<input type="submit" value="Perform search">
<input type="reset" value="Clear fields">
</td>
</tr>
</form>
<tr><td><br/></td></tr>
<tr>
<td height="40" class="note"><u><b><i>Note</i></b></u> : </b>
You can specify up to 5 search terms in each text field, provided that they are separated by a comma.</td>
</tr>
</tbody>
</table>
</div>
</body>
</html>
The retrieve_text.php page gets the data submitted by the user and fetches the results. Also, if the user clicks on 1 or more of the
checkboxes next to each record, the respective entries are downloaded as raw format archives.
However, because in some cases there were more than 200 results
I decided to implement pagination… I searched around and asked for help, and managed to get it as far as you see below.
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>Text search results</title>
<link rel="shortcut icon" type="image/x-icon" href="css/images/fav.ico">
<link rel ="stylesheet" href ="css/style.css">
</head>
<body>
<div align="center">
<table class="whole">
<tbody>
<!-- arxikos pinakas -->
<tr>
<td> <?php include 'css/header.html';?> </td>
</tr>
<tr>
<td> <?php include 'css/navbar.php';?> </td>
</tr>
<tr>
<td>
<div align="center">
<table class="included">
<tbody>
<?php
session_start();
require_once("css/connection.php");
print "POST_ARRAY:";print_r($_POST);print "<br><br>";
print "SESSION_CHOICE_ARRAY:";print_r($_SESSION['choice']);print "<br><br>";
/*<--- Download raw entries in zip form BEGIN--->*/
if ($_POST['submit'] == 'Retrieve')
{
if($count_selected<1000)
{
$add = rand();
$ids_selected=array_keys($_SESSION['choice']);
foreach ( $ids_selected as $entry_selected)
{
$file_fasta = 'FASTA_FILES/'.$entry_selected.'.fasta';
$file_fasta_all = "TEMP_FTP_FILES/".$add.".fasta";
$create_fasta = `cat $file_fasta >>$file_fasta_all`;chmod($file_fasta_all,0777);
}
$zip_fasta=`gzip $file_fasta_all`;
$final_fasta=$file_fasta_all.'.gz';
$file_xml_all = "TEMP_FTP_FILES/".$add.".xml";
$start_xml='<?xml version="1.0" encoding="utf-8"?>'."\
"."<Entries>\
";
$fh = fopen($file_xml_all, 'w');
fwrite($fh,$start_xml);
fclose($fh);
foreach ( $ids_selected as $entry_selected)
{
$file_xml = 'XML_DOWN_FILES/'.$entry_selected.'.xml';
$holdcontents = file_get_contents($file_xml);
$fh2 = fopen($file_xml_all, 'a');
fwrite($fh2,$holdcontents);
}
$end_xml = "</Entries>\
";
fwrite($fh2,$end_xml);
fclose($fh2);chmod($file_xml_all,0777);
$zip_xml=`gzip $file_xml_all`;
$final_xml=$file_xml_all.'.gz';
foreach ( $ids_selected as $entry_selected)
{
$file_flat = 'FLAT_FILES/'.$entry_selected.'.flat';
$file_flat_all = "TEMP_FTP_FILES/".$add.".flat";
$create_flat = `cat $file_flat>>$file_flat_all`; chmod($file_flat_all,0777);
}
$zip_flat=`gzip $file_flat_all`;
$final_flat=$file_flat_all.'.gz';
if(file_exists($final_fasta) && file_exists($final_flat) && file_exists($final_xml))
{
echo "<tr><td class=\\"headcell\\">Download selected entries</td></tr>\
";
echo "<tr><td class=\\"searches_cell\\"><br>Select one of the following formats:</td></tr>\
";
echo "<tr><br><td>";
echo "<ul>".
"<li><a href=\\"".$final_fasta."\\">FASTA format</a></li>".
"<li><a href=\\"".$final_flat."\\">Text format</a></li>".
"<li><a href=\\"".$final_xml."\\">XML format</a></li>".
"</ul>".
"</td></tr></table>\
";
}
}
else
{
echo "<div align=\\"center\\"><table>".
"<tr><td><div align=\\"center\\">".
"You selected ".$counter." entries for download, which is more than the allowed limit of 1000. <br>For large barch searches, please download and search the total database files from the <a target=_blank href = \\"download.php\\">download</a> page. ".
"<p>Go <a href=\\"javascript: window.history.go(-1);\\">back</a> to selection list</p>"; exit;
}
echo "<p>Go <a href=\\"javascript: window.history.go(-1);\\">back</a> to selection list</p>";
exit;
}
/*<--- Download raw entries in zip form END --->*/
function cleanQuery($string)
{
if (get_magic_quotes_gpc()) // prevents duplicate backslashes
{ $string = stripslashes($string); }
$string = mysql_real_escape_string($string);
$trimmed = trim($string);
$final=str_replace("%","\\%",$trimmed);
return $final;
}
$name=$gene=$organism=$taxonomy=$references=$transmembrane="";
$family=0;
$coverage=0;
$combination='combined';
$frag='';
if (isset($_REQUEST['prot_name'])) {$name = cleanQuery($_REQUEST['prot_name']);}
if (isset($_REQUEST['prot_gene'])) {$gene = cleanQuery($_REQUEST['prot_gene']);}
if (isset($_REQUEST['org_name'])) {$organism = cleanQuery($_REQUEST['org_name']);}
if (isset($_REQUEST['taxid'])) {$taxonomy = cleanQuery($_REQUEST['taxid']);}
if (isset($_REQUEST['crossref'])) {$references = cleanQuery($_REQUEST['crossref']);}
if (isset($_REQUEST['pmid'])) {$references_pmid = cleanQuery($_REQUEST['pmid']);}
if (isset($_REQUEST['operator'])) {$combination = $_REQUEST['operator'];}
if (isset($_REQUEST['fragment'])) {$frag='Y';}
if (isset($_REQUEST['family'])) {$family = $_REQUEST['family'];}
if (isset($_REQUEST['signal'])) {$signal = $_REQUEST['signal'];}
if ( !$name && !$gene && !$organism && !$taxonomy && !$references && !$references_pmid && $family==0 && $signal=="ALL")
{
echo "<div align=\\"center\\"><table>".
"<tr><td><div align=\\"center\\">".
"<br><br><br><br>No values in the text fields specified. ".
"Please go <a href = \\"text_search.php\\">back</a> to text search page.<br><br><br><br><br><br><br><br></td></div></tr>"; exit;
}
else
{
$wclause = array();
if($name!="")
{
if(strstr($name, ',', true))
{
$array_name = explode(",", $name);
$count_name = count ($array_name);
if ($count_name>5) {print "<br><br><br><br>You specified more than 5 terms in one or more text fields. Please go <a href = \\"text_search.php\\">back</a> to text search page and review your submission.<br><br><br><br><br><br><br><br>";}
$all_name='';
foreach ($array_name as $value_name) { $value_name=trim($value_name); $all_name = $all_name."protein.protein_name LIKE '%".$value_name."%' OR "; }
$final_name_addition = substr($all_name, 0, -4); // remove the ' OR ' in the end
$wclause[]= $final_name_addition;
}
else
{ $wclause[]="protein.protein_name LIKE '%".$name."%' "; }
}
if($gene!="")
{
if(strstr($gene, ',', true))
{
$array_genes = explode(",", $gene);
$count_genes = count ($array_genes);
if ($count_genes>5) {print "<br><br><br><br>You specified more than 5 terms in one or more text fields. Please go <a href = \\"text_search.php\\">back</a> to text search page and review your submission.<br><br><br><br><br><br><br><br>";}
$all_genes='';
foreach ($array_genes as $value_genes) { $value_genes=trim($value_genes); $all_genes = $all_genes."protein.protein_gene LIKE '%".$value_genes."%' OR "; }
$final_genes_addition = substr($all_genes, 0, -4); // remove the ' OR ' in the end
$wclause[]= $final_genes_addition;
}
else
{ $wclause[]="protein.protein_gene LIKE '%".$gene."%' "; }
}
if($organism!="")
{
if(strstr($organism, ',', true))
{
$array_organism = explode(",", $organism);
$count_organism = count ($array_organism);
if ($count_organism>5) {print "<br><br><br><br>You specified more than 5 terms in one or more text fields. Please go <a href = \\"text_search.php\\">back</a> to text search page and review your submission.<br><br><br><br><br><br><br><br>";}
$all_organism='';
foreach ($array_organism as $value_organism) { $value_organism=trim($value_organism); $all_organism = $all_organism."protein.protein_organism LIKE '%".$value_organism."%' OR "; }
$final_organism_addition = substr($all_organism, 0, -4); // remove the ' OR ' in the end
$wclause[]= $final_organism_addition;
}
else
{ $wclause[]="protein.protein_organism LIKE '%".$organism."%' "; }
}
if($taxonomy!="")
{
if(strstr($taxonomy, ',', true))
{
$array_taxonomy = explode(",", $taxonomy);
$count_taxonomy = count ($array_taxonomy);
if ($count_taxonomy>5) {print "<br><br><br><br>You specified more than 5 terms in one or more text fields. Please go <a href = \\"text_search.php\\">back</a> to text search page and review your submission.<br><br><br><br><br><br><br><br>";}
$all_taxonomy='';
foreach ($array_taxonomy as $value_taxonomy) { $value_taxonomy=trim($value_taxonomy); $all_taxonomy = $all_taxonomy."protein.protein_ncbi ='".$value_taxonomy."' OR "; }
$final_taxonomy_addition = substr($all_taxonomy, 0, -4); // remove the ' OR ' in the end
$wclause[]= $final_taxonomy_addition;
}
else
{ $wclause[]="protein.protein_ncbi ='".$taxonomy."' "; }
}
if($references!="")
{
if(strstr($references, ',', true))
{
$array_references = explode(",", $references);
$count_references = count ($array_references);
if ($count_references>5) {print "<br><br><br><br>You specified more than 5 terms in one or more text fields. Please go <a href = \\"text_search.php\\">back</a> to text search page and review your submission.<br><br><br><br><br><br><br><br>";}
$all_references='';
foreach ($array_references as $value_references) { $value_references=trim($value_references); $all_references = $all_references."reference.reference_code ='".$value_references."' OR "; }
$final_references_addition = substr($all_references, 0, -4); // remove the ' OR ' in the end
$wclause[]= $final_references_addition;
}
else
{ $wclause[]="reference.reference_code ='".$references."' "; }
}
if($references_pmid!="")
{
if(strstr($references_pmid, ',', true))
{
$array_references_pmid = explode(",", $references_pmid);
$count_references_pmid = count ($array_references_pmid);
if ($count_references_pmid>5) {print "<br><br><br><br>You specified more than 5 terms in one or more text fields. Please go <a href = \\"text_search.php\\">back</a> to text search page and review your submission.<br><br><br><br><br><br><br><br>";}
$all_references_pmid='';
foreach ($array_references_pmid as $value_references_pmid) { $value_references_pmid=trim($value_references_pmid); $all_references_pmid = $all_references_pmid."families.families_pubmed LIKE '%".$value_references_pmid."%' OR "; }
$final_references_pmid_addition = substr($all_references_pmid, 0, -4); // remove the ' OR ' in the end
$wclause[]= $final_references_pmid_addition;
}
else
{ $wclause[]="families.families_pubmed LIKE '%".$references_pmid."%' "; }
}
if($family>0) { $wclause[]="protein.protein_families_id ='".$family."' "; }
if($signal!="ALL" && $signal!="") { $wclause[]="protein.protein_sp_quality ='".$signal."' "; }
if($frag == "N") { $wclause[]="protein.protein_isfragment ='".$frag."' "; }
switch($combination)
{
case "combined":
$wclause_sql = implode(" AND ", $wclause); break;
case "separate":
$wclause_sql = implode(" OR ", $wclause); break;
}
//if(isset($_POST['sql']))
//{
//$query_total = "SELECT DISTINCT protein.protein_id, protein.protein_name, ".
// "protein.protein_seq_len, protein.protein_organism, protein_ncbi FROM protein ".
// "LEFT JOIN protein_reference ON protein_reference.prot_ref_protein_id = protein.protein_id ".
// "LEFT JOIN reference ON reference.reference_id = protein_reference.prot_ref_reference_id ".
// "LEFT JOIN families ON protein.protein_families_id = families.families_id ".
// "WHERE ".$_POST['sql'];
//}
//else
//{
$query_total = "SELECT DISTINCT protein.protein_id, protein.protein_name, ".
"protein.protein_seq_len, protein.protein_organism, protein_ncbi FROM protein ".
"LEFT JOIN protein_reference ON protein_reference.prot_ref_protein_id = protein.protein_id ".
"LEFT JOIN reference ON reference.reference_id = protein_reference.prot_ref_reference_id ".
"LEFT JOIN families ON protein.protein_families_id = families.families_id ".
"WHERE ".$wclause_sql;
//}
print "QUERY: $query_total<br>";
$text_result = mysql_query($query_total) or die("<br><br><br><br>Non-acceptable query. Please go <a href = \\"text_search.php\\">back</a> to text search page.<br><br><br><br><br><br><br><br></td></div></tr>");
$number_results = mysql_num_rows($text_result);
if ($number_results==0)
{ echo "<tr><td colspan=\\"5\\"><br><br><br><br><i>No records matching your query were found<br><br><br><br><br><br><br><br><br><br></i></td></tr>\
"; }
else
{
// number of rows to show per page
$rowsperpage = 25;
// find out total pages
$totalpages = ceil($number_results / $rowsperpage);
// get the current page or set a default
if (isset($_GET['currentpage']) && is_numeric($_GET['currentpage'])) {
// cast var as int
$newpage = (int)$_GET['currentpage'];
} else {
// default page num
$newpage = 1;
} // end if
// modify the requested page based on any pagination form buttons, set/clear any remembered session checkbox data
$page = isset($_POST['page']) ? trim($_POST['page']) : false;
if($page){
// values of <<, <, x, >, or >>
switch($page){
case '<< First':
$newpage = 1;
break;
case '< Previous':
$newpage--;
break;
case 'Next >':
$newpage++;
break;
case 'Last >>':
$newpage = $totalpages;
break;
default:
// not one of the symbols, should be a number
$page = intval($page);
if($page > 0){
$newpage = $page;
}
break;
}
// set or clear the state of the check boxes in $_SESSION['choice']
// $_SESSION['ids'] = array of ids on the page that submitted
foreach($_SESSION['ids'] as $key){
if(isset($_POST['choice'][$key])){
$_SESSION['choice'][$key] = 1;
} elseif (isset($_SESSION['choice'][$key])) {
unset($_SESSION['choice'][$key]);
}
}
header("location: {$_SERVER['SCRIPT_NAME']}?currentpage=$newpage"); // clear post data
exit;
}
// if current page is greater than total pages...
if ($newpage > $totalpages) {
// set current page to last page
$newpage = $totalpages;
} // end if
// if current page is less than first page...
if ($newpage < 1) {
// set current page to first page
$newpage = 1;
} // end if
/****** build the pagination links ******/
// range of num links to show
$range = 3;
$links = '';
// if not on page 1, don't show back links
if ($newpage > 1) {
// show << link to go back to page 1
$links .= "<input type='submit' name='page' value='<< First'>";
// show < link to go back 1 page
$links .= "<input type='submit' name='page' value='< Previous'>";
} // end if
// loop to show links to range of pages around current page
for ($x = ($newpage - $range); $x < (($newpage + $range) + 1); $x++) {
// if it's a valid page number...
if (($x > 0) && ($x <= $totalpages)) {
// if we're on current page...
if ($x == $newpage) {
// 'highlight' it but don't make a link
$links .= " [<b>$x</b>] ";
// if not current page...
} else {
$links .= "<input type='submit' name='page' value='$x'>";
} // end else
} // end if
} // end for
// if not on last page, show forward and last page links
if ($newpage != $totalpages) {
// echo forward link for next page
$links .= "<input type='submit' name='page' value='Next >'>";
// echo forward link for lastpage
$links .= "<input type='submit' name='page' value='Last >>'>";
} // end if
/****** end build pagination links ******/
/****** get the actual database content for the requested page ******/
// the offset of the list, based on current page
$offset = ($newpage - 1) * $rowsperpage;
/* LIMITED SQL QUERY */
$query_limited = $query_total." LIMIT $offset, $rowsperpage";
$result_limited = mysql_query($query_limited);
echo "<form name=\\"srs_form\\" action='?currentpage=$newpage' method='post'>";
$counter_color=0;
echo "<tr><td class=\\"headcell\\" colspan=\\"6\\">Text search results</td></tr><tr><td><br></td></tr>\
";
echo "<tr>\
";
echo "<td width=\\"10%\\" class=\\"browse_cell\\">Select entries</td>".
"<td width=\\"15%\\" class=\\"browse_cell\\">View Entry</td>".
"<td width=\\"30%\\" class=\\"browse_cell\\">Description</td>".
"<td width=\\"33%\\" class=\\"browse_cell\\">Organism</td><td width=\\"12%\\" class=\\"browse_cell\\">Length</td>";
echo "</tr>\
";
$ids_this_page = array(); // a list of the checkbox id's on this page
while ($list = mysql_fetch_assoc($result_limited))
{
echo "<tr>\
";
$counter++;
if ($counter % 2) {$colour_class="browse_cell_change1";}
else {$colour_class="browse_cell_change2";}
$code = $list['protein_id'];
$name_cell = $list['protein_name'];
$length_cell = $list['protein_seq_len'];
$organism_cell = $list['protein_organism'];
$taxonomy_cell = $list['protein_ncbi'];
$checked = isset($_SESSION['choice'][$list['protein_id']]) ? " checked='checked'" : '';
$ids_this_page[] = $list['protein_id'];
echo "<td width=\\"10%\\" class=".$colour_class."><input type='checkbox' name='choice[{$list['protein_id']}]' value=".$list['protein_id']."><br />";
echo "<td width=\\"15%\\" class=".$colour_class."_left><a href=\\"entry.php?code=".$code."\\">DBID: ".$code."</a>";
echo "<td width=\\"30%\\" class=".$colour_class."_left>".$name_cell."</td>\
";
echo "<td width=\\"33%\\" class=".$colour_class."_left><a href=\\"retrieve_text.php?taxid=".$taxonomy_cell."\\">".$organism_cell."</a>";
echo "<td width=\\"12%\\" class=".$colour_class.">".$length_cell."</td>\
";
echo "</tr>\
";
}
$_SESSION['ids'] = $ids_this_page;
$total_selected=count($_SESSION['choice']);
echo "<tr><td><br><input type = \\"submit\\" value = \\"Retrieve\\"></td>";
echo "<td colspan='4'><br>".$links."<br/></td></tr>";
echo "<tr><td><input type=\\"hidden\\" name=\\"sql\\" value=\\"".$wclause_sql."\\"></td></tr>";
echo "</form>";
}
}
?>
</tbody>
</table>
</div>
</td>
</tr>
</tbody>
</table>
</div>
</body>
</html>
My problem is that, although the state of the selected entries is maintained as the user browses from page to page, this is NOT the
case for the $wclause_sql part, which is actually the “WHERE” clause of the sql query. So I can only see the first page, and not the others…
I used sessions in order to keep track which records are selected by the user, so as when the user finally hits ‘Retrieve’
he can get all the desired entries, but I cannot transfer the $wclause and therefore, in all other pages except page 1, the SQL query is empty, thus no
entries are shown.
What am I missing here?