ktsirig Posted May 23, 2012 Share Posted May 23, 2012 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>\n";} ?> </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"?>'."\n"."<Entries>\n"; $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>\n"; 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>\n"; echo "<tr><td class=\"searches_cell\"><br>Select one of the following formats:</td></tr>\n"; 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>\n"; } } 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>\n"; } 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>\n"; echo "<tr>\n"; 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>\n"; $ids_this_page = array(); // a list of the checkbox id's on this page while ($list = mysql_fetch_assoc($result_limited)) { echo "<tr>\n"; $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>\n"; 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>\n"; echo "</tr>\n"; } $_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? Link to comment https://forums.phpfreaks.com/topic/263004-help-with-pagination/ Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.