CODEWITHSUNDEEP
phpmysqlsqljoininner-join
Yamaha32088
Yamaha32088

Reputation: 4163

MySQL not showing duplicate column names in separate tables using INNER JOIN

I have a database with 22 tables most tables have a few column fields with identical names. When I use INNER JOIN on the tables with the duplicate column names it will ignore them in the last table. Right now I am only joining 3 tables 

SELECT * FROM company C 
INNER JOIN typeofdealer PI ON C.CompanyID = PI.CompanyID 
INNER JOIN typeofservices TS ON PI.CompanyID = TS.CompanyID

So in this example typeofdealer and typeofservices both contain column names Other and OtherText When I get this query back I only see one Other and one OtherText.

I just discovered as I was writing this that if I do the query in "phpmyadmin" that I get it back exactly as intended. I am doing this query using prepared statements in php and outputting the results using:

echo "<PRE>";
print_r($results);
echo "</PRE>";

I believe the problem is that php arrays cannot contain duplicate fields so is there a way to circumvent the issue I am having?

Upvotes: 1

Views: 1079

Answers (2)

Justin Paul Pa&#241;o
Justin Paul Pa&#241;o

Reputation: 936

The workaround you can do for this is to use an alias AS to make a distinction between columns with the same names. Something like: 

SELECT PI.Other AS Other1, PI.OtherText AS OtherText2, TS.Other AS Other2, TS.OtherText AS OtherText2 FROM company C 
INNER JOIN typeofdealer PI ON C.CompanyID = PI.CompanyID 
INNER JOIN typeofservices TS ON PI.CompanyID = TS.CompanyID

Actually it will work the same way if you omit the AS keyword say SELECT PI.Other Other1, PI.OtherText OtherText2

Upvotes: 1

ollieread
ollieread

Reputation: 6301

The problem is, as you rightfully suggested, that PHP won't allow multiple entries into an array with the same key name.

The simplest way around this, is to alias the fields in the column selection section of the query, like so:

SELECT *, typeofservices.Other as ServicesOther, typeofservices.OtherText as ServicesOtherText FROM company C 
INNER JOIN typeofdealer PI ON C.CompanyID = PI.CompanyID 
INNER JOIN typeofservices TS ON PI.CompanyID = TS.CompanyID

It's not pretty but it's simple.

Upvotes: 1

Related Questions