Q . How to replace mySQL query coloumns with count keyword?

sajeera kt. asked, Jun 26 ' 2016

I have to replace mysql columns using php preg_replace function is it possible to do it. fields are not consistent any thing can be there like * or any number of fields.

example :-

$query1 = 'Select * from user';
$query2 = 'Select fname,lname,email from user';
$query3 = 'Select a.*,b.col1,b.col2 from user as a left join table2 as b where a.id = b.userid';

After replacement query should be like :

$query1 = 'Select count(*) from user';
$query2 = 'Select count(*) from user';
$query3 = 'Select count(*) from user as a left join table2 as b where a.id = b.userid';

 

  • MySQL
  • regex
  • preg-replace

1 Answers

preg_replace is pretty straightforward to use, it simply accepts a pattern, a string to replace the pattern with and subject text as parameters, it returns the manipulated string:

<?php

$query1 = <<<SQL
    Select * from user;
SQL;
$query2 = <<<SQL
    Select fname,lname,email from user;
SQL;
$query3 = <<<SQL
    Select a.*,b.col1,b.col2 from user as a left join table2 as b where a.id = b.userid;
SQL;

$query1 = preg_replace('/Select .* from/i', 'Select count(*) from', $query1);
$query2 = preg_replace('/Select .* from/i', 'Select count(*) from', $query2);
$query3 = preg_replace('/Select .* from/i', 'Select count(*) from', $query3);

/* the manipulated strings are now assigned to these variables, echo to view them */

echo $query1 . PHP_EOL;
echo $query2 . PHP_EOL;
echo $query3 . PHP_EOL;

output (in a terminal)

% php test.php
        Select count(*) from user;
        Select count(*) from user;
        Select count(*) from user as a left join table2 as b where a.id = b.userid;

 

Leave a Ansewer