Page 1 of 2
using explode to get different characteristics of sql query
Posted: Thu Jul 13, 2006 2:58 am
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
Posted: Thu Jul 13, 2006 3:05 am
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.
Posted: Thu Jul 13, 2006 3:48 am
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???
Posted: Fri Jul 14, 2006 11:36 pm
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( ) ).
Posted: Sat Jul 15, 2006 2:47 am
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.
Posted: Sat Jul 15, 2006 4:59 am
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:
and there are a bunch of other functions that extract meta data from result sets.
Posted: Sat Jul 15, 2006 5:59 pm
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.
Posted: Sun Jul 16, 2006 1:15 am
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
Posted: Sun Jul 16, 2006 9:55 am
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.
Posted: Sun Jul 16, 2006 1:33 pm
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...
Posted: Tue Jul 18, 2006 6:35 am
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.
Posted: Tue Jul 18, 2006 6:49 am
by Ollie Saunders
Yep, something like get_string_between() could very well be useful for writing your own parsing code.
Posted: Tue Jul 18, 2006 6:52 am
by Weirdan
if you understand whatever language its written in!!)
it's Catalan
Posted: Wed Jul 19, 2006 7:44 am
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!
Posted: Wed Jul 19, 2006 8:00 am
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.