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@xxxxxxxxxxxxxxx> wrote in message
news:20051012210050.R79050@xxxxxxxxxxxxxxxx
>> 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);
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
