Get count query for MySQL

Posted on 27-Mar-2016

The following function will require a sql query string as the first parameter and a second parameter with column that will be used to count the query by.

**
 * @param string $sql
 * @param string $what
 *
 * @return string
 */
function countQuery($sql, $what='*')
{
    // remove new line elements
    $sql = preg_replace("/([\r\n]+)/", " ", $sql);

    // insert COUNT(*) between select and from
    $sql = preg_replace("/^SELECT(.*)FROM/i", "SELECT COUNT(" . $what . ") AS `total` FROM ", $sql);

    // exclude the limitation of results
    $sql = preg_replace("/LIMIT(.*)/i", "", $sql);

    // exclude the ordering of results
    $sql = preg_replace("/ORDER BY(.*)/i", "", $sql);

    return $sql;
}

Usage example

<?php

$query = "SELECT id, username, email, password FROM users WHERE status = 'active'";

$countQuery = countQuery($query);

// $countQuery will be: SELECT COUNT(*) FROM users WHERE status = 'active';