Query String Match

[The Little Guy]

I have this method:

protected function get_tables(){
preg_match_all("/(from|join)(.+?)(left|join|on|where|order|;|$)/i", $this->query, $select);
print_r($select);
$tables = $select[2];
foreach($tables as $k => $v){
	$tables[$k] = preg_replace("/( as.+| .+)/", '', trim($v));
}
return $tables;
}

 

the preg_match_all line is the one I am having issues with.

 

If this is the string I am trying to match:

 

select * from users u join ratings r on(u.id = r.user_id) where email = 'something'

 

the method is supposed to get all the tables used in the query string. The function works if I do a left join, but once I remove the left, it only matches the first table "users".

 

How can I get it to match both tables, and others if I add any?

[gizmola]

Try this expression.  It will even match tables that have the `tablename` characters.

 

(?:from|join)\s[`]?(\w+)[`]?

 

There are some syntax variations where this will not work, but I'm assuming this is a tool for your use and not a generic filter for all valid mysql syntax.  For example, there are variations where you can have a list of tables like (table1, table2, table3, etc) .  This also won't work right for something like

 

select * from tablea, tableb WHERE

[The Little Guy]

Thanks works nice!

 

I just need it to grab comma separated tables too, so If you have any ideas let me know, otherwise I will be trying to find a solution to that.

[The Little Guy]

Oh!

This seems to work:

(?:from|join|,)\s[`]?(\w+)[`]?

[The Little Guy]

maybe not...

 

It looks like that matches comma separated columns too.

[gizmola]

Yeah there is a point at which, for this problem, regex isn't the best solution.  You might be better off having a little procedural code that finds the location of 'from' and 'join' and looks at all the string after that. 

[The Little Guy]

Here is my final result:

 

	protected function get_tables(){
	preg_match("/from(.+?)(where|$)/i", $this->query, $matches);
	$t1 = preg_split("/left|join|on.+?\(.+?\)|,/i", $matches[1]);
	$tables = array();
	foreach($t1 as $table){
		$tbls = preg_split("/as|\s/i", $table);
		$tbls = array_unique($tbls);
		$tbls2 = array();
		foreach ($tbls as $t){
			if(!empty($t))
				$tbls2[] = $t;
		}
		$tables[] = $tbls2;
	}
	$i = 0;
	$opt = array();
	foreach($tables as $tbl){
		if(!empty($tbl)){
			if(count($tbl) == 2){
				$opt[$tbl[1]] = str_replace('`', '', $tbl[0]);
			}else{
				$opt[$i] = str_replace('`', '', $tbl[0]);
			}
		}
		$i++;
	}
	return $opt;
}