将 SQL 查询的 SELECT 子句解析为 PHP 数组 [英] Parse SELECT clause of SQL queries into a PHP array

问题描述

这更多的是用于在将查询发送到服务器之前在 PHP 中分析查询.很复杂，为什么我要这样做，所以我不想深入探讨这个原因.

This is more for analyzing a query in PHP BEFORE it's sent to the server. Very complicated why im doing this, so i'd rather not go into the reason for this.

在 PHP 中，我需要将字段选择存储到一个 php 数组中.以这个查询为例:

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

因此，在这种情况下，我需要将user_id,username,DATE(join_datetime) 存储为 join_date，(SELECT COUNT(1) FROM foobar WHERE foonum IN (5,4,6) and user_id = users.user_id) 作为myfoo_count" 到一个由逗号 (,) 展开的数组中.所以我会得到:

So, 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 im stuck on trying to explode the fields by comma. The main problem being with subqueries which might have commas in them too (see example).

感谢您的帮助！

推荐答案

您将不得不编写一个几乎与 MySQL 的查询解析器(用 YACC/Bison for C 编写)一样复杂的解析器.它不会是一个正则表达式或一个小的字符串操作.这是一种非常规语言，没有实际的解析器就无法解析它们.

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.

您也不能仅仅通过字符串查找逗号和括号，SQL 比这复杂得多.您可以在表达式、函数调用、条件逻辑等中使用表达式.所有这些都可以用逗号和括号任意深度嵌套.

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

如果您真的想用 PHP 做到这一点，那么您还有一项艰巨的任务要做.

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

这篇关于将 SQL 查询的 SELECT 子句解析为 PHP 数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！