Replace everything between SELECT and FROM

Question

I have a certain object processing certain queries for generic HTML tables. These queries are just ordinary SELECTs with subqueries, aggregates, etc.

In the current situation, the application counts the number of results the query would give, to enable (automatic) pagination:

1. SELECT COUNT(*) FROM (SELECT this, that, SUM(foo), COUNT(bar) FROM b WHERE x = y)

Then some object processes and adds pagination etc. The table gets filled with the same query + a LIMIT clause:

2. SELECT this, that, SUM(foo), COUNT(bar) FROM b WHERE x = y LIMIT n

But that count query (as shown under 1) is quite slow, so I wanna replace that using this:

3. SELECT COUNT(*) FROM b WHERE x = y <-- the query from 1 & 2, but I replaced this, that, SUM(foo), COUNT(bar) with COUNT(*).

The question is: how can I effectively replace everything between SELECT and FROM without messing things up when I use subqueries? Can I do this with a regex? Is there another way?

The queries differ everytime. It's a generic handler executing 2 queries: 1 count query, and 1 for data in the table. I'm trying to find a generic solution to cut everything between the first SELECT and its corresponding FROM.

Will your subquery always contain only one table that you're querying from? — Frank Schmitt, Apr 26 '13 at 12:59

score 1 · Accepted Answer · edited Feb 02 '15 at 14:31

1

I've tried to do the following. I hope it helps.

<?php
$new = "source,destination";

$source = "SELECT t.id , t.name, t.number from tbl_tttt t";

$start = 'select ';
$end = ' from';

$data = preg_replace('#('.preg_quote($start).')(.*)('.preg_quote($end).')#si', '$1'.$new.'$3', $source);


    echo $data;

?>

Then the query becomes :

SELECT source,destination from tbl_tttt t

edited Feb 02 '15 at 14:31

Luis Gouveia

8,334
9
46
68

answered Feb 02 '15 at 13:45

Kamlesh Kumar

1,632
2
21
31

Replace everything between SELECT and FROM

1 Answers1