Regular Expression for extracting a specific portions on a mySQL Select statement
Results 1 to 6 of 6

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

  1. #1
    Noob 4ever!
    Join Date
    Jul 2003
    Posts
    354

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

    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

    TJ
    --------------------------
    PHP - Phrantic Hyper Puh
    (couldnt think of a good sig right now..s orry)

  2. #2
    High Energy Magic Dept. NogDog's Avatar
    Join Date
    Aug 2006
    Location
    Ankh-Morpork
    Posts
    13,816
    Maybe...
    Code:
    '/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:
    PHP Code:
    $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"
    Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be." ~ from Nation, by Terry Pratchett

    "But the main reason that any programmer learning any new language thinks the new language is SO much better than the old one is because hes a better programmer now!" ~ http://www.oreillynet.com/ruby/blog/...ck_to_p_1.html


    eBookworm.us

  3. #3
    PHP Witch laserlight's Avatar
    Join Date
    Apr 2003
    Location
    Singapore
    Posts
    13,459
    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.
    Use Bazaar for your version control system
    Read the PHP Spellbook
    Learn How To Ask Questions The Smart Way

  4. #4
    Noob 4ever!
    Join Date
    Jul 2003
    Posts
    354
    Quote Originally Posted by NogDog View Post
    Maybe...
    Code:
    '/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:
    PHP Code:
    $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
    --------------------------
    PHP - Phrantic Hyper Puh
    (couldnt think of a good sig right now..s orry)

  5. #5
    Pedantic Curmudgeon Weedpacket's Avatar
    Join Date
    Aug 2002
    Location
    General Systems Vehicle "Thrilled To Be Here"
    Posts
    21,771
    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.
    Last edited by Weedpacket; 05-01-2013 at 03:35 AM.
    THERE IS AS YET INSUFFICIENT DATA FOR A MEANINGFUL ANSWER
    FAQs! FAQs! FAQs! Most forums have them!
    Search - Debugging 101 - Collected Solutions - General Guidelines - Getting help at all

  6. #6
    Noob 4ever!
    Join Date
    Jul 2003
    Posts
    354
    Quote Originally Posted by Weedpacket View Post
    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.
    Last edited by Tea_J; 05-01-2013 at 03:54 AM.
    --------------------------
    PHP - Phrantic Hyper Puh
    (couldnt think of a good sig right now..s orry)

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •