php-sql-parser

To use the parser in your PHP project, follow these steps:

1. Download the stable version from the Downloads wiki and unzip it into your include directory.
2. Include the main file in your application using require_once('php-sql-parser.php').
3. Instantiate PHPSQLParser and call the parse() method to get the associative array representation of your SQL.

Generating keyword positions: If you need to know the exact position of elements within the original SQL string, pass true as the second argument to the parse() method. This will store the position within every base_expr entry.

require_once('php-sql-parser.php');

// Basic parsing
$parser = new PHPSQLParser();
$parsed = $parser->parse($sql);
print_r($parsed);

// Parsing with keyword positions
$parser = new PHPSQLParser();
$parsed = $parser->parse($sql, true);
print_r($parsed);

When parsing supported MySQL statements (like SELECT), the parser returns a structured associative array representing the components of the query (e.g., OPTIONS, SELECT, FROM, WHERE). Each component contains detailed information such as expression types (expr_type), base expressions (base_expr), aliases, and sub-trees.

Example of a parsed SELECT statement structure:

Array
(
    [OPTIONS] => Array
        (
            [0] => STRAIGHT_JOIN
        )       
    [SELECT] => Array
        (
            [0] => Array
                (
                    [expr_type] => colref
                    [base_expr] => a
                    [sub_tree] => 
                    [alias] => `a`
                )
            // ... other columns
        )
    [FROM] => Array
        (
            [0] => Array
                (
                    [table] => some_table
                    [alias] => an_alias
                    [join_type] => JOIN
                    // ... other table details
                )
        )
    [WHERE] => Array
        (
            [0] => Array
                (
                    [expr_type] => colref
                    [base_expr] => d
                    [sub_tree] => 
                )
            [1] => Array
                (
                    [expr_type] => operator
                    [base_expr] => >
                    [sub_tree] => 
                )
            [2] => Array
                (
                    [expr_type] => const
                    [base_expr] => 5
                    [sub_tree] => 
                )
        )
)

SELECT STRAIGHT_JOIN a, b, c 
  FROM some_table an_alias
 WHERE d > 5;

The PHPSQLParser class can be used to parse highly complex MySQL-dialect SQL statements, including those with subqueries, CASE statements, multiple JOIN types (LEFT, INNER, USING, ON), aggregate functions, and various clauses like HAVING, GROUP BY, and LIMIT.

When initializing the parser, you can pass a second boolean parameter to enable specific parsing behaviors (as shown in the example). The resulting parsed structure is accessible via the parsed property, which returns a multi-dimensional associative array representing the Abstract Syntax Tree (AST) of the query.

require_once('php-sql-parser.php');

$sql = 'select DISTINCT 1+2   c1, 1+ 2 as
`c2`, sum(c2),sum(c3) as sum_c3,"Status" = CASE
        WHEN quantity > 0 THEN \'in stock\' 
        ELSE \'out of stock\' 
        END case_statement
, t4.c1, (select c1+c2 from t1 inner_t1 limit 1) as subquery into @a1, @a2, @a3 from t1 the_t1 left outer join t2 using(c1,c2) join t3 as tX ON tX.c1 = the_t1.c1 join t4 t4_x using(x) where c1 = 1 and c2 in (1,2,3, "apple") and exists ( select 1 from some_other_table another_table where x > 1) and ("zebra" = "orange" or 1 = 1) group by 1, 2 having sum(c2) > 1 ORDER BY 2, c1 DESC LIMIT 0, 10 into outfile "/xyz" FOR UPDATE LOCK IN SHARE MODE';

$parser = new PHPSQLParser($sql, true);
print_r($parser->parsed);