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.

Solution 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.

Solution 2

you can do this:

SELECT Table1.*,Table2.xyz, Table2.abc,... From...

where you get all columns from one table using "*" and then just the columns from the other table you need, so there is no clash.

You could also use column aliases, where you "rename" a column:

SELECT Table1.A AS T1_A,Table2.A AS T2_A,... From...

your result set would be of columns T1_A and T2_A

Solution 3

Unfortunately, no; there is no SQL syntax for ensuring that column names are unique.

If you truly don't know the names of the columns and must use SELECT *, your only real option would be to revert to some very ugly looking dynamic SQL that could inspect the structure of the tables and generate a query that would select them all explicitly with a table-name prefix.

I don't know which RDBMS you're using, but something like this should work on SQL Server:

declare @columns table (idx int identity(1,1), tablename varchar(100), columnname varchar(100))

insert into @columns (tablename, columnname) 
select tablename, columnname

from INFORMATION_SCHEMA.COLUMNS

where tablename in ('table_1', 'table_2')

declare @sql nvarchar(4000)

declare @i int
declare @cnt in

declare @col varchar(100)
declare @table varchar(100)

select @i = 0, @cnt = max(idx), @sql = '' from @columns

while @i < @cnt
begin
    select @i = @i + 1

    select @col = columnname, @table = tablename from @columns where idx = @i

    if len(@sql) > 0
        select @sql = @sql + ', '

    select @sql = @sql + '[' + @table + '].[' + @col + '] as [' + @table + '_' + @col + ']'
end

select @sql = 'select ' + @sql + ' from table_1, table_2'

exec sp_executesql @sql

Solution 4

Shamelessly repackaged from @goat:

// Workaround for setAttribute(PDO::ATTR_FETCH_TABLE_NAMES, true);
function pdoStatementExecuteAndFetchObjWithTableNames(PDOStatement $statement)
{
  $statement->setFetchMode(PDO::FETCH_NUM);
  $statement->execute();

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

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

NOTE: if you use:

SELECT 1 FROM my_table AS my_table_alias

then you will get my_table. I would have hoped for my_table_alias. I got this result with PHP 5.6 and sqlite driver.

View more solutions