Nov/090
MySql Multiple Databases with Same Column Name
An interesting problem came one day as I was trying to get data from multiple databases. As this is an easy topic, I had a hard time of getting it to display in my php code. The problem is using the same column names on the tables. While MySql allows this PHP has a hard time distinguishing.
Example
Lets say I have the 3 tables below Customer, Business and Invoice. Notice that Customer and Business have the same columns.
Customer [id, name, phone]
Business [id, name, phone]
Invoice [id, business, customer, description]
A normal SQL SELECT statement for this would be
SELECT c.name, c.phone, b.name, b.phone, i.description
FROM Customer c, Business b, Invoice i
WHERE c.id=i.customer AND b.id=i.business
This would output the following columns.
MySql - RESULT [name, phone, name, phone, description]
PHP - RESULT[name, phone, description]
Notice how it has name and phone twice on the MySql but when PHP looks at it as an array it has it only once. Well we need to be able to use the other data from this statement. To do this we will add a simple AS statement for the duplicate column names.
Example
SELECT c.name AS cname, c.phone AS cphone, b.name, b.phone, i.description
FROM Customer c, Business b, Invoice i
WHERE c.id=i.customer AND b.id=i.business
RESULT
MySql - RESULT [cname, cphone, name, phone, description]
PHP - RESULT[cname, cphone, name, phone, description]
Take a look at the first 2 columns now. Just adding the AS statement allowed the column to have different names that you can process.
