Duplicate column names after JOIN in MySQL with PHP

Question

Consider two tables:

t1          t2
A   B       A   C
------      -----
1   2       3   4

I run this code:

$q = mysql_query("SELECT * FROM t1 LEFT JOIN t2 ON 1=1");
print_r(mysql_fetch_all($q));

And get this result:

array(
  A => 3
  B => 2
  C => 4
)

In all the cases I tried such thing, the value from the latest joined table goes to array (index A). The question is, can I count on that?
I know about aliases, but it would be much easier for me if I could know how Mysql+php behave in such case. Thank you.

@J-Dizzle: To get a result with a row, I assume. And it shows, from which of the columns with the same name the value is taken. — VMai
– VMai, Commented Jul 22, 2014 at 23:38
I think (and hope) the 1=1 join was just for this example. I also don't think the tables are named t1 and t2 ;) — Mike T
– Mike T, Commented Jul 22, 2014 at 23:45

Community · Accepted Answer · 2023-11-17 19:32:19Z

2

Assuming that you use

mysql_fetch_assoc()

then you can count on it:

From mysql_fetch_assoc

Return Values

If two or more columns of the result have the same field names, the last column will take precedence. To access the other column(s) of the same name, you either need to access the result with numeric indices by using mysql_fetch_row() or add alias names. See the example at the mysql_fetch_array() description about aliases.

Note

Please look at the red box. If you write new code for new projects consider moving to mysqli or PDO. And take the inpact of the deprecation of the mysql_* functions to your existing projects into account.

edited Nov 17, 2023 at 19:32

CommunityBot

11 silver badge

answered Jul 22, 2014 at 23:34

VMai

10.4k9 gold badges28 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Serge Seredenko Over a year ago

Thank you. Although I am still not sure about which column is 'last' when php gets the result from mysql, I figure it's possible to use select t1.*, t2.* to make it explicit.

Liam Over a year ago

The columns of the table in the FROM clause are first, followed by the columns of the table in the JOIN clause. Within each table, the columns are in the same order that you will see if you run DESC t1. The column order is t1.A, t1.B, t2.A, t2.C, and is consistent. In your query, t2.A will always be returned, replacing the value from t1.A , as it is the last out of those 2 that have the same name!

Liam Over a year ago

The same behaviour is in the newer mysqli functions also.

Mike T · Accepted Answer · 2014-07-22 23:44:01Z

0

You can specify aliases to help out here:

SELECT *, t1.a AS t1_a FROM ...

Aliases also help if you have operations in the select clause, eg: SUM(a*b) AS line_total

answered Jul 22, 2014 at 23:44

Mike T

1,29612 silver badges13 bronze badges

Collectives™ on Stack Overflow

Duplicate column names after JOIN in MySQL with PHP

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related