how to identify the source table of fields from a mysql query

Question

I have two dynamic tables (tabx and taby) which are created and maintained through a php interface where columns can be added, deleted, renamed etc. I want to read all columns simulataneously from the two tables like so;-

select * from tabx,taby where ... ;

I want to be able to tell from the result of the query whether each column came from either tabx or taby - is there a way to force mysql to return fully qualified column names e.g. tabx.col1, tabx.col2, taby.coln etc?

Answer 1

select * from tabx tx, taby ty where ... ;

Answer 2

Does:

SELECT tabx.*, taby.* FROM tabx, taby WHERE ...

work?

Answer 3

Leaving aside any questions about why you might want to do this, and why you would want to do a cross join here at all, here's the best way I can come up with off the top of my head.

You could try doing an EXPLAIN on each table and build the select statement programatically from the result. Here's a poor example of a script which will give you a dynamically generated field list with aliases. This will increase the number of queries you perform though as each table in the dynamically generated query will cause an EXPLAIN query to be fired (although this could be mitigated with caching fairly easily).

<?php
$pdo = new PDO($dsn, $user, $pass, array(PDO::ATTR_ERRMODE=>PDO::ERRMODE_EXCEPTION));
function aliasFields($pdo, $table, $delim='__') {
    $fields = array();
    // gotta sanitise the table name - can't do it with prepared statement
    $table = preg_replace('/[^A-z0-9_]/', "", $table);
    foreach ($pdo->query("EXPLAIN `".$table."`") as $row) {
        $fields[] = $table.'.'.$row['Field'].' as '.$table.$delim.$row['Field'];
    }
    return $fields;
}
$fieldAliases = array_merge(aliasFields($pdo, 'artist'), aliasFields($pdo, 'event'));
$query = 'SELECT '.implode(', ', $fieldAliases).' FROM artist, event';
echo $query;

The result is a query that looks like this, with the table and column name separated by two underscores (or whatever delimeter you like, see the third parameter to aliasFields()):

// ABOVE PROGRAM'S OUTPUT (assuming database exists)
SELECT artist__artist_id, artist__event_id, artist__artist_name, event__event_id, event__event_name FROM artist, event

From there, when you iterate over the results, you can just do an explode on each field name with the same delimeter to get the table name and field name.

---

John Douthat's answer is much better than the above. It would only be useful if the field metadata was not returned by the database, as PDO threatens may be the case with some drivers.

Here is a simple snippet for how to do what John suggetsted using PDO instead of mysql_*():

<?php

$pdo = new PDO($dsn, $user, $pass, array(PDO::ATTR_ERRMODE=>PDO::ERRMODE_EXCEPTION));

$query = 'SELECT artist.*, eventartist.* FROM artist, eventartist LIMIT 1';
$stmt = $pdo->prepare($query);
$stmt->execute();

while ($row = $stmt->fetch()) {
 foreach ($row as $key=>$value) {
  if (is_int($key)) {
   $meta = $stmt->getColumnMeta($key);
   echo $meta['table'].".".$meta['name']."<br />";
  }
 }
}

Answer 4

I'm left wondering what you are trying to accomplish. First of all, adding and removing columns from a table is a strange practice; it implies that the schema of your data is changing at run-time.

Furthermore, to query from the two tables at the same time, there should be some kind of relationship between them. Rows in one table should be correlated in some way with rows of the other table. If this is not the case, you're better off doing two separate SELECT queries.

The answer to your question has already been given: SELECT tablename.* to retrieve all the columns from the given table. This may or may not work correctly if there are columns with the same name in both tables; you should look that up in the documentation.

Could you give us more information on the problem you're trying to solve? I think there's a good chance you're going about this the wrong way.

Answer 5

In PHP, you can get the field information from the result, like so (stolen from a project I wrote long ago):

/*
Similar to mysql_fetch_assoc(), this function returns an associative array
given a mysql resource, but prepends the table name (or table alias, if
used in the query) to the column name, effectively namespacing the column
names and allowing SELECTS for column names that would otherwise have collided
when building a row's associative array.
*/
function mysql_fetch_assoc_with_table_names($resource) {
    // get a numerically indexed row, which includes all fields, even if their names collide
    $row = mysql_fetch_row($resource);
    if( ! $row)
        return $row;

    $result = array();

    $size = count($row);    
    for($i = 0; $i < $size; $i++) {
        // now fetch the field information
        $info = mysql_fetch_field($resource, $i);
        $table = $info->table;
        $name = $info->name;
        // and make an associative array, where the key is $table.$name
        $result["$table.$name"] = $row[$i];  // e.g. $result["user.name"] = "Joe Schmoe";
    }

    return $result;
}

Then you can use it like this:

$resource = mysql_query("SELECT * FROM user JOIN question USING (user_id)");
while($row = mysql_fetch_assoc_with_table_names($resource)) {
    echo $row['question.title'] . ' Asked by ' . $row['user.name'] . "\n";
}

So to answer your question directly, the table name data is always sent by MySQL -- It's up to the client to tell you where each column came from. If you really want MySQL to return each column name unambiguously, you will need to modify your queries to do the aliasing explicitly, like @Shabbyrobe suggested.