Issue with join and 2 exact column names

Question

Hello all. Never came across this before...

SELECT ".TBL_USERS.".username, 
       ".TBL_USERS.".id,  <-----------|
       ".TBL_COMMENTS.".creator,      |
       ".TBL_COMMENTS.".comment,      |- same column name
       ".TBL_COMMENTS.".date,         |
       ".TBL_COMMENTS.".id  <---------|

As you can see, I am selecting two id columns... When i take them in PHP:

$userid = $row['id'];

How will I be able to realize which one is which?

Answer 1

This is one reason why ID is poor choice for an ID column. You will have to alias your column names to know which is which.

Answer 2

You need to use a column alias, with AS keyword:

Example:

(id) AS id1.........(id) AS id2

Now from your query result, you can get them like this:

$userid1 = $row['id1'];
$userid2 = $row['id2'];

More Info:

http://dev.mysql.com/doc/refman/5.0/en/select.html

Answer 3

USe AS to rename one of the ID column in the table.

Answer 4

I've answered virtually the same question several times in the past:

• PHP MySQL mysql_fetch_assoc with array keys distinguished by 'as' designations
• Merging result from 2 columns with same name and not over-writing one
• Php pdo result of JOIN query

Answer 5

Remember... "AS" is not required for mysql, you can...

SELECT ".TBL_USERS.".username, 
       ".TBL_USERS.".id id1,  <-----------|
       ".TBL_COMMENTS.".creator,      |
       ".TBL_COMMENTS.".comment,      |- same column name
       ".TBL_COMMENTS.".date,         |
       ".TBL_COMMENTS.".id id2 <----------|