Posted by "Guy Brom" on 10/13/05 12:50

I ended up using the rewriteCountQuery from PEAR::DB_Pager (attached).
Thanks all!

function rewriteCountQuery($sql)
{
if (preg_match('/^\s*SELECT\s+\bDISTINCT\b/is', $sql) ||
preg_match('/\s+GROUP\s+BY\s+/is', $sql)) {
return false;
}
$queryCount = preg_replace('/(?:.*)\bFROM\b\s+/Uims', 'SELECT COUNT(*)
FROM ', $sql, 1);
list($queryCount, ) = preg_split('/\s+ORDER\s+BY\s+/is', $queryCount);
list($queryCount, ) = preg_split('/\bLIMIT\b/is', $queryCount);
return trim($queryCount);
}

"Philip Hallstrom" <php@philip.pjkh.com> wrote in message
news:20051012210050.R79050@wolf.pjkh.com...
>> Can anyone suggest the correct regex to replace col1,col2... with
>> count(*)
>> and strip out everything just before ORDER BY?
>>
>> so for this:
>> SELECT col1,col2... FROM tbl WHERE filter1 filter2 ORDER BY order1,order2
>>
>> I would get this:
>> SELECT count(*) FROM tbl WHERE filter1 filter2
>
> $str = "SELECT col1,col2... FROM tbl WHERE filter1 filter2 ORDER BY
> order1,order2";
>
> $str = ereg_replace("^SELECT .* FROM (.*) ORDER BY .*",
> "SELECT COUNT(*) FROM \\1", $str);

[Back to original message]