Re: Pagination need help again

From:	M. Sokolewicz	Date:	Sat, 13 Nov 2004 15:52:21 +0000
Subject:	Re: Pagination need help again
References:	1 2	Groups:	php.general
Request:	Send a blank email to php-general+get-201920@lists.php.net to get a copy of this message

Matthew Weier O'Phinney wrote:

* Scott McWhite <smcwhite@mchsi.com>:
I'm using an HTML search form that passes the "searchterm" to a php file. In my case the "searchterm" can have 1000s of records in my database, so I implemented a limit which displays 20 records per page. The pagination function works fine with one exception. Example of my problem: searchterm typed in the html search form equals "ford", in the database there are 2000 fords, the first page displays 20 fords and then the next page forgets that we are searching "fords" and displays the equivalent to a wildcard "%" search and displays accordingly.
I use PEAR's Pager class for this sort of thing. It's highly configurable -- which means it may take a few tries to get it working with your own code, but it *will* work, eventually. Browse for the Pager class at http://pear.php.net -- look in the HTML category. however, like most PEAR projects it's heavily bloated, and if you only need partial functionality, your script will be faster when you write it yourself.

Back to the question. I've modified the code for you a bit, so the search-args can also be sent via GET and thus can be embedded in the next-page URL. I've also removed/recoded a few minor things, all with comments around it explaining why that was done. ---[ code ]--- <?php // change to _REQUEST so we can pick from both GET and POST $searchterm = addslashes($_REQUEST['searchterm']); $searchterm1 = addslashes($_REQUEST['searchterm1']); $searchterm2 = addslashes($_REQUEST['searchterm2']); // don't surpress warnings here. They're never issued anyway, so what's the use? mysql_pconnect("localhost", "root", "") or die("error -- can't connect to server"); // what's the persistent connection for? mysql_select_db("list") or die ("error -- can't connect to DB"); if(!isset($_GET['page'])){ $page = 1; } else { $page = $_GET['page']; } // Define the number of results per page $max_results = 20; // Figure out the limit for the query based // on the current page number. $from = (($page * $max_results) - $max_results); // Perform MySQL query on only the current page number's results $sql = "SELECT * FROM parts WHERE model LIKE '%".$searchterm."%' AND seller LIKE '%".$searchterm1."%' AND description like '%".$searchterm2."%' ORDER BY model, price, quantity LIMIT $from, $max_results"; echo '<body bgcolor = "#dfefff">'; $result = mysql_query($sql) or die(mysql_error()); $numofrows = mysql_num_rows($result); echo "<TABLE BORDER=\"0\">\n"; echo "<TR bgcolor=\"3366cc\"><TD align=\"left\">Seller</TD></TD><TD align=\"left\">Model Number</TD><TD align=\"left\">Manufacturer</TD><TD align=\"left\">Description</TD><TD align=\"left\">Quantity</TD><TD align=\"left\">Price</TD><TD align=\"left\">Condition</TD><TD align=\"left\">Email Address</TD><TD align=\"left\">AIM</TD></TR>\n"; for($i = 0; $i < $numofrows; $i++) { $row = mysql_fetch_array($result); //get a row from our result set if($i % 2) { //this means if there is a remainder echo "<TR bgcolor = \"eaeaea\">\n"; } else { //if there isn't a remainder we will do the else echo "<TR bgcolor = \"d5eaff\">\n"; } echo "<TD align = left>".$row['seller']."</a></TD></a><TD align = left>".$row['model']."</TD><TD align = left>".$row['manufacturer']."</TD><TD align = left>".$row['description']."</TD><TD align = left>".$row['quantity']."</TD><TD align = left>".$row['price']."</TD><TD align = left>".$row['condition']."</TD><TD align = left><a href = \"mailto:".$row['email']."\">".$row['email']."</TD><TD align = left><a href = \"aim:goim?message = Hi.+Are+you+there?&screenname = ".$row['aim']."\">AIM Link</TD></a>\n"; echo "</TR>\n"; } //now let's close the table and be done with it echo "</TABLE>\n"; // what are you doing here? you've already looped trough the entire resultset before! // This code will never get executed, since mysql_fetch_array returns false (because it // looped trough all results already), and thus it won't execute anything in the loop. /****************************************** while($row = mysql_fetch_array($result)){ // Build your formatted results here. $variable1 = $row["manufacturer"]; $variable2 = $row["seller"]; $variable3 = $row["model"]; $variable4 = $row["description"]; $variable5 = $row["email"]; //results echo "<tr>"; echo "<td class='body_1'>$variable1</td>"; echo "<td class='body_1'>$variable2</td>"; echo "<td class='body_1'>$variable3</td>"; echo "<td class='body_1'>$variable4</td>"; echo "<td class='body_1' style='border-right: 0px;'>$variable5</td>"; echo "</tr>"; } ******************************************/ // Figure out the total number of results in DB: $total_results = mysql_result(mysql_query("SELECT COUNT(*) FROM parts WHERE model LIKE '%".$searchterm."%' AND seller LIKE '%".$searchterm1."%' AND description LIKE '%".$searchterm2."%'"),0); // you don't need to alias the result-col, it's not used anyway // Figure out the total number of pages. Always round up using ceil() // make sure to ignore 0 results! if($max_results < 1) { $total_pages = 0; } else { $total_pages = ceil($total_results / $max_results); } // Page Number Hyperlinks echo "<tr><td colspan = 4 align = center>Select a Page "; // Previous Link if($page > 1){ $prev = ($page - 1); echo "<a href = \"".$_SERVER['PHP_SELF']."?page=$prev&searchterm=$searchterm&searchterm1=$searchterm1&searchterm2=$searchterm2\"><<Previous</a> "; } for($i = 1; $i < = $total_pages; $i++){ if(($page) = = $i){ echo "$i "; } else { echo "<a href = \"".$_SERVER['PHP_SELF']."?page = $i\">$i</a> "; } if(($i + 30) % 30 = = 0) { echo '</td></tr><tr><td colspan = 4 align = center>'; } } // Next Link if($page < $total_pages){ $next = ($page + 1); echo "<a href = \"".$_SERVER['PHP_SELF']."?page=$next&searchterm=$searchterm&searchterm1=$searchterm1&searchterm2=$searchterm2\">Next>></a>"; } echo "</center>"; ?>