how to mysql_fetch_array on joined tables, but columns have same name [duplicate]

Question

Possible Duplicate:
How to fetch result from MySQL row with multiple same-name columns with PHP?

I have two tables, and they share similar column names.

Query is:

SELECT a.name,b.name
FROM tablea a 
JOIN tableb b ON a.id = b.id

Results are put into an array:

while ($row = mysql_fetch_array($results)){
   $aname = $row['name'];
}

Once I added in that second table I noticed the $aname was using tableb's data.

Question(s): How can I store both name columns, $row['a.name'] does not work. My guess is maybe I need to alias each result in the query. Any suggestions? Should I same avoid giving the column names in the future?

---

I know mysql_* is deprecated. Save your energy.

Answer 1

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

Answer 2

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