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

Ignacio Vazquez-Abrams · Accepted Answer · 2010-02-11 18:48:23Z

3

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.

answered Feb 11, 2010 at 18:48

Ignacio Vazquez-Abrams

804k160 gold badges1.4k silver badges1.4k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

NullUserException · Accepted Answer · 2011-11-13 17:47:20Z

2

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;
?>

edited Nov 13, 2011 at 17:47

NullUserException

85.8k31 gold badges212 silver badges239 bronze badges

answered Feb 11, 2010 at 18:48

Thiago Belem

7,8325 gold badges47 silver badges65 bronze badges

Collectives™ on Stack Overflow

Modify SQL SELECT statement with PHP

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related