The function below takes a function and returns the col->table mapping as an array.
For example:
$query = "SELECT a.id AS a_id, b.id b_id FROM atable AS a, btable b"
$cols = queryAlias($query);
print_r($cols);
Returns:
Array
(
[a] => atable
[b] => btable
)
I can't promise it's perfect, but this function never hit production cause I ended up using mysqli methods instead.
Enjoy
-Jorge
/**
* Takes in a query and returns the alias->table mapping.
*
* @param string $query
* @return array of alias mapping
*/
function queryAlias ( $query ) {
//Make it all lower, we ignore case
$substr = strtolower($query);
//Remove any subselects
$substr = preg_replace ( '/\(.*\)/', ", $substr);
//Remove any special charactors
$substr = preg_replace ( '/[^a-zA-Z0-9_,]/', ' ', $substr);
//Remove any white space
$substr = preg_replace('/\s\s+/', ' ', $substr);
//Get everything after FROM
$substr = strtolower(substr($substr, strpos(strtolower($substr),' from ') + 6));
//Rid of any extra commands
$substr = preg_replace(
Array(
'/ where .*+$/',
'/ group by .*+$/',
'/ limit .*+$/' ,
'/ having .*+$/' ,
'/ order by .*+$/',
'/ into .*+$/'
), ", $substr);
//Remove any JOIN modifiers
$substr = preg_replace(
Array(
'/ left /',
'/ right /',
'/ inner /',
'/ cross /',
'/ outer /',
'/ natural /',
'/ as /'
), ' ', $substr);
//Replace JOIN statements with commas
$substr = preg_replace(Array('/ join /', '/ straight_join /'), ',', $substr);
$out_array = Array();
//Split by FROM statements
$st_array = split (',', $substr);
foreach ($st_array as $col) {
$col = preg_replace(Array('/ on .*+/'), ", $col);
$tmp_array = split(' ', trim($col));
//Oh no, something is wrong, let's just continue
if (!isset($tmp_array[0]))
continue;
$first = $tmp_array[0];
//If the "AS" is set, lets include that, if not, well, guess this table isn't aliased.
if (isset($tmp_array[1]))
$second = $tmp_array[1];
else
$second = $first;
if (strlen($first))
$out_array[$second] = $first;
}
return $out_array;
}