How to resolve ambiguous column names when retrieving results?

php sql mysql mysql-error-1052
Dan Burzo picture Dan Burzo · Jan 10, 2009 · Viewed 144.3k times · Source

I have two tables in my database:

NEWS table with columns:

  • id - the news id
  • user - the user id of the author)

USERS table with columns:

  • id - the user id

I want to execute this SQL: 

SELECT * FROM news JOIN users ON news.user = user.id

When I get the results in PHP I would like to get associative array and get column names by $row['column-name']. How do I get the news ID and the user ID, having the same column name?

Answer

Christian C. Salvadó picture Christian C. Salvadó · Jan 10, 2009

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'

Related questions

How can I prevent SQL injection in PHP?

If user input is inserted without modification into an SQL query, then the application becomes vulnerable to SQL injection, like in the following example: $unsafe_variable = $_POST['user_input']; mysql_query("INSERT INTO `table` (`column`) VALUES ('$unsafe_variable')"); That's …

Read More
MySQL query String contains

I've been trying to figure out how I can make a query with MySQL that checks if the value (string $haystack ) in a certain column contains certain data (string $needle), like this: mysql_query(" SELECT * FROM `table` WHERE `column`.contains(…

Read More
How to 'insert if not exists' in MySQL?

I started by googling, and found this article which talks about mutex tables. I have a table with ~14 million records. If I want to add more data in the same format, is there a way to ensure the record I …

Read More