4

This is more for analysing a query in PHP before it's sent to the server. Very complicated why I'm doing this, so I'd rather not go into the reason for this.

In PHP, I need to store the field selections into a PHP array. So take this query for example:

SELECT user_id,username,DATE(join_datetime) as join_date, (SELECT COUNT(1) FROM foobar WHERE foonum IN (5,4,6) and user_id = users.user_id) as myfoo_count 
FROM users 
WHERE user_id = 123

In this case I need to store user_id,username,DATE(join_datetime) as join_date, (SELECT COUNT(1) FROM foobar WHERE foonum IN (5,4,6) and user_id = users.user_id) as myfoo_count into an array exploded by a comma (,). So I would get:

array (
  [1] => 'user_id',
  [2] => 'username',
  [3] => 'DATE(join_datetime) as join_date',
  [4] => '(SELECT COUNT(1) FROM foobar WHERE foonum IN (5,4,6) and user_id = users.user_id) as myfoo_count'
)

I have gotten as far as extracting the fields part of the query, but I'm stuck on trying to explode the fields by comma. The main problem being with subqueries which might have commas in them too (see example).

Improve this question
3
  • 2
    Well, its got nothing to do with that. Its a situation which would take me a novel to explain why. Its not bad design or laziness, i just don't want to bore everyone with the uneeded details of the project. Commented Feb 11, 2011 at 12:33
  • 1
    X-Ref: SQL parser in PHP? Commented Jul 9, 2013 at 8:01
  • David, anybody who doubts the usefulness of a parser for a declarative language within PHP isn't worth arguing with. Note, I wrote the PHP SQL parser referenced in the X-Ref. Commented Oct 6, 2013 at 3:50

3 Answers 3

Reset to default
7

For anyone coming across this question in the future someone has already gone to the trouble of writting an SQL parser in PHP.

At present it supports SELECT, INSERT, UPDATE, DELETE and REPLACE statements.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

5

You would have to write a parser almost as complex as MySQL's query parser (written in YACC/Bison for C). It's not going to be a regular expression or a little string manipulation. This is a nonregular language, you can't parse them without an actual parser.

You can't just walk through the string finding commas and parentheses either, SQL is much more complex than that. You have expressions within expressions, function calls, conditional logic, etc. all of which can be nested arbitrarily deep with commas and parentheses all over.

http://dev.mysql.com/doc/refman/5.0/en/expressions.html

If you really want to do this with PHP, you have a big job ahead of yourself.

Improve this answer

4 Comments

As noted in the second answer, I wrote an actual SQL parser for PHP because I need to parse and re-write SQL for sharding. It is called PHP-SQL-parser. code.google.com/p/php-sql-parser The parser is used by SugarCRM, is available as a Drupal plugin and more. It is very thorough.
Following the demise of Google code, PHP-SQL-parser source is now maintained on Github github.com/greenlion/PHP-SQL-Parser
@JustinSwanhart I'd love to use this parser but I couldn't get it configured to run on my system using a Docker container. Looks like the repo has been abandoned too.
Looks like the repo is abandoned? I just pushed a new release a couple of days ago with multiple PR from contributors, including updating for PHP 8.1. Lots of people use it. I am not sure what problem you are having but the documentation explains how to set up the library.
0

I am trying this:

https://github.com/greenlion/PHP-SQL-Parser

May be works

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.