using explode to get different characteristics of sql query

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

mcccy005
Forum Contributor
Posts: 123
Joined: Sun May 28, 2006 7:08 pm

using explode to get different characteristics of sql query

Post by mcccy005 »

What I need is to get the following things from an sql query string:
1. column names;

AND
(if applicable)

2. the two valus after 'LIMIT' (if present)
3. the column name after 'ORDER BY' (if applicable)
4. ASC OR DESC (if applicable after the column name after ORDER BY)

Any ideas on how to do this though? I can't use explode("," $sql_query) because there may only be one column; or there may be multiple tables in the query; so I somehow need to get the values AFTER 'select' and BEFORE 'table', but put them into an array based on commas (and if there aren't any, put it into the first element of a columns array).
Same goes with 'LIMIT' if it is present - i need the first value after limit and the value after the comma if it is present (ie. LIMIT 5, 10) which translates to select rows 6 - 15.
Again same goes for 'ORDER BY' (if its present) - i need the column name after that AND seperately I need 'ASC' or "DESC" (if its present).

For all I know, there may even be built in mysql functions in php to allow me to get these things (but whcih i havent been able to find).
Thanks
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post by Benjamin »

This really sounds like a job for RegEx. What is the purpose of this? Would it not be possible for you to simply have the script log the queries?

MySQL has built in features to log queries as well I believe.
mcccy005
Forum Contributor
Posts: 123
Joined: Sun May 28, 2006 7:08 pm

Post by mcccy005 »

havent heard of regex before so will look into that
basically I'm developing objects/classes to make creating a set of results much much easier (well...easier for future projects and code re-use).
Theres basically a Result_Set class (all results on each page); Result_Field (each row of the table of results) and a Result_Cell class (each individual cell in the table).

The results page will be set up to basically accept either the results manually; or preferably, accept a database connection and an sql query.
If I can break apart the query, I can dynamically use it to create a new result_set object (which contains the fields and cells objects).

What do you mean by 'log' the query???
mcccy005
Forum Contributor
Posts: 123
Joined: Sun May 28, 2006 7:08 pm

Post by mcccy005 »

Ok so i've looked at regex (really should have known what that was before); but I can't see how its use will allow me to all values between the words "SELECT" and "FROM" in a string sql query??

With regard to the "LIMIT" and "ORDER BY" values, I can sort that out in a different way, but I still have trouble finding column names and putting them into a seperate array with each column distinguished by a "," (hence, the reason I'm thinking of using explode( ) ).
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

That is what regex does... it looks in a string/array for a pattern. In your case the pattern would be anything after 'SELECT' but before 'FROM'. You may also want to look in the RegEx forum for common examples and community provided solutions to common regex questions.
User avatar
Ollie Saunders
DevNet Master
Posts: 3179
Joined: Tue May 24, 2005 6:01 pm
Location: UK

Post by Ollie Saunders »

OK lets clear up some confusion here.
No single regular expression no matter how fancy will satifisfy your requirements here. What you are asking for is for code that can parse SQL and return a data structure of everything its found. Regular expressions could assist in the writing of one of those but you need a bit more too.

There may already be an SQL parser for PHP so have a search but if not you'd have to write one yourself and that's no small job. Of course if you don't need something that will always work for everybody's code then you could write one just to satisify some basic rules. So if all you need is column names from queries like this:

"SELECT * FROM Table"
"SELECT title,id FROM Table"
"SELECT monkey, title AS name FROM Table"

then you could quite happily write something that parsed that; wouldn't be too difficult.

Of course you could save yourself the bother of that and query the a table's meta data with DESCRIBE, here's the result of a "DESCRIBE IntResponse" query on one of my databases:

Code: Select all

+-----------------+------------------+------+-----+---------+----------------+
| Field           | Type             | Null | Key | Default | Extra          |
+-----------------+------------------+------+-----+---------+----------------+
| intResponseId   | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| _siteQuestionId | int(10) unsigned | NO   | MUL | NULL    |                |
| value           | int(10) unsigned | NO   |     | NULL    |                |
| answers         | int(10) unsigned | YES  |     | 1       |                |
+-----------------+------------------+------+-----+---------+----------------+
That is a much easier thing to parse.

But there is a better solution still. When you get back a result set from a query you can use:

Code: Select all

mysql_field_name()
and there are a bunch of other functions that extract meta data from result sets.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

If I am not mistaken, he didn't want information from the query result but the actual SQL command string. I could be wrong, but that is the impression I was under.
mcccy005
Forum Contributor
Posts: 123
Joined: Sun May 28, 2006 7:08 pm

Post by mcccy005 »

Yes you are right Everah; thanks.

Well if you're that confident REGEX will be capable of what I'm trying to do then I guess I'll put coding aside for a couple of days and learn some stuff about REGEX.
I have had a look at a few basic tutorials including ones on these forums; but still none the wiser so I guess its a matter of re-reading the same stuff until it finally clicks!

Thanks
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

I can't with certainty that REGEX will do what you want. I am still try to wrap my mind around doing what you want to do, but regex may be a good place to start.
User avatar
n00b Saibot
DevNet Resident
Posts: 1452
Joined: Fri Dec 24, 2004 2:59 am
Location: Lucknow, UP, India
Contact:

Post by n00b Saibot »

or you could look into the highlight SQL code phpMyAdmin uses. it's pretty good at parsing different part of the query...
mcccy005
Forum Contributor
Posts: 123
Joined: Sun May 28, 2006 7:08 pm

Post by mcccy005 »

Hmm....I may have found a solution to this (as I also have no idea about phpmyadmin)
Note; I didnt write this piece of code; I found it published at http://au2.php.net/strings with the guys link to his website below (if you understand whatever language its written in!!)

Code: Select all

function get_string_between($string, $start, $end){
   $string = " ".$string;
     $ini = strpos($string,$start);
     if ($ini == 0) return "";
     $ini += strlen($start);    
     $len = strpos($string,$end,$ini) - $ini;
     return substr($string,$ini,$len);
}
And heres the implementation:

Code: Select all

$string = "this [custom] function is useless!!";
echo get_string_between($string,"[","]");
// must return "custom";
more functions at http://www.sphoera.com

All I need to do is change $start and $end to an array, so that I can find values between SELECT and 'AS' or 'FROM', with I suppose the values at the beginning of the array taking precedence.
User avatar
Ollie Saunders
DevNet Master
Posts: 3179
Joined: Tue May 24, 2005 6:01 pm
Location: UK

Post by Ollie Saunders »

Yep, something like get_string_between() could very well be useful for writing your own parsing code.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

if you understand whatever language its written in!!)
it's Catalan
mcccy005
Forum Contributor
Posts: 123
Joined: Sun May 28, 2006 7:08 pm

Post by mcccy005 »

Alrighty, starting off; what is Catalan????

More importantly, I think I've fixed that thing up so that it will accept an array as the $start AND OR $end values as well as a simple string/char value. It hasn't been tested as yet but thought it might be helpful to someone besides myself who always seems to code in a weird way that nobody else has done.

Code: Select all

function get_string_between($string, $start, $end)
    { 
        $string = " ".$string;
        $start_pos;    //stores the first element number whereby an element in $start matches a char/string in $string if $start is an array
        $end_pos;    //stores the first element number whereby an element in $end matches a char/string in $string if $end is an array
        if(is_array($start))    //checks if $start is an array and calls this code
        {
            //Searches through $string and sets $ini to the FIRST element in $start which is found in $string
            for($i=0; $i<sizeof($start); $i++)
            {
                if(!strpos($string, $start[$i]));    //if the char/string in the array '$start' at element $i does not exist, do nothing and repeat the loop
                /*if strpos($string, $end[$i]) returns false (ie. $end [$i] cannot be found in $string) AND this is the last element of the array, 
                *  then return an empty string. */
                else if(!strpos($string, $end[$i]) && $i=sizeof($end)) return " ";
                else
                {
                    $ini=strpos($string, $start[$i]);    //if the char/string in $start[$i] exists, set $ini to the string position of this value
                    $start_pos=$i;
                    break;    //exit the for loop
                }
            }                
        }
        //else if $start is NOT an array
        else if(!strpos($string,$start)) return "";    //if $start cannot be found in $string return an empty string
        else $ini=strpos($string, $start);    //set $ini to the position which the char/string $start is found in $string
        
        //if $start IS an array, add the length of the first found match of $start to $ini
        if(is_array($start)) $ini+=strlen($start[$start_pos]);
        //add the length of $start to $ini
        else $ini += strlen($start);
        
        if(is_array($end))    //checks if $end is an array and calls this code
        {
            //Searches through $string and finds the FIRST element in $end which matches a char/string in $string
            for($i=0; $i<sizeof($end); $i++)
            {
                if(!strpos($string, $end[$i]));    //if the char/string in the array '$end' at element $i does not exist, do nothing and repeat the loop
                /*if strpos($string, $end[$i]) returns false (ie. $end [$i] cannot be found in $string) AND this is the last element of the array, 
                *  then return an empty string. */
                else if(!strpos($string, $end[$i]) && $i=sizeof($end)) return " ";
                else
                {
                    $ini=strpos($string, $end[$i]);    //if the char/string in $start[$i] exists, set $ini to the string position of this value
                    $end_pos=$i;
                    break;    //exit the for loop
                }
            }
            if(is_array($start)    $len=strpos($start[$start_pos], $end[$end_pos], $ini) - $ini;
            else $len=strpos($string, $end[$end_pos], $ini) - $ini;
        }
        //else if $end is NOT an array
        $len = strpos($string,$end,$ini) - $ini; 
        return substr($string,$ini,$len);
Sorry about the messiness with the cut and paste; but you get the idea!
jmut
Forum Regular
Posts: 945
Joined: Tue Jul 05, 2005 3:54 am
Location: Sofia, Bulgaria
Contact:

Post by jmut »

mcccy005 wrote:havent heard of regex before so will look into that
basically I'm developing objects/classes to make creating a set of results much much easier (well...easier for future projects and code re-use).
Theres basically a Result_Set class (all results on each page); Result_Field (each row of the table of results) and a Result_Cell class (each individual cell in the table).

The results page will be set up to basically accept either the results manually; or preferably, accept a database connection and an sql query.
If I can break apart the query, I can dynamically use it to create a new result_set object (which contains the fields and cells objects).

What do you mean by 'log' the query???
SQL syntax can get VERY VERY complex....there are aggragation functions... short names, full names etc etc....
At what point are the queries you want to parse generated. I think you should change the approach.
Still not sure what exactly you want to achieve.
Post Reply