How to determine the original field table from a mysql query
I have two dynamic tables (tabx and taby) that are created and maintained via php interface where columns can be added, removed, renamed, etc. I want to read all columns at the same time from two tables:
select * from tabx, taby where ...;
I want to know from the query result if each column was from tabx or taby - is there a way to get mysql to return fully qualified column names eg. tabx.col1, tabx.col2, taby.coln, etc.
In PHP, you can get information about a field from the result, for example (stolen from a project I wrote a long time 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 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 directly answer your question, the table name data is always sent to MySQL. It is 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 make aliases explicitly, such as the one suggested by @Shabbyrobe.
a source to share
Leaving aside any questions about why you might want to do this, and why you would want to do a cross here at all, here's the best way I can think of from my head.
You can try EXPLAIN on each table and build a select statement from the result. Here's a bad example script that will give you a dynamically generated list of aliased fields. This will increase the number of queries you run as each table in the dynamically generated query will trigger an EXPLAIN query to run (although this could easily be mitigated with caching).
<?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: table and column name separated by two underscores (or any other demis, see the third parameter for aliasFields ()):
// ABOVE PROGRAM 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 simply blow up each field name with the same delimiter to get the table name and field name.
John Dutat's answer is much better than above. It would be helpful if the field metadata were not returned by the database as the PDO threat might be associated with some drivers.
Here's a simple snippet of how to do what John suggested 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 />";
}
}
}
a source to share
I'm left wondering what you are trying to achieve. First of all, adding and removing columns from a table is a strange practice; this means that the schema of your data changes at runtime.
In addition, for a simultaneous query from two tables, there must be some relationship between them. Rows in one table must be mapped in some way to rows in another table. If it is not, you are better off making two separate SELECT queries.
The answer to your question has already been asked: SELECT tablename. * to extract all columns from a given table. This may or may not work correctly if both tables have columns with the same name; you should look it up in the documentation.
Could you please give us more information about the problem you are trying to solve? I think you have a good chance that you will go down this path in the wrong way.
a source to share