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

Question

select * from A left join B on A.columnc=B.columnd

results returned by above SQL will include both columns of A and B.

And what if A and B have some columns with the same name?

How to retrieve the value from PHP?

Answer 1

You should use column aliases in the select statement.

Answer 2

You probably want to be more explicit in your query. That way you can provide aliases for your columns:

SELECT
  A.foo as a_foo,
  B.foo as b_foo
FROM A
LEFT JOIN B ON A.columnc = B.columnd

Answer 3

The answer is actualy in the PHP documentation:

"If two or more columns of the result have the same field names, the last column will take precedence. To access the other column(s) of the same name, you either need to access the result with numeric indices by using mysql_fetch_row() or add alias names. See the example at the mysql_fetch_array() description about aliases. "

Especialy mysql_fetch_array() seems to be the best candidate when you insist on using star in the select statement:

$row = mysql_fetch_array($result, MYSQL_BOTH)

Then you can refer to the unambigous fields by $row[name] and to the ambigous one by $row[col_number], but that limits portability of your code (maybe next version of MySQL is going to return columns in a different order?). Recommended solution is to rewrite your query and list all the required fields instead of using star and for the ambigous ones - use aliases.

Answer 4

In Java+MySQL from the ResultSet object, you can use for example getString("a.id") and also getString("b.id"), if your query was like "SELECT a.id, b.id FROM a,b"

I dunno if there is something like this in PHP.

Regards