SQL从多个表中选择* [英] SQL Select * from multiple tables

问题描述

使用PHP / PDO / MySQL可以在对多个表进行选择时使用通配符，返回的数组键完全限定为避免列名冲突？

Using PHP/PDO/MySQL is it possible to use a wildcard for the columns when a select is done on multiple tables and the returned array keys are fully qualified to avoid column name clash?

例如：

SELECT * from table1，table2;

给出：

数组键是'table1.id'，'table2.id'，'table1.name'等。

Array keys are 'table1.id', 'table2.id', 'table1.name' etc.

我尝试了SELECT table1。*，table2。* ...，但是返回的数组键不完全限定，所以具有相同名称的列冲突并被覆盖。

I tried "SELECT table1.*,table2.* ..." but the returned array keys were not fully qualified so columns with the same name clashed and were overwritten.

推荐答案

是的，你可以。最简单的方法是使用pdo，虽然至少有一些其他扩展是有能力的。

Yes, you can. The easiest way is with pdo, although there's at least a few other extensions which are capable of it.

设置 PDO 对象，而不是 PDOStatment 。

$PDO->setAttribute(PDO::ATTR_FETCH_TABLE_NAMES, true);

就是这样。然后得到如 myTable.myColumn 的关联数组键。如果你获取一个对象，所以要小心，因为你需要访问属性

That's it. Then you get associative array keys like myTable.myColumn. It works if you fetch an object too so beware, because you need to access the properties like

$obj->{'myTable.myColumn'};

* 手动说该属性仅受某些驱动程序支持。

*The manual says that attribute is only supported by certain drivers. If the above doesn't work, this might work instead.

$pdoStatement->setFetchMode(PDO::FETCH_NUM);
$pdoStatement->execute();
//build our associative array keys
$qualifiedColumnNames = array();
for ($i = 0; $i < $pdoStatement->columnCount(); $i++) {
    $columnMeta = $pdoStatement->getColumnMeta($i);
    $qualifiedColumnNames[] = "$columnMeta[table].$columnMeta[name]";
}

//fetch results and combine with keys
while ($row = $pdoStatement->fetch()) {
    $qualifiedRow = array_combine($qualifiedColumnNames, $row);
    print_r($qualifiedRow);
}

---

使用相同的基本模式对于其他数据库扩展

---

Same basic pattern is used for other database extensions

$res = mysql_query($sql);
//build our associative array keys
$qualifiedColumnNames = array();
for ($i = 0; $i < mysql_num_fields($res); $i++) {
    $columnMeta = mysql_fetch_field($res, $i);
    $qualifiedColumnNames[] = "$columnMeta[table].$columnMeta[name]";
}

//fetch results and combine with keys
while ($row = mysql_fetch_row($res)) {
    $qualifiedRow = array_combine($qualifiedColumnNames, $row);
    print_r($qualifiedRow);
}

---

mysqli

---

mysqli

$res = $mysqli->query($sql);
//build our associative array keys
$qualifiedColumnNames = array();
foreach ($res->fetch_fields() as $columnMeta) {
    $qualifiedColumnNames[] = "{$columnMeta->table}.{$columnMeta->name}";
}

//fetch results and combine with keys
while ($row = $res->fetch_row()) {
    $qualifiedRow = array_combine($qualifiedColumnNames, $row);
    print_r($qualifiedRow);
}

这篇关于SQL从多个表中选择*的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！