1

I'm running a fairly complex MySQL query (multiple LEFT JOIN, GROUP_CONCAT and bound parameters) with PDO. Due to the complexity of the query I have added a LIMIT clause to the end to return a particular set of the data - which is shown in a paginated table in my application.

The pagination requires the total number of results to be found. I didn't realise this was so awkward but have read the following:

The performance of SQL_CALC_FOUND_ROWS was terrible so I can't use this.

The only way I could think to write this was to run a second query, using COUNT. But because the query uses parameter binding (for search terms the user has entered), I have to bind them again in the second query.

My initial query to get the results looks like this:

// Get the results
$sql = ' '; // Complex query
$limit = ' LIMIT ' . $start . ', ' . $offset;
$stmt = $connection->prepare($sql);

// Search parameters
if (isset($search['term1'])) {
    $stmt->bindValue('term1', '%' . $search['term1'] . '%');
} 
if (isset($search['term2'])) {
    $stmt->bindValue('term2', '%' . $search['term2'] . '%');
}
if (isset($search['term3'])) {
    $stmt->bindValue('term3', '%' . $search['term3'] . '%');
}

$stmt->execute();
return $stmt->fetchAll('assoc');

Now I need to remove the LIMIT so I've done the following:

$stmt_count = $connection->prepare('SELECT COUNT(*) FROM (' . $sql . ') AS total_results');
$stmt_count->execute();

But this doesn't work, because I'll need to bind the parameters again. Surely there's a more efficient way, than repeating all of the binding on to $stmt_count??

 if (isset($search['term1'])) {
    $stmt_count->bindValue('term1', '%' . $search['term1'] . '%');
} 
if (isset($search['term2'])) {
    $stmt_count->bindValue('term2', '%' . $search['term2'] . '%');
}
if (isset($search['term3'])) {
    $stmt_count->bindValue('term3', '%' . $search['term3'] . '%');
}

Incidentally, I cannot believe how something as basic as this has been made so difficult! It's a very basic requirement to be able to easily know the total number of results of a query.

Andy
  • 5,142
  • 11
  • 58
  • 131
  • 1
    Create an array with the parameters and pass it to your `$stmt->execute($params)` instead. Then you can reuse the array for the next call. http://php.net/manual/en/pdostatement.execute.php – M. Eriksson Jul 06 '17 at 11:06
  • How can I build that array up? If I do things such as `$bind[] = ['term1' => '%' . $search['term1'] . '%']; $bind[] = ['term2' => '%' . $search['term2'] . '%']` it doesn't work. Presumably because although `execute($bind)` accepts an array, the contents have to be formatted more like a string? – Andy Jul 06 '17 at 11:17
  • You're currently building a multidimensional array, which is wrong. Just do: `$bind = []; $bind[':term1'] = '%' . $search['term1'] . '%'; $bind[':term2'] = ....` – M. Eriksson Jul 06 '17 at 11:20

0 Answers0