I have been working for several weeks on trying to finish my pagination script. And yes I have googled and search all over the place trying to figure this one out. I’m just about there but I’m stuck on the last few things I would like to do.
Currently I have a pagination script that shows the content of my database with pagination. I want to be able to do a simple search in my database and display the results of the search with my pagination script. I don’t want to redirect to another page for the search. I want to display the results of the search in place of displaying all the results in x pages when a user clicks on the search button, submitting a search query.
Now this is the general idea on what I came up with.
$search = $_POST['txtSearch'];
if ($search == null) {
// search field is null no search was performed, display all records
$sql = "SELECT * FROM articles ORDER BY id DESC LIMIT $from, $max_results";
}
else
{
// a search was performed, display results of the search
$sql = "SELECT * FROM articles WHERE message LIKE '%$search%' ORDER BY id DESC LIMIT $from, $max_results";
}
$getlist = mysql_query($sql) or die(mysql_error());
If $search is null then display all the records in the database (no search). If it’s not null, it will run the search query instead.
Now, I’m not sure if this is the best way to go about doing this, maybe I’m trying to do too much at once. Maybe someone could guide me in the general direction on how to split up my code to make it more manageable. (I eventually would like to get this into some kind of a function for easy expandability and use)
Anyways, I realized that my search term was being lost when I click on another page of results. So I did some research and found out that I needed to pass my search query in my url in my page numbers and next, prev buttons ect... So I did the following to all my links in my pagination:
echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$i&search=$search\">$i</a>";
All is dandy right? Nah still didn’t work, I don’t know if I’m missing something with passing the search query or if there’s something wrong with my code all together.
Here is my code in full in case anyone would like to see it and point me in the general direction.
<?php
// Database Connection
include 'db.php';
$search =$_POST['txtSearch'];
echo "This is the search query: ". $search; //just checking to see if search term is being passed
// If current page number, use it
// if not, set one!
if(!isset($_GET['page'])){
$current_page = 1;
} else {
$current_page = $_GET['page'];
}
// Define the number of results per page
$max_results = 3;
// Figure out the limit for the query based on the current page number.
$from = (($current_page * $max_results) - $max_results);
if ($search == null) {
// search field is null no search was performed, display all records
$sql = "SELECT * FROM articles ORDER BY id DESC LIMIT $from, $max_results";
}
else
{
// a search was performed, display results of the search
$sql = "SELECT * FROM articles WHERE message LIKE '%$search%' ORDER BY id DESC LIMIT $from, $max_results";
}
$getlist = mysql_query($sql) or die(mysql_error());
// Figure out the total number of results in the database
$total_results = mysql_result(mysql_query("SELECT COUNT(*) as Num FROM articles"),0);
// Figure out the total number of pages. Always round up using ceil()
$total_pages = ceil($total_results / $max_results);
// Build your formatted results here.
while($row = mysql_fetch_array($getlist)){
$now_format = $row['article_date'];
$new_format = date('F d, Y',strtotime($now_format)); // transform the date
echo "<div id=\"article_date\">". $new_format ."</div> \n"; // prints out: date in following format October 20, 2006
echo "<div id=\"article_chk\">"."<input type=\"checkbox\" name=\"del_select[]\" value=\"$row[id]\" />"."</div>"."<br>\n";
$row['message']=nl2br($row['message']);
echo "<div id=\"article_body\">" .$row['message'] ."</div>\n";
echo $row['title'];
}
// Now we are ready for the output of our pagination!
echo '<div style="margin-top: 15px; margin-bottom: 5px; text-align:center;">';
if ($total_results == 0){
echo "sux0rz no articles were found";
}
$show_pages = 5; // Change the value for the number of links you want to show
// Display only x pages in the pagination list so there wont be too many page numbers
if ($total_pages <= $show_pages)
{
$page_end = $total_pages;
$page_start = 1;
}
else
{
$pages = floor($show_pages/2);
$page_start = $current_page - $pages;
if ($page_start <= 0)
{
$page_start = 1;
}
$page_end = $page_start + $show_pages - 1;
if ($page_end > $total_pages)
{
$page_start = $total_pages - $show_pages + 1;
$page_end = $total_pages;
}
}
// Build First page Link if not on the first page
echo '<div class="pages" style="float:right; text-align:right; width: 142px;">';
if ($current_page != 1) {
echo "<a href=\"".$_SERVER['PHP_SELF']."?page=1&search=$search\">« First</a>";
}
// Build Back Link
if($current_page > 1){
$prev = ($current_page - 1);
echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$prev&search=$search\">‹ Back</a>";
}
// Build Next Link
if($current_page < $total_pages){
$next = ($current_page + 1);
echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$next&search=$search\">Next ›</a>";
}
// Build Last Page link if not on last page
if ($current_page != $total_pages) {
echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$total_pages&search=$search\">Last »</a>";
}
echo "</div>"."\n";
// Display record numbers
echo '<div class="pages" style="float:left; text-align:right; width: 142px; text-align:left;">';
if ($total_results == 0){
$min_num = 0;
}
else
{
$min_num = $from + 1;
}
$max_num = $from + mysql_num_rows($getlist);
echo $min_num. " - ".$max_num. " of " .$total_results. " Results";
echo "</div>"."\n";
// Loop to display page numbers
echo '<div class="pages">';
for ($i = $page_start; $i <= $page_end; $i++)
{
if ($i == $current_page)
{
// on current page. do not link this number!
echo '<span class="current">'."$i"."</span>";
}
else
{
// hey we arent on the page we want. link the user so they can go there!
echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$i&search=$search\">$i</a>";
}
}
echo "</div></div>"."\n";
//close database connection
mysql_close($db_handle);
?>
And just for a visual reference this is what we are working with.
http://img.photobucket.com/albums/v487/xfezz/forms2.jpg
That is all my records being displayed with out a search performed. I would like the search results to show up there instead of that.