Regular Expression for extracting a specific portions on a mySQL Select statement

Tea_J

From this (and other similar

SELECT
emp_contacts.mobile_no,
emp_contacts.fname,
emp_contacts_cache.data_source,
emp_contacts_cache.name_summary
FROM
emp_contacts
LEFT JOIN emp_contacts_cache ON emp_contacts.mobile_no = emp_contacts_cache.mobile_no
WHERE emp_contacts.mobile_no = emp_contacts_cache.mobile_no
ORDER BY emp_contacts.name ASC LIMIT 0,100

I need to get just this part:

SELECT
emp_contacts.mobile_no,
emp_contacts.fname,
emp_contacts_cache.data_source,
emp_contacts_cache.name_summary
FROM
emp_contacts
LEFT JOIN emp_contacts_cache ON emp_contacts.mobile_no = emp_contacts_cache.mobile_no
WHERE emp_contacts.mobile_no = emp_contacts_cache.mobile_no
ORDER BY emp_contacts.name ASC LIMIT 0,100

so my new string would result to

FROM
emp_contacts
LEFT JOIN emp_contacts_cache ON emp_contacts.mobile_no = emp_contacts_cache.mobile_no
WHERE emp_contacts.mobile_no = emp_contacts_cache.mobile_no

I basically need to create a copy of the SELECT statement and run a SELECT COUNT(*) rather than retrieving the rows.

Hope ya'll can help

Regards

Tea_J

bounce 😛

Tea_J

anyone?

NogDog

Maybe...

'/FROM.*(?=GROUP BY|ORDER BY|LIMIT|$)/sU'

However, if this is something to do with your question about pagination, I'd me more inclined to explicitly defining it myself. To that end, you can sort of avoid repeating yourself by defining that part first, then incorporating it into each query:

$sqlCore = "FROM
emp_contacts
LEFT JOIN emp_contacts_cache ON emp_contacts.mobile_no = emp_contacts_cache.mobile_no
WHERE emp_contacts.mobile_no = emp_contacts_cache.mobile_no
";

$sqlMain = "SELECT
emp_contacts.mobile_no,
emp_contacts.fname,
emp_contacts_cache.data_source,
emp_contacts_cache.name_summary
$sqlCore
ORDER BY emp_contacts.name ASC LIMIT 0,100";

$sqlTotal = "SELECT COUNT(*)
$sqlCore";

laserlight

Yeah, this is related to the pagination question. Basically, the approach that I would take is to construct the SQL statement rather than try to modify an already constructed SQL statement with regex.

Tea_J

NogDog;11027455 wrote:
Maybe...
'/FROM.*(?=GROUP BY|ORDER BY|LIMIT|$)/sU'
However, if this is something to do with your question about pagination, I'd me more inclined to explicitly defining it myself. To that end, you can sort of avoid repeating yourself by defining that part first, then incorporating it into each query:
$sqlCore = "FROM
emp_contacts
LEFT JOIN emp_contacts_cache ON emp_contacts.mobile_no = emp_contacts_cache.mobile_no
WHERE emp_contacts.mobile_no = emp_contacts_cache.mobile_no
";

$sqlMain = "SELECT
emp_contacts.mobile_no,
emp_contacts.fname,
emp_contacts_cache.data_source,
emp_contacts_cache.name_summary
$sqlCore
ORDER BY emp_contacts.name ASC LIMIT 0,100";

$sqlTotal = "SELECT COUNT(*)
$sqlCore";

ey man, thanks. and like laserlight said it is related to the other topic.. i hope this is not considered duplicate topic..i wanted to organize my questions...

anywho... thanks for the samples.. but i' actually simplifying my code, make it cleaner and easier to read.. your code sample is perfectly fine.. but as you can see you had to do 2 different sql statements, and that means having to debug /modify 2 statements if need be.. and the added lines to do the logic in loops during constructing it etc.. now multiply that to the many other sql queries you have to build and it's a headache to read/debug..

my approach is much more simple .. not too many hoops and loops during

just create the query you need, STRAIGHT FORWARD , and just worry about the conditions you need to code for the query itself..

put all that in a simple $q variable
pass it to the function

eg. query_select($q, $rowlimit,$curpage);

, and let the function parse the select statement to create the needed count statement .. and add the LIMIT clause to the original $q statement as needed 🙂

hence i need this Regex to extract just the conditions i need ..

To be honest, i was thinking of simply splitting the sql at FROM keyword, and just add SELECT count(*) to the rest of the query .. hmm.. perhaps that's a more simple approach huh?

$q_parts = explode("$q","FROM");
$q_count = "SELECT count(*) " . $q_parts[1];
etc...

hmmmmm

Weedpacket

I'd agree with laserlight that the better approach is to construct the query however it's needed as it's needed, rather than construct it and then deconstruct it and then reconstruct it again. For the deconstruction step in the case of SQL if you want any sort of robustness (unless you impose sufficiently tight constraints on what sorts of queries you'll allow - in which case you might as well provide a mechanism for building them that ensures those constraints are met...) you'd be looking at implementing more of a proper parser than taking the desperate Perl hacker's approach of throwing regular expressions at everything and hoping some of them stick.

Tea_J

Weedpacket;11027469 wrote:
I'd agree with laserlight that the better approach is to construct the query however it's needed as it's needed, rather than construct it and then deconstruct it and then reconstruct it again. For the deconstruction step in the case of SQL if you want any sort of robustness (unless you impose sufficiently tight constraints on what sorts of queries you'll allow - in which case you might as well provide a mechanism for building them that ensures those constraints are met...) you'd be looking at implementing more of a proper parser than taking the desperate Perl hacker's approach of throwing regular expressions at everything and hoping some of them stick.

i understand.. but i still feel that's more of a "just because it is standards" approach.. for that it is better.. but for cleaner faster coding, i do feel i found a more efficient way to do this paginating thing, well in my case at least.. this is lesser lines of code, faster in terms of development (im big on speed coding, as most projects nowadays dont offer good pay, i developed my own framework that makes my coding so much more faster and lighter on the server) ..

i know there may be "surprises" later on w/ this technique, but as w/ everytthing else, sure there's a way to fix, mod, or even "hack" it.. there's nothing wrong w/ minor hacks and work arounds as long as it does the job in more efficient way.. infact if i explained how i do my query_select function, i'd probably get bombarded w/ OOP and other coding criticisms .. lol.. but bottom line they get the job done in much less time.. and a lot of times quicker server performance even.

Thank you though for your perspectives.. i do appreciate them still .

ps
woot! i just tried splitting technique, it's working fabulously! ^_ .. even w/ my joins and stuff, hmm i think this works fine with all select statements except for ones w/ GROUP BY clauses.
havent tried regexp code you gave me though, but i believe splitting quicker.. ill save your regex incase i find issues w/ splitting.