Modify SQL SELECT statement with PHP

Question

How best to alter a SQL SELECT statment's field list to a COUNT(*) using PHP?

e.g

SELECT blah, blah, blah FROM tbl_blah WHERE blah;

to

SELECT COUNT(*) FROM tbl_blah WHERE blah

I have considered mysql_num_rows... but I am pretty sure it would be more efficient to edit the statement. I am guessing it could be solved with a regex expression... :S

Answer 1

You can do this using preg_replace():

<?php
$sql = "SELECT blah, blah, blah FROM tbl_blah WHERE blah;";

$newSql = preg_replace(
    "/^SELECT (.*) FROM (.*)$/",
    "SELECT COUNT(*) FROM $2",
    $sql);

echo $newSql;
// SELECT COUNT(*) FROM tbl_blah WHERE blah;
?>

Answer 2

The best thing would be to store the various pieces in separate variables and then call a function to coalesce them:

function makeSQL($fields, $tables, $conditions='')
{
  $sql = "SELECT $fields FROM $tables";
  if ($conditions != '')
  {
    $sql .= " WHERE $conditions";
  }
  return $sql;
}

That way you can call it with the proper fields one time, then COUNT(*) the next.