Need Help With Pagination via PDO

I’m used to working with PHP 5x and oldschool MySQL, so I’m stumped as to how to add pagination to this script. Any pointers would be welcome. I know I have to add a LIMIT clause to my query, but anything I’ve tried so far to create pagination hasn’t been working. Many of the examples I’ve found don’t work with PHP7/ PDO,

Thanks for your help.

//initialize the variable we're going to pass through the URL so it isn't empty	
    $search = "%".$_GET['search']."%";
//if there's no search variable in the url, that means you've just arrived on the page, so exit the script and display a welcome message with instructions
    $search = " ";
	echo '<h2>What are you looking for?</h2><p>Please try searching for something. </p>';

$conn = new PDO("mysql:host=$servername;dbname=$dbname;charset=$charset;port=$port", $dbusername, $dbpassword);
//set up the SQL statement as a variable
$sql= "SELECT * FROM databasenane WHERE text LIKE :text order by id DESC";
//prepare the SQL query stored in variable $sql, see how the placeholder exists above
$stmt = $conn->prepare($sql);
//bind the paramater in the placeholder
$stmt->bindParam(':text', $search, PDO::PARAM_STR);
//execute the query

//display a row count
$total = $stmt->rowCount();
//echo back the text variable as a new variable, called textreflect, without the concatenated percentage symbols in the original variable
$textreflect = $_GET['search']; 
echo 'Your search for <strong>"'. $textreflect. '"</strong> uncovered '.$total.' result(s).<br>';

// here's the while loop, for dealing with search results
while ($row = $stmt->fetchObject()) {

//start displaying the items retrieved
echo '
the code in here for displaying the results

//close the database connection once it's done returning results
$conn = null;

Try echoing the $sql statement and then exit; Then paste the $sql statement into PhpMyAmin and see if there are any errors.

I think quotation marks are required around the $sql statement.

Question: what is your SQL query supposed to achievee?

Could you post one of the things you’ve tried to implement the pagination that you can’t get to work with PDO? I can’t see how there would be that many changes required - after all, it’s the way you execute the query and retrieve the data that changes for PDO, the query itself (with the exception of parameters) and general layout of the code should be very similar.

Basically all you need for simple pagination is to keep track of the page number and the original search query, which you could pass back into the results page as hidden variables in the form you use to submit the “next” or “previous” request. When you get one of those requests, perform the search again but with a limit of page length, (page number * page length) in the query.


get search parameter
if "next" or "previous" form-vars set {
  $limit = " LIMIT " . $pagelength . ", " . $nextpage * $pagelength }
$query = "select * from table where text like :whatever " . $limit
run $query
loop through results {
  output results }
output next and previous buttons

Once you have that working you can deal with making it nicer, only displaying next and/or previous buttons if there are any more results, implementing a dynamic “more results” thing to not re-draw the page, etc.

This particular query allows users to do a vague search on text that may be contained within an image. We transcribed the text into a database field by hand and let the user broadly search for an image related to a specific theme.

The query is ok; I get hundreds of search results when I test – I just need to be able to paginate those. Thanks.

you need one query that gives you the number of results and another that fetches the portion of the results that should be displayed on the page.

Here’s what I’ve got so far. I’ve been trying to make some example code fit in with my existing scripting (the original post is what I wrote), but I’m hung up on the fact that the example uses two separate queries, when I think I can and should do it with just one. Or from the sounds of it, I need to at least get the first query to be retrieving the same data as the second one.

I’ve written this program before in PHP5x without PDO, so I have a general idea of the principle, it’s just getting everything in the right place as I remake it to be more modern.

If I run this as-is, I end up displaying all 250 or so of the search results for my keyword, on one page, with no limit applied. The pagination links appear, but aren’t working correctly, because I can see that the first query is counting all the database rows, total, and trying to paginate 104 pages of results (about 3000 records), but the second query is actually retrieving the result set I want, which has about 250 rows. So If I can just apply the pagination stuff to the correct query, I’ll be laughing. I’m having trouble with the syntax, though.


$search = “%”.$_GET[‘search’].“%”;
//if there’s no search variable in the url, that means you’ve just arrived on the page, so exit the script and display a welcome message with instructions
$search = " ";
echo ‘

What are you looking for?

Please try searching for something.


try {
$conn = new PDO(“mysql:host=$servername;dbname=$dbname;charset=$charset;port=$port”, $dbusername, $dbpassword);

  $total  = $conn->query("SELECT COUNT(id) as rows FROM databasename")
  $perpage = 50;
  $posts  = $total->rows;
  $pages  = ceil($posts / $perpage);
  # default
  $get_pages = isset($_GET['page']) ? $_GET['page'] : 1;
  $data = array(
  	'options' => array(
  		'default'   => 1,
  		'min_range' => 1,
  		'max_range' => $pages
  $number = trim($get_pages);
  $number = filter_var($number, FILTER_VALIDATE_INT, $data);
  $range  = $perpage * ($number - 1);
  $prev = $number - 1;
  $next = $number + 1;
  $stmt = $conn->prepare("SELECT * FROM databasename WHERE text LIKE :text order by id DESC LIMIT :limit, :perpage;");
  $stmt->bindParam(':perpage', $perpage, PDO::PARAM_INT);
  $stmt->bindParam(':limit', $range, PDO::PARAM_INT);
  $stmt->bindParam(':text', $search, PDO::PARAM_STR);
  $result = $stmt->fetchAll();
  } catch(PDOException $e) 
  $error = $e->getMessage();
  $conn = null;
  $textreflect = $_GET['search']; 
  	echo "
  	<div class=\"error\">
  	<p><strong>Database Error:</strong> $error</p>
  	if($result && count($result) > 0)
  			echo "<h3>Total pages ($pages)</h3>";
  			# first page
  			if($number <= 1)
  				echo "<span>&laquo; prev</span> | <a href=\"?search=$textreflect&?page=$next\">next &raquo;</a>";
  			# last page
  			elseif($number >= $pages)
  				echo "<a href=\"?search=$textreflect&?page=$prev\">&laquo; prev</a> | <span>next &raquo;</span>";
  			# in range
  				echo "<a href=\"?search=$textreflect&?page=$prev\">&laquo; prev</a> | <a href=\"?search=$textreflect&?page=$next\">next &raquo;</a>";
  			echo "<p>No results found.</p>";
  		if($result && count($result) > 0)
  		{echo 'show results here';

What makes you think that you should do it with one? for simple pagination cases, two queries are often faster than a single one, due to the DBs internal query optimisation.

If that’s the case, I’d be fine with doing what works. I’m stuck on how to get the pagination query working with the same result set as the other query, then.

other query?

The sample code I pasted above has two queries: the first is (probably incorrectly) retrieving the total number of database records, while the second query is actually returning the result set that I want. In order to get the pagination working properly, I think I have to get the first query set up so it’s calculating the pagination based on the same result set that the second query is displaying.

My thinking is that I have to get the original query doing something like:

SELECT COUNT(id) as rows FROM databasename WHERE text LIKE :text order by id DESC

but I’m unsure of how to get the syntax set up correctly. I’m going to have to change the code to bind the :text value since :text relies on user input.

I’m stuck on how to modify this query and restructure

$total = $conn->query(“SELECT COUNT(id) as rows FROM databasename”)

correctly, so I can prepare a statement, bind :text and still have the pagination script pick up the proper row count, and function. I’ve been hacking around with this for a couple days now and not having any luck.

It is possible to do a “get everything” and have PHP display the results in “pages”.

But I’m wondering if it might be more efficient to have a query with LIMIT and OFFSET instead.

Have you tried using SQL_CALC_FOUND_ROWS ?

SQL_CALC_FOUND_ROWS and FOUND_ROWS() can be useful in situations when you want to restrict the number of rows that a query returns, but also determine the number of rows in the full result set without running the query again. An example is a Web script that presents a paged display containing links to the pages that show other sections of a search result. Using FOUND_ROWS() enables you to determine how many other pages are needed for the rest of the result.

Well, the syntax doesn’t look at that far out to me. Bind the search parameter to it exactly as you did earlier, and it should return the count of matching records. You can then run the main query with the limit and offset to recover the appropriate set. If performance is a concern, you could always just do the count the first time the query is run, and pass it through the ‘next’ and ‘previous’ buttons as a hidden variable, would be OK as long as no-one adds or deletes while you’re paging through.

Surely it would be

$cntqry = "select count(id) from tablename where text like :text"; // don't care about order here
$prep = $conn->prepare($cntqry);
$prep->bindParam(':text', $search);
$res = $prep->execute();
$count = $prep->fetchColumn();

Thanks. I’ll be out of town for a few days but will tackle this asap and check in when I’ve had a chance.

Thank you SO much. That last little kick was all I needed. It’s working now; much appreciated.

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.