mysqlpdofield-names

Ambiguous field names / add table name with FETCH_OBJ

I've got a table T1 with 2 foreign keys targeting the same table T2.

So, my SQL statement is like that :

SELECT *
FROM T1
INNER JOIN T2 AAA ON AAA.ID = T1.ID1
INNER JOIN T2 BBB ON BBB.ID = T1.ID2

(this sql works)

Then, I use $info = $exec->fetch(PDO::FETCH_ASSOC);

I'd like, at end, use the $info->AAA.name and $info->BBB.name, but I cannot find how to set the table name into the reading. And I'd like to avoid to change my SQL by adding dozen of ... AS ...

Any idea?

Solution

  • UPDATE1
    I have searched through PDO documentation and found directive PDO::ATTR_FETCH_TABLE_NAMES. Docs say that it makes driver to return table names before column names. Table names will be separated from column names by '.'(dot).
    You could try to use this directive to solve your problem, but perhaps it will not work without aliases..
    Please try this on: $PDO->setAttribute(PDO::ATTR_FETCH_TABLE_NAMES, true);

    UPDATE2
    I have found following question and answer from the asking person, who have used this directive to solve his problem, similar to yourth.
    SELECT result with table names for columns

    This question dublicates several other questions asked earlier:
    PDO: fetchAll() with duplicate column name on JOIN
    PDO Auto-aliasing when fetching?

    So, the first answer was: "NO, you cannot do this without setting aliases for each column."

    Workaround is to use PDO::FETCH_NUM, which will return you a numbered array of fetched columns. But I would not recomend it, because if you will change your tables' structure, your PHP script may become broken.

    Anyway, it is still a good practise to select certain columns and set aliases instead of all *. Like this:

    SELECT T1.ID1 as ID1, T1.ID2 as ID2, T1.name as T1_name, AAA.name as AAA_name, BBB.ID as BBB_name
FROM 
 T1
 INNER JOIN
 T2 AAA
  ON AAA.ID = T1.ID1
 INNER JOIN
 T2 BBB
  ON BBB.ID = T1.ID2