Divide a SQL statement

[jcgonz]

Hi guys!! help needed over here!

I need to be able to figure out the "source code" of an aliased field in MySQL using Php.

For example:
select user_id, concat_ws(' ',user_name, user_lastname) as name, s.name as state, (6+4) as operation
from user as u, state as s

I need to get this array (or something similar)
[name] => concat_ws(' ',user_name, user_lastname)
[state] => s.name
[operation] => (6+4)

I tried with regex(ing) for a while without success... any help WILL BE appreciated!!

Thank you in advance!!

[Roger_Ramjet]

Not possible. Any SQL using AS will return the results using the alias, this applies to all RDBMS not just MySQL.

[jcgonz]

I know that it's not possible directly using the DBMS; that's the reason I was trying to do so parsing the source SQL statement with regular expressions.

Any help will be really appreciated!

[tsinka]

Hi,

I tried this code:

<?PHP
$sql = "select user_id as id, concat_ws(' ',user_name, user_lastname) as name, s.name as state, (6+4) as operation from user as u, state as s";

$match = preg_match_all("/(?<!from)[,\\s]([^,]*?|\\w*?\\(.*?\\)) as ([^,]*?)[,\\s]/i",$sql,$matches);

print_r($matches);
?>

Matches is the following array:

Array
(
    [0] => Array
        (
            [0] =>  user_id as id,
            [1] =>  concat_ws(' ',user_name, user_lastname) as name,
            [2] =>  s.name as state,
            [3] =>  (6+4) as operation
        )

[1] => Array
    (
        [0] => user_id
        [1] => concat_ws(' ',user_name, user_lastname)
        [2] => s.name
        [3] => (6+4)
    )

[2] => Array
    (
        [0] => id
        [1] => name
        [2] => state
        [3] => operation
    )

)

matches[1] and matches[2] contain the important data.

matches[1][n] and matches[2][n] are the corresponding values where matches[1] contains the field/calc and matches[2] contains the alias.

Thomas