在PHP中获取没有别名的SQL-join-statement [英] fetch SQL-join-statement without alias in PHP

问题描述

以下我的代码：

$list = $pdo->prepare("SELECT * FROM table_a INNER JOIN table_b ON table_a.id = table_b.blabla_id");
$list_result = $list->execute();
while($element = $list->fetch()) {
    //CONTENT
}

现在我想用 echo $ element ['table_a.id']; -来获取列工作。
我不想为每列写一个别名。有办法解决吗？ ：）

Now I would like to fetch the columns with something like echo $element['table_a.id']; - which doesn't work. I don't want to write an alias for every single column. Is there a way to deal with this? :)

解决方案：

$list = $pdo->prepare("SELECT * FROM table_a INNER JOIN table_b ON table_a.id = table_b.blabla_id");
$list->execute();
while($element = $list->fetch(PDO::FETCH_ASSOC)) {
    $a = [];
    $i = 0;
    foreach ( $element as $k => $v ) {
        $meta = $list->getColumnMeta($i);
        $a[ $meta['table'] . '.' . $k ] = $v;
        $i++;
    }
    echo $a['table_b.blabla'];
}

如kmoser所述，有可能提高有效性，因为没有必要检查每个循环的列名，因为它们不会改变。

As kmoser mentioned, it's possible to improve the effectivity, as it's not necessary to check the column-names every loop, as they don't change.

感谢大家。

推荐答案

将呼叫固定为 $ list-> fetch（）之后，将其更改为 $ list_result -> fetch（），则可以使用 $ list_result-> getColumnMeta（$ i）来获取元信息（包括表名）位置 $ i 的列，其中 $ i 是结果集中的0索引列。

Once you've fixed your call to $list->fetch() by changing it to $list_result->fetch(), you can use $list_result->getColumnMeta($i) to get meta information (including the table name) of the column in position $i, where $i is the 0-indexed column in the result set.

然后可以遍历各列，检索其表名，并使用更新的键和原始数组中的值填充新数组：

You can then loop through the columns, retrieve their table names, and populate a new array with updated keys, and values from your original array:

while($element = $list->fetch()) {
    $a = []; // New array
    $i = 0;
    foreach ( $element as $k => $v ) { // For each element in the fetched row
        $meta = $list_result->getColumnMeta($i); // Get the meta info for column $i
        $a[ $meta->table . '.' . $k ] = $v; // E.g. $a[ 'table_a.id' ] = 'Foo'
        $i++; // Point to next column
    }
    $element = $a; // If you really need this variable name
}

现在可以使用 $ element ['table_a.id'] 。

您可能想通过仅循环浏览来提高示例效率元信息一次，因为每一列的表名在行与行之间都不会更改。

You'll probably want to make my example more efficient by only looping through the meta info once, since the table names for each column will not change from row to row.

请参见 https://www.php.net/manual/en/pdostatement.getcolumnmeta.php 了解更多信息。

这篇关于在PHP中获取没有别名的SQL-join-statement的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！