PHP Classes

PHP SQL Parser Class: Parse SQL to extract the SQL query structure

Recommend this page to a friend!
     
  Info   Example   View files Files   Install with Composer Install with Composer   Download Download   Reputation   Support forum   Blog    
Ratings Unique User Downloads Download Rankings
Not enough user ratingsTotal: 123 All time: 9,471 This week: 41Up
Version License PHP version Categories
php-sql-parser 1.0.0GNU General Publi...5PHP 5, Databases, Parsers
Description 

Authors

Justin Swanhart
André Rothe


Contributor
Michael Cummings

This package can parse SQL to extract the SQL query structure.

It can take as parameter a SQL query string and returns the structure of a query split into its parts.

The package returns an associative array with the information of its parts like the query type, options, field names, values, where condition clause, etc..

Picture of Michael Cummings
  Performance   Level  

Level 3 StarStarStar

 Innovation award
Innovation award
Nominee: 7x

Winner: 1x

 

Example

<?php

/**
 * you cannot execute this script within Eclipse PHP
 * because of the limited output buffer. Try to run it
 * directly within a shell.
 */

namespace PHPSQLParser;
require_once dirname(__FILE__) . '/../vendor/autoload.php';

$sql = 'SELECT 1';
echo $sql . "\n";
$start = microtime(true);
$parser = new PHPSQLParser($sql, true);
$stop = microtime(true);
print_r($parser->parsed);
echo "parse time simplest query:" . ($stop - $start) . "\n";

/*You can use the constuctor for parsing. The parsed statement is stored at the ->parsed property.*/
$sql = 'REPLACE INTO table (a,b,c) VALUES (1,2,3)';
echo $sql . "\n";
$start = microtime(true);
$parser = new PHPSQLParser($sql);
$stop = microtime(true);
print_r($parser->parsed);
echo "parse time very somewhat simple statement:" . ($stop - $start) . "\n";

/* You can use the ->parse() method too. The parsed structure is returned, and
   also available in the ->parsed property. */
$sql = 'SELECT a,b,c
          from some_table an_alias
    where d > 5;';
echo $sql . "\n";
print_r($parser->parse($sql, true));

$sql = 'SELECT a,b,c
          from some_table an_alias
      join `another` as `another table` using(id)
    where d > 5;';
echo $sql . "\n";
$parser = new PHPSQLParser($sql, true);
print_r($parser->parsed);

$sql = 'SELECT a,b,c
          from some_table an_alias
      join (select d, max(f) max_f
                 from some_table
                where id = 37
                group by d) `subqry` on subqry.d = an_alias.d
    where d > 5;';
echo $sql . "\n";
$parser = new PHPSQLParser($sql);
print_r($parser->parsed);

$sql = "(select `c2`, `c```, \"quoted \'string\' \\\" with `embedded`\\\"\\\" quotes\" as `an``alias` from table table)
UNION ALL (select `c2`, `c```, \"quoted \'string\' \\\" with `embedded`\\\"\\\" quotes\" as `an``alias` from table table)";
$parser = new PHPSQLParser($sql);
print_r($parser->parsed);

$sql = "(select `c2`, `c```, \"quoted \'string\' \\\" with `embedded`\\\"\\\" quotes\" as `an``alias` from table table)
UNION (select `c2`, `c```, \"quoted \'string\' \\\" with `embedded`\\\"\\\" quotes\" as `an``alias` from table table)";
$parser = new PHPSQLParser($sql);
print_r($parser->parsed);

$sql = "select `c2`, `c```, \"quoted \'string\' \\\" with `embedded`\\\"\\\" quotes\" as `an``alias` from table table";
echo $sql . "\n";
$parser = new PHPSQLParser($sql);
print_r($parser->parsed);

$sql = "alter table xyz add key my_key(a,b,c), drop primay key";
echo $sql . "\n";
$parser = new PHPSQLParser($sql);
print_r($parser->parsed);

$sql = 'INSERT INTO table (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id), c=3;';
echo $sql . "\n";
$parser = new PHPSQLParser($sql);
print_r($parser->parsed);

$sql = 'UPDATE t1 SET col1 = col1 + 1, col2 = col1;';
echo $sql . "\n";
$parser = new PHPSQLParser($sql);
print_r($parser->parsed);

$sql = 'DELETE FROM t1, t2 USING t1 INNER JOIN t2 INNER JOIN t3
WHERE t1.id=t2.id AND t2.id=t3.id;';
echo $sql . "\n";
$parser = new PHPSQLParser($sql);
print_r($parser->parsed);

$sql = 'delete low_priority partitioned_table.* from partitioned_table where partition_id = 1;';
echo $sql . "\n";
$parser = new PHPSQLParser($sql);
print_r($parser->parsed);

$sql = "UPDATE t1 SET col1 = col1 + 1, col2 = col1;";
echo $sql . "\n";
$parser = new PHPSQLParser($sql);
print_r($parser->parsed);

$sql = 'insert into partitioned_table (partition_id, some_col) values (1,2);';
echo $sql . "\n";
$parser = new PHPSQLParser($sql);
print_r($parser->parsed);

$sql = 'delete from partitioned_table where partition_id = 1;';
echo $sql . "\n";
$parser = new PHPSQLParser($sql);
print_r($parser->parsed);

$sql = 'SELECT 1';
echo $sql . "\n";
$parser = new PHPSQLParser($sql);
print_r($parser->parsed);

$sql = 'SHOW TABLE STATUS';
echo $sql . "\n";
$parser = new PHPSQLParser($sql);
print_r($parser->parsed);

$sql = 'SHOW TABLES';
echo $sql . "\n";
$parser = new PHPSQLParser($sql);
print_r($parser->parsed);

$sql = 'select DISTINCT 1+2 c1, 1+ 2 as
`c2`, sum(c2),"Status" = CASE
        WHEN quantity > 0 THEN \'in stock\'
        ELSE \'out of stock\'
        END
, 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 natural join t4 t4_x using(cX) 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';

echo $sql . "\n";
$parser = new PHPSQLParser($sql);
print_r($parser->parsed);

$sql = "(select 1, 1, 1, 1 from dual dual1) union all (select 2, 2, 2, 2 from dual dual2) union all (select c1,c2,c3,sum(c4) from (select c1,c2,c3,c4 from a_table where c2 = 1) subquery group by 1,2,3) limit 10";
echo $sql . "\n";
$parser = new PHPSQLParser($sql);
print_r($parser->parsed);

$sql = 'select DISTINCT 1+2 c1, 1+ 2 as
`c2`, sum(c2),"Status" = CASE
        WHEN quantity > 0 THEN "in stock"
        ELSE "out of stock"
        END
, t4.c1, (select c1+c2 from t1 table limit 1) as subquery into @a1, @a2, @a3 from `table` the_t1 left outer join t2 using(c1,c2) join
(select a, b, length(concat(a,b,c)) from ( select 1 a,2 b,3 c from some_Table ) table ) subquery_in_from join t3 as tX on tX.c1 = the_t1.c1 natural join t4 t4_x using(cX) 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
UNION ALL
SELECT NULL,NULL,NULL,NULL,NULL FROM DUAL LIMIT 1';

$start = microtime(true);
$parser = new PHPSQLParser($sql);
$stop = microtime(true);
echo "Parse time highly complex statement: " . ($stop - $start) . "\n";

?>

Details

PHP-SQL-Parser

A pure PHP SQL (non validating) parser w/ focus on MySQL dialect of SQL

Download

GitHub Wiki<br>

Full support for the MySQL dialect for the following statement types

SELECT
INSERT
UPDATE
DELETE
REPLACE
RENAME
SHOW
SET
DROP
CREATE INDEX
CREATE TABLE
EXPLAIN
DESCRIBE

Other SQL statement types

Other statements are returned as an array of tokens. This is not as structured as the information available about the above types. See the ParserManual for more information.

Other SQL dialects

Since the MySQL SQL dialect is very close to SQL-92, this should work for most database applications that need a SQL parser. If using another database dialect, then you may want to change the reserved words - see the ParserManual. It supports UNION, subqueries and compound statements.

External dependencies

The parser is a self contained class. It has no external dependencies. The parser uses a small amount of regex.

Focus

The focus of the parser is complete and accurate support for the MySQL SQL dialect. The focus is not on optimizing for performance. It is expected that you will present syntactically valid queries.

Manual

ParserManual - Check out the manual.

Example Output

Example Query

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

Example Output (via print_r)

Array
( 
    [OPTIONS] => Array
        (
            [0] => STRAIGHT_JOIN
        )       
        
    [SELECT] => Array
        (
            [0] => Array
                (
                    [expr_type] => colref
                    [base_expr] => a
                    [sub_tree] => 
                    [alias] => `a`
                )

            [1] => Array
                (
                    [expr_type] => colref
                    [base_expr] => b
                    [sub_tree] => 
                    [alias] => `b`
                )

            [2] => Array
                (
                    [expr_type] => colref
                    [base_expr] => c
                    [sub_tree] => 
                    [alias] => `c`
                )

        )

    [FROM] => Array
        (
            [0] => Array
                (
                    [table] => some_table
                    [alias] => an_alias
                    [join_type] => JOIN
                    [ref_type] => 
                    [ref_clause] => 
                    [base_expr] => 
                    [sub_tree] => 
                )

        )

    [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] => 
                )

        )

)

  Files folder image Files (575)  
File Role Description
Files folder image.settings (4 files)
Files folder imageexamples (2 files)
Files folder imagelibs (1 directory)
Files folder imagesrc (1 directory)
Files folder imagetests (2 files, 2 directories)
Files folder imagevendor (1 file)
Files folder imagewiki (5 files)
Accessible without login Plain text file .buildpath Data Auxiliary data
Accessible without login Plain text file .eclipse-PHP-formatter.xml Data Auxiliary data
Accessible without login Plain text file .project Data Auxiliary data
Accessible without login Plain text file composer.json Data Auxiliary data
Accessible without login Plain text file LICENSE Lic. License text
Accessible without login Plain text file phpunit.xml.dist Data Auxiliary data
Accessible without login Plain text file README.md Doc. Documentation

The PHP Classes site has supported package installation using the Composer tool since 2013, as you may verify by reading this instructions page.
Install with Composer Install with Composer
Downloadphp-sql-parser-2018-11-20.zip 637KB
Downloadphp-sql-parser-2018-11-20.tar.gz 159KB
Install with ComposerInstall with Composer
 Version Control Unique User Downloads Download Rankings  
 100%
Total:123
This week:0
All time:9,471
This week:41Up

  Applications that use this package  
No pages of applications that use this class were specified.

Add link image If you know an application of this package, send a message to the author to add a link here.