php - Paginate result set having written the query with prepared statements,

Question

I have been using paginating classes when I did before the query the traditional way, but now that I have come to do the queries using prepared statements, via the $stmt->fetch() statement, I don't know how to paginate now. I have been looking around and checking the stackoverflow literature and it seems that there is some kind of ready solution for it, some commands or some : sort-> stuff.

How do you paginate what comes out of this loop? Are there some commands within the "world of prepared statements"? any link, page or web where that is shown?

while( $stmt->fetch() )
    {
        printf("%s%s%s%s", $Email, $Webpage, $Telephone, $Mobile);
    }

Answer 1

Method 1: get_result():

*Note: This method only works with PHP >= 5.3, having the mysqlnd native driver.

Assuming this was done with MySQLi and you have bound these variables as result vars via bind_result(), I would instead use get_result() to transfer it into a MySQLi result resource, and fetch the rows as arrays onto an array containing all rows. Then use any pagination method or plugin you would normally use on array data:

// Don't use bind_result()...
// execute your statement
$stmt->execute();
// Get result set into a MySQLi result resource
$result = $stmt->get_result();

// array to hold all results
$rowset = array();
// And fetch with a while loop
while ($row = $result->fetch_assoc()) {
  $rowset[] = $row;
}

? ? var_dump($rowset);

Now use $rowset as a 2D array, with which you can use any pagination method that operates on regular arrays.

Method 2: build an array with bound output vars

If you don't have the mysqlnd native driver (and hence cannot use get_result(), continue using bind_result() but append all of those onto an array:

// array to hold all rows
$rowset = array();

// All results bound to output vars
while ($stmt->fetch()) {
  // Append an array containing your result vars onto the rowset array
  $rowset[] = array(
    'email' => $Email,
    'webpage' => $Webpage,
    'telephone' => $Telephone,
    'moblile' => $Mobile
  );
}
var_dump($rowset);