有没有办法在PHP中按表名分隔MySQL查询结果？

Example:

SELECT chapter.id, book.title, book.author FROM chapter 
INNER JOIN book ON (chapter.book = book.id)

After fetching that query, I would like to get something like this:

获取该查询后,我想得到这样的东西:

Array
(
    [0] => Array
        (
            [chapter] => Array
                (
                    [id] => 1
                )

            [book] => Array
                (
                    [title] => Lorem Ipsum
                    [author] => John Doe
                )

         )

)

Better yet, stdClass objects.

更好的是,stdClass对象。

Well, after searching and working for some time, I've found a way of doing what I want by parsing the SQL statement with php-sql-parser, But this is really not a good solution for my issue.

好了,经过一段时间的搜索和工作,我找到了一种通过php-sql-parser解析SQL语句来实现我想要的方法,但这对我的问题来说真的不是一个好的解决方案。

I have specific reasons for this, and a solution would be a great benefit for my project. Though I don't really think that there is a simpler way of doing this...

我有具体的理由,解决方案对我的项目来说是一个很大的好处。虽然我真的不认为有一种更简单的方法可以做到这一点......

3 个解决方案

#1

One approach would be to name (alias) the columns of your result set in such a way that your PHP code knows where to "file" them, e.g. with the table and column split with ::

一种方法是命名(别名)结果集的列,以便PHP代码知道在哪里“归档”它们,例如:将表和列拆分为::

SELECT chapter.id as `chapter:id`, book.title as `book:title`, book.author as `book:author` ...

Then when retrieving the results from PDO/mysqli, you can split the resulting keys to know where to store your data:

然后,当从PDO / mysqli检索结果时,您可以拆分生成的密钥以了解存储数据的位置:

$formatted_row = array();
foreach ( $db_row as $column_alias => $value )
{
     list($table, $column) = explode(':', $column_alias);
     $formatted_row[$table][$column] = $value;
}

#2

The fetch_fields() function will give you the information you need. The script

fetch_fields()函数将为您提供所需的信息。剧本

<?php
$sql = "
SELECT u.user, h.db, RAND() x FROM mysql.user u
LEFT JOIN mysql.host h ON h.host = u.host
";
$conn = new mysqli('localhost', 'root', '', 'mysql');
$stmt = $conn->query($sql);
$fields = $stmt->fetch_fields();
print_r($fields);

produces the following output:

产生以下输出:

Array(
    [0] => stdClass Object(
            [name] => user
            [orgname] => User
            [table] => u
            [orgtable] => user
            [def] => 
            [db] => mysql
            ...
        )
    [1] => stdClass Object(
            [name] => db
            [orgname] => Db
            [table] => h
            [orgtable] => host
            [def] => 
            [db] => mysql
            ...
        )
    [2] => stdClass Object(
            [name] => x
            [orgname] => 
            [table] => 
            [orgtable] => 
            [def] => 
            [db] => 
            ...
        )
)

so you can use the db and orgtable fields to determine what table each field came from.

因此,您可以使用db和orgtable字段来确定每个字段来自哪个表。

#3

$query         = $myPdoInstance->prepare('SELECT lorem FROM ipsum...');
$metaData      = array();
$data          = new stdClass;
$i             = 0;
$tablesIdField = 'id';

$query->execute();

while ($columnsMeta = $query->getColumnMeta($i++)) {
    $metaData[] = $columnsMeta;
}

foreach ($query->fetch(PDO::FETCH_NUM) as $key => $columnValue) {

    $currentTable = $meta[$key]['table'];

    if (!isset($data->{$currentTable})) {
        $data->{$currentTable} = new stdClass;
    } elseif (!is_object($data->{$currentTable})) {
        $prev                                    = $data->{$currentTable};
        $data->{$currentTable}                   = new stdClass;
        $data->{$currentTable}->{$tablesIdField} = $prev;
    }

    $data->{$currentTable}->{$meta[$key]['name']} = $columnValue;
}

This is a very simple example of what I did. It is quite a solution for my project. The code I'm using in my project is too complex and class implemented for me to post it here.

这是我所做的一个非常简单的例子。这对我的项目来说是一个很好的解决方案我在我的项目中使用的代码太复杂了,我实现了类在这里发布。

So, if there is any syntax error in this example, I'm sorry!

所以,如果这个例子中有任何语法错误,我很抱歉!

#1