There are two columns in the new table(table1 join table2) which have the same column name, how to get the values of both respectively

Question

select * from table1 join table2 on table1.column3=table2.column4 where ...
...
$row=mysql_fetch_assoc($result);

However, there are two columns in the new table(table1 join table2) which have the same column name, how to get the values of both respectively?

Answer 1

Call the columsn out specifically with an alias like

SELECT table_1.id as table_1_id, table_2.id as table_2_id

Youll have to list out all or most of the columns form at least one of the tables this way but you can get access to cols with the same name across multiple tables.

Answer 2

Prefix the column name in the select with its table name.

select table1.my_column, table2.my_column
from table1, table2
where table1.id = table2.t1_id

But with this method, you would have to read the columns using their returned order indexes, rather than their names. Another answer mentioned using as to name each column, so consider that if you're going to read them by name.

Answer 3

When there's a column name collision due to a query joining 2+ tables, you can not use *. You can use:

SELECT table_1.*, 
       table_2.*
  FROM table_1, 
       table_2

If that doesn't return the list of columns you want, you will have to explicitly list every column. There's no way around - it's an all or nothing deal.

Table Aliases

...but typing out the full table name every time can be a pain, which is why you can alias tables:

SELECT t1.*, 
       t2.*
  FROM table_1 AS t1, 
       table_2 t2

Either alias notation is supported. They're also required if you want to join a table to itself.

Answer 4

Using only the column names in a Select list that you actually need\ is always the best, but when you want everything, then, well, go ahead and use the *:

Select a.*, 
       b.*, 
       a.id as a_id, 
       b.id as b_id,
       a.name as a_name,
       b.name as b_name
  from tablea a,
       tableb b
...

It won't hurt to be redundant, as a.* includes a_id and a_name, but there values from the * get lost in the associative array, so just put them back in with new, unique names.