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.

Answer 1

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'

Answer 2

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

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

Answer 3

You can do something like

SELECT news.id as news_id, user.id as user_id ....

And then $row['news_id'] will be the news id and $row['user_id'] will be the user id

Answer 4

Thanks everybody for the prompt answers. Aliases seem to be the way to go!

Answer 5

If you don't feel like aliassing you can also just prefix the tablenames.

This way you can better automate generation of your queries. Also, it's a best-practice to not use select * (it is obviously slower than just selecting the fields you need Furthermore, only explicitly name the fields you want to have.

SELECT
    news.id, news.title, news.author, news.posted, 
    users.id, users.name, users.registered 
FROM 
    news 
LEFT JOIN 
    users 
ON 
    news.user = user.id

Answer 6

THis helped me, thx.

And now there are somany fields in my table i cant write all those (instead of *).

Now how can i do this...?

select user.id as uid, news.id as nid, [and other fields] from news, user where 1

in the [and other fields] place i need to write all the fields instead how ?

Thanks, anand.

Answer 7

I just figured this out. It's probably a bad practice but it worked for me in this case.

I am one of the lazy people who doesn't want to alias or write out every column name with a table prefix.

You can select all of the columns from a specific table by using table_name.* in your select statement.

When you have duplicated column names, mysql will overwrite from first to last. The data from the first duplicated column name will be overwritten when it encounters that column name again. So the duplicate column name that comes in last wins.

If I am joining 3 tables, each containing a duplicated column name, the order of the tables in the select statement will determine what data I am getting for the duplicate column.

Example:

SELECT table1.* , table2.* , table3.* FROM table1 LEFT JOIN table2 ON table1.dup = table2.dup LEFT JOIN table3 ON table2.dup = table3.dup;

In the example above, the value of dup I get will be from table3.

What if I want dup to be the value from table1?

Then I need to do this:

SELECT table3.* , table2.* , table1.* FROM table1 LEFT JOIN table2 ON table1.dup = table2.dup LEFT JOIN table3 ON table2.dup = table3.dup;

Now, table1 comes last, so the value of dup will be the value from table1.

I got the value I wanted for dup without having to write out every single freaking column and I still get all of the columns to work with. Yay!

I know the value of dup should be the same in all 3 tables, but what if table3 doesn't have a matching value for dup? Then dup would be blank in the first example, and that would be a bummer.

Answer 8

Is there a way to do this automatically, so that news.* will be news_* and users.* will be users_* ?

Because if I have to alias 20 fields it's going to be a lot of code.

Answer 9

@rebellion "Is there a way to do this automatically, so that news.* will be news_* and users.* will be users_* ?" // yes, if you can define your tables, name the columns with the (¿short?) tablename as prefix, that way the columns names will never be ambiguous.

Regards