問題描述
使用 PHP/PDO/MySQL 是否可以在對多個表進行選擇并且返回的數(shù)組鍵是完全限定的以避免列名沖突時對列使用通配符?
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;
給出:
數(shù)組鍵是'table1.id'、'table2.id'、'table1.name'等
Array keys are 'table1.id', 'table2.id', 'table1.name' etc.
我嘗試了SELECT table1.*,table2.* ..."，但返回的數(shù)組鍵不是完全限定的，因此具有相同名稱的列發(fā)生沖突并被覆蓋.
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.
Set the attribute on the PDO object, not the PDOStatment.
$PDO->setAttribute(PDO::ATTR_FETCH_TABLE_NAMES, true);
就是這樣.然后你會得到像 $row['myTable.myColumn'] 這樣的關聯(lián)數(shù)組鍵.如果您也獲取對象(例如通過 PDO::FETCH_OBJECT)，它也可以工作，所以要小心，因為您需要訪問像 $obj->{'myTable.myColumn'}<這樣的屬性/代碼>
That's it. Then you get associative array keys like $row['myTable.myColumn']. It works if you fetch an object too (eg via PDO::FETCH_OBJECT) so beware, because you need to access the properties like $obj->{'myTable.myColumn'}
*手冊說 PDO::ATTR_FETCH_TABLE_NAMES 屬性僅受某些驅動程序支持.如果上述方法不起作用，則可能可以代替.
*The manual says the PDO::ATTR_FETCH_TABLE_NAMES 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);
}
<小時>
相同的基本模式用于其他數(shù)據(jù)庫擴展
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
$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);
}
這也適用于表別名(在 php 7.1 中測試) - 限定列名將使用表別名.
This should also work with table aliases (tested in php 7.1) - the qualified column name will use the table alias.
這篇關于SQL 從多個表中選擇 *的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！