PHP Pagination of Search Results

Pagination not working

[TheKG]

I have tried many pagination scripts over several weeks and cannot get any one of them to work correctly. This is the closest that I've gotten. The problem is that when I know for a fact that there should be more than one page to display, they don't. Only one page displays with the number "1" at the bottom.

I would appreciate any assistance in getting this to work. I know I've got to be missing something in all the articles I've read and forums I've visited.

Thank you!

<?php



include('file to connect to db');
$cxn = mysqli_connect($host,$user,$passwd,$dbname) or die ("couldn't connect to server" . mysqli_error()); 

$page = (!isset($_GET['page']))? 1 : $_GET['page'];
$prev = ($page - 1); 
$next = ($page + 1); 
$max_results = 35; 
$from = (($page * $max_results) - $max_results); 



$searchstring = mysqli_real_escape_string($cxn, $_POST['searchstring']);

$searchstring = rtrim($searchstring, 's');

$searchterm = explode(' ', $searchstring);

$query="SELECT * FROM table WHERE MATCH (`vendor_name`, `color_name`, `style_no`, `desc`, `style_desc`) AGAINST ('";

$i=0;
foreach ($searchterm as $word) {
 $query .= "+".$word."* ";
}
$query .= "' IN BOOLEAN MODE) LIMIT $from, $max_results";


echo "<p class='red'>You searched for $searchstring</p>";
echo "<p>&nbsp;</p>";


$result = mysqli_query($cxn,$query);
{
$numcols=7;
$numrows=5;
$i=0;
$ii=0;
$trout=0;

echo "<table id='styles' cellspacing='0' align='center'>";

while ($row = mysqli_fetch_array($result))
{
 if ($ii<$numrows) { 
 if ($trout==0){
  echo "<tr>"; 
  $trout=1; 
 }
 if ($i<$numcols){ 
 $desc = $row ['desc'];
 $vendor = $row ['vendor_name'];
$color = $row ['color_name'];
$styleno = $row ['style_no'];
$img = $row ['photo_sm'];
  $url = $row ['order_url'];


  echo "<td align='center' valign='top'>";
  echo "<p class='botstyle'>$vendor</p>";
  echo "<p class='botstyle'>Style #$styleno</p>";
  echo "<p class='botstyle'>$color</p>"; 
  echo "<a href='../$url'>";
  echo "<img src='../images/$img' border='0' alt='$desc' title='$desc'>";
  echo "</a></p>";
  echo "<p class='botstyle'>$desc</p></td>";

  $i++;
 }
 $endtr=$numcols-1;
 if ($i==$endtr){
  echo "</tr>";
  $i=0;
  $trout=0;
  $ii++; 
 }
 }
}
}

$total_results = mysqli_num_rows($result);
$total_pages = ceil($total_results / $max_results); 
$pagination = ''; 
if($page > 1) 
{ 
$pagination .= '<a href="match4.php?page='.$prev.'"><< Previous</a> '; 
} 


/* Loop through the total pages */ 
for($i = 1; $i <= $total_pages; $i++) 
{ 
if(($page) == $i) 
{ 
$pagination .= $i; 
} 
else 
{ 
$pagination .= '<a href="match4.php?page='.$i.'"><b>&nbsp;'.$i.'&nbsp;</b></a>'; 
} 
}
if($page < $total_pages) 
{ 
$pagination .= '<a href="match4.php?page='.$next.'">Next >></a>'; 
} 

echo "<tr>";
echo "<td colspan='7' align='center'>$pagination</td>";
echo "</tr>";

echo "</table>";

?> 

[robzilla]

Are $page, $total_results and $total_pages, in fact, returning the correct values, when you echo them just before you start the loop? There's "know[ing] for a fact", and there's knowing for a fact :-) Don't see a problem with your navigation loop just yet.

PS: I would suggest running $searchstring through htmlspecialchars() before you echo it onto the page, just to be sure.

[TheKG]

Thank you for the quick response. Still learning and did not realize that I should have echoed $page, $total_results and $total_pages. Your intuition was correct. The values they are returning are 1 page, 35 total results and 1 total page; there should be many more results than that. How do I find and correct these values?

[RhinoFish]

Echo your $query too, so you can see what info you are asking for. :-)

Here:
$query .= "' IN BOOLEAN MODE) LIMIT $from, $max_results";
You are adding a limit to the rows returned, telling the database to give you up to 35 rows, which is the max_results you defined above.

"there should be many more results than that."
Not if your query says to limit the rows. :-)

[TheKG]

OK, I had echoed the query prior to adding the pagination and it is requesting the correct information from the database. When pagination is not used, I verified that all records were displayed.

I guess I misunderstood what max_results meant; I was under the impression that was the maximum results I wanted to display on each page, not the total of all results. When I change the number from 35 to 350, max_results echoes 162 in my search (which is correct), but only one page with 35 results is displayed.

[robzilla]

I was under the impression that was the maximum results I wanted to display on each page, not the total of all results

And that's correct, but now you don't know how many results there are in total, since you impose a maximum length on the query result set by passing the value of $max_results to the LIMIT function. So the problem starts at $total_results, which always returns 35 (or less). You could work around this by adding a second query that is identical to the first query but without the LIMIT function, so that you get the complete result set, which you can then count and store as $total_results. The downside is you have to run the query twice, and search queries can be slow if your tables contain many records.

Replace...

$total_results = mysqli_num_rows($result);

...with...

$total_results = mysqli_num_rows(mysqli_query($cxn,preg_replace('#LIMIT[0-9, ]+#','',$query)));

...and you're good to go. We're recycling $query but using preg_replace() to remove the LIMIT function so that MySQL returns the complete set of results, which we then count with mysqli_num_rows() and store in $total_results.

It's a little rough around the edges but just about the least complex and most plug-and-play solution I can think of.

[TheKG]

By replacing as suggested by robzilla, one of my searches shows 95 total results, 3 total pages, but when I attempt to go to subsequent pages, I get this message:

Notice: Undefined index: searchstring in myurl.php on line 34

This is line 34: $searchstring = mysqli_real_escape_string($cxn, $_POST['searchstring']);

[robzilla]

Right, because the POST data isn't "remembered" with any subsequent request. That's why search queries are usually handled via GET rather than POST, since the query then is a value of a URL parameter, e.g. /search.php?query=some+query, that you can then use in your pagination links, like /search.php?query=some+query&page=2.

[TheKG]

OK, so I changed POST to GET in both my form method and on line 34. Same results, 95 total results, 3 total pages, but can't get to subsequent pages and receive the same message as I listed above.

[RhinoFish]

I'm not sure what you mean by "can't get to subsequent pages".
When you do click on the Next link, what URL loads?
(remove your domain name if you post the full URL)

[TheKG]

When I click on "Next" to get to page 2 of the results, the only info that displays is:

Notice: Undefined index: searchstring in /home1/mydomain/public_html/testsite/match4.php on line 34

Here is the url: www.mydomain.com/match4.php?page=2

[robzilla]

www.mydomain.com/match4.php?page=2

Where's the search query? You'll need to add it to your $pagination variable, or PHP won't have any way of knowing what the query is (it's not stored anywhere in between page visits, unless you keep it in a URI parameter, as you do with "page").

So you should end up with: www.mydomain.com/match4.php?page=2&searchstring=something

You can use urlencode() to sanitize the query string when you pass it onto $pagination.

[TheKG]

Sorry, I don't understand the question, "Where's the search query?"; I included all the code in my first post. Additionally, I looked up using urlencode(), but do not know how to add it to the $pagination variable; the examples provided on numerous sites weren't clear on using is for this purpose.

[robzilla]

You're not including the search query in the URL parameters of your pagination links. When you submit the query widgets to the search form (with method GET rather than POST, as previously discussed), you will be directed to /match4.php?searchstring=widgets, right? Because they are part of the request URI, the "searchstring" parameter and its value ("widgets") can be used in PHP: the variable $_GET['searchstring'] is automagically set to contain the value 'widgets'. Your pagination links, however, do not include the search query: /match4.php?page=2. As such, on that page (e.g. page 2) PHP no longer knows what the query is (only that $_GET['page'] == '2', because that's the only GET parameter), and cannot query the database accordingly ($_GET['searchstring'] does not exist, so you get the error).

Do you see how that works, with the URL parameters translating to $_GET? So all you need to do is make sure the searchstring variable and its value (the query) go into the pagination URLs. For example:

$pagination .= '<a href="match4.php?page='.$i.'&searchstring='.urlencode($_GET['searchstring']).'"><b>&nbsp;'.$i.'&nbsp;</b></a>';

So that you end up with /match4.php?page=2&searchstring=widgets and not /match4.php?page=2.

[TheKG]

Read through your post and tried to replace $paginate, but in which line?

$pagination = ''; 
if($page > 1) 
{ 
$pagination .= '<a href="match4.php?page='.$prev.'"><< Previous</a> '; 
} 


/* Loop through the total pages */ 
for($i = 1; $i <= $total_pages; $i++) 
{ 
if(($page) == $i) 
{ 
$pagination .= $i; 
} 
else 
{ 
$pagination .= '<a href="match4.php?page='.$i.'"><b>&nbsp;'.$i.'&nbsp;</b></a>'; 
} 
}
if($page < $total_pages) 
{ 
$pagination .= '<a href="match4.php?page='.$next.'">Next >></a>'; 
} 

When I replaced $paginate='', I receive the message:
Notice: Undefined variable: pagination in /myurl/public_html/testsite/match4.php on line 107
on the first page of resultls

When I replaced $pagination .= '<a href="match4.php?page='.$prev.'"><< Previous</a> '; , I receive the message:
Notice: Undefined index: searchstring in /home1/myurl/public_html/testsite/match4.php on line 33
on the second page - no results are shown on second page.

I must still not be understanding where to use this part of the code.

[robzilla]

It's probably a good idea to get a basic grasp on the PHP language if you intend on working with it more in the future. Borrowing code snippets from here and there can be a recipe for disaster, and it obviously makes troubleshooting a lot more complicated. Just knowing the basics (should only take a few hours) can help a lot.

Every time your pagination code creates a pagination link ("Previous", "Next", or "1", "2", "3", etc), you'll need to include the search query in the URL of the href attribute, just as you're already including the page number (e.g. ?page='.$i.'). This happens three times in the code, so all of those need to be adjusted. I'll just edit the code for now (changes in bold).

$pagination = '';
$search_query = urlencode($_GET['searchstring']);

if($page > 2) {
$pagination .= '<a href="match4.php?searchstring='.$search_query.'&page='.$prev.'"><< Previous</a> ';
} elseif($page == 2) {
$pagination .= '<a href="match4.php?searchstring='.$search_query.'"><< Previous</a> ';
}

/* Loop through the total pages */
for($i = 1; $i <= $total_pages; $i++)
{
if(($page) == $i)
{
$pagination .= $i;
}
else
{
$pagination .= '<a href="match4.php?searchstring='.$search_query.'&page='.$i.'"><b>&nbsp;'.$i.'&nbsp;</b></a>';
}
}
if($page < $total_pages)
{
$pagination .= '<a href="match4.php?searchstring='.$search_query.'&page='.$next.'">Next >></a>';
}

I created a new variable $search_query to avoid calling the urlencode() function thrice. I also added a condition to the "Previous" link generation so that you don't link to /match4.php?page=1 from page 2 but rather to /match4.php (the page parameter is superfluous because page 1 is the default).

$page = (!isset($_GET['page']) || !is_numeric($_GET['page']))? 1 : $_GET['page'];

I've added an extra condition here to make sure that $_GET['page'] is numeric to avoid people injecting non-numerical content into your output and thereby breaking your pagination (or worse). One of the aforementioned "disasters".

That's just a fancy way of writing the following, by the way:

if(!isset($_GET['page']) || !is_numeric($_GET['page'])) {
$page = 1;
} else {
$page = $_GET['page']
}

There are more improvements and safeguards imaginable throughout the whole script, but this should (hopefully) fix the pagination issue.

[TheKG]

Thank you, robzilla, it works like a charm! The pagination code I started with was one that I used on another page on my site, but it wasn't for a search. Learning PHP code has been a work in progress and I will continue to read up on it. I appreciate that when I post in this forum, there is assistance given with instruction. By the time I post here, I have already attempted to figure out the solution on my own for days or weeks and feel like a total idiot. Thank you all for not treating me like one. :-)

[RhinoFish]

You can see now, why I asked what URL you land on. :-)

[robzilla]

Happy to help, glad to know it's working, and feel free to ask again :-)