PHP & MYSQL: How to resolve ambiguous column names in JOIN operation?


Question

I have two tables in my database:

NEWS ('id' - the news id, 'user' - the user id of the author)

USERS ('id' - the user id)

I want to make a SELECT * FROM news JOIN users ON news.user = user.id, now when I get the results in PHP it's something like:

$row = mysql_fetch_array($result), and get column names by $row['column-name']... how do I get the news ID and the user ID, having the same column name?

UPDATE: Thanks everybody for the quick answers. Aliases seem the best solution.

1
71
1/27/2011 8:32:40 PM

Accepted Answer

You can set aliases for the columns that you are selecting:

$query = 'SELECT news.id AS newsId, user.id AS userId, [OTHER FIELDS HERE] FROM news JOIN users ON news.user = user.id'
115
1/10/2009 5:37:42 PM

You can either use the numerical indices ($row[0]) or better, use AS in the MySQL:

SELECT *, user.id AS user_id FROM ...


Licensed under: CC-BY-SA with attribution
Not affiliated with: Stack Overflow
Icon