how to mysql_fetch_array on joined tables, but columns have same name
Your guess was right. You need to create an ALIAS
for the columns so you can fetch it uniquely,
SELECT a.name Name1, b.name Name2
FROM tablea a
JOIN tableb b ON a.id = b.id
then you can now call
$row['Name1']
There is a way.
mysql_field_table()
will tell you a result set field's name given the $result
handle and ordinal position. In conjunction with mysql_field_name()
, that should be everything you need:
// Fetch the table name and then the field name
$qualified_names = array();
for ($i = 0; $i < mysql_num_fields($result); ++$i) {
$table = mysql_field_table($result, $i);
$field = mysql_field_name($result, $i);
array_push($qualified_names, "$table.$field");
}
You could wrap this into your own mysql_fetch_qualified_array
function, for example, to give you an associative array keyed on "table.field"
:
function mysql_fetch_qualified_array($result) {
...
// caching $qualified_names is left as an exercise for the reader
...
if ($row = mysql_fetch_row($result)) {
$row = array_combine($qualified_names, $row);
}
return $row;
}