mysql php selecting fields from 2 tables, with the same field names [duplicate]

Question

So I have a bespoke CMS that allows dynamic creation of forms and lists etc. I have noticed an issue where it grabs the data for the list, which is conflicting with an approval table in the database.

The problem is, if the table with the data has fields names the same as field names in the approvals table, then when i mysql_fetch_array and it returns the values in an array, it will only return one field name

So an example of what is being returned

Array
(
    [id] => 1
)

And ideally I would want it returned as

Array
(
    [approvals.id] => 1
    [affiliates.id] => 2
)

So how can I make it prefix the table name to the results array to counteract field names called the same thing? I dont want to go through changing the field names as its pretty embedded.

Ewall

is it possible for you to modify the sql? – Cattle

Answer 1

Normally you use an alias in the SQL sentence:

SELECT table1.id as t1_id, table2.id as t2_id FROM .....

Then when you have the fetching, you will access it this way:

echo $row['t1_id'];

Benzoic

Answer 2

Use mysql alias

approvals table id alias as [id AS approvals.id]

affiliates table id alias as [id AS affiliates.id]

Xanthippe

Answer 3

Use an alias in the query:

SELECT approvals.id AS approvals_id, affiliates.id AS affiliates_id ...

The associative array will then contain:

echo $row['approvals_id'];

Auspicious

Answer 4

The problem is, if the table with the data has fields names the same as field names in the approvals table

You have to alias these fields' names with different aliases. Something like:

SELECT 
   approvals.id approvalsid,
   affiliates.id affiliatesid
    ...
FROM
...

Ceasefire