Problem in PHP when joining tables having columns with the same name

Question

In MySQL I have joined two tables which both have a column named 'Name'. In PHP I am writing a script that retrieve the values of the columns and store it in variables:

$row = mysql_fetch_array($result);

$table1name = $row['Name'];   
$table2Name = $row['Name'];

Of course the variables will not give the values of the 2 columns, but they will both give the same value from the column of the second table. Is there a way, without changing the name of the columns in the database, to retrieve the distinct values of both columns?

Note, although you should alias similar names, this has more do with PHP's mysql_fetch_array() than MySQL. — Jason McCreary
– Jason McCreary, Commented May 20, 2011 at 22:08
This is an SQL issue (which is solved by using aliases), not a PHP problem... — dm76
– dm76, Commented May 31, 2011 at 9:44

dynamic · Accepted Answer · 2011-05-20 22:03:12Z

13

You need to use alias in mysql

SELECT table1.name AS name1

answered May 20, 2011 at 22:03

dynamic

48.2k56 gold badges163 silver badges236 bronze badges

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

Comments

kstev · Accepted Answer · 2011-05-20 22:04:34Z

8

yes. in your SQL query use the AS keyword to specify the desired alias.

Example:

SELECT `tbl_1`.`field` AS `tbl_1_field`, `tbl_2`.`field` AS `tbl_2_field` FROM....

back ticks are not required, that is just how I learned SQL

answered May 20, 2011 at 22:04

kstev

7401 gold badge5 silver badges17 bronze badges

3 Comments

OMG Ponies Over a year ago

AS isn't required to define an alias, but it does make it more obvious

Arjan Over a year ago

The keyword AS is also optional, at least in MySQL.

OMG Ponies Over a year ago

@yes123: it only gets worse, the higher your rep gets.

BugFinder · Accepted Answer · 2011-05-20 22:06:06Z

0

You can reference them in your mysql query with table qualifiers, eg tablea.id, and tableb.id, if you need both IDs in the results, you can do select tablea.id as id1, tableb.id as id2 etc.

answered May 20, 2011 at 22:06

BugFinder

17.9k4 gold badges42 silver badges58 bronze badges

2 Comments

OMG Ponies Over a year ago

Table aliasing will not solve the issue, only column aliasing will

BugFinder Over a year ago

I didnt say table aliases, I showed how to alias specific fields. I mentioned table qualifiers to reference them to then alias them

Collectives™ on Stack Overflow

Problem in PHP when joining tables having columns with the same name

3 Answers 3

Comments

3 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related