SQL Select * from multiple tables

Question

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?

example:

SELECT * from table1, table2;

gives:

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

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

Answer 1

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

pdo

Set the attribute on the PDO object, not the PDOStatment.

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

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'}

*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);
}

---

Same basic pattern is used for other database extensions

mysql

$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);
}

This should also work with table aliases (tested in php 7.1) - the qualified column name will use the table alias.