How to resolve ambiguous column names when retrieving results

Question

I have two tables in my database   NEWS table with columns    id - the news id  user - the user id of the author    USERS table with columns    id - the user id   I want to execute this SQL    SELECT   FROM news JOIN users ON news user   user id    When I get the results in PHP I would like to get associative array and get column names by  row  column-name    How do I get the news ID and the user ID  having the same column name

User · Answer

I had this same issue with dynamic tables. (Tables that are assumed to have an id to be able to join but without any assumption for the rest of the fields.) In this case you don't know the aliases before hand.

In such cases you can first get the table column names for all dynamic tables:

$tblFields = array_keys($zendDbInstance->describeTable($tableName));

Where $zendDbInstance is an instance of Zend_Db or you can use one of the functions here to not rely on Zend php pdo: get the columns name of a table

Then for all dynamic tables you can get the aliases and use $tableName.* for the ones you don't need aliases:

$aliases = "";
foreach($tblKeys as $field)
    $aliases .= $tableName . '.' . $field . ' AS ' . $tableName . '_' . $field . ',' ;
$aliases = trim($aliases, ',');

You can wrap this whole process up into one generic function and just have cleaner code or get more lazy if you wish :)

User · Answer

You can either use the numerical indices   row 0   or better  use AS in the MySQL   SELECT    user id AS user id FROM

User · Answer

I just figured this out  It s probably a bad practice but it worked for me in this case   I am one of the lazy people who doesn t want to alias or write out every column name with a table prefix   You can select all of the columns from a specific table by using table name   in your select statement   When you have duplicated column names  mysql will overwrite from first to last  The data from the first duplicated column name will be overwritten when it encounters that column name again  So the duplicate column name that comes in last wins   If I am joining 3 tables  each containing a duplicated column name  the order of the tables in the select statement will determine what data I am getting for the duplicate column   Example   SELECT table1     table2     table3   FROM table1 LEFT JOIN table2 ON table1 dup   table2 dup LEFT JOIN table3 ON table2 dup   table3 dup    In the example above  the value of dup I get will be from table3   What if I want dup to be the value from table1   Then I need to do this   SELECT table3     table2     table1   FROM table1 LEFT JOIN table2 ON table1 dup   table2 dup LEFT JOIN table3 ON table2 dup   table3 dup    Now  table1 comes last  so the value of dup will be the value from table1   I got the value I wanted for dup without having to write out every single freaking column and I still get all of the columns to work with  Yay   I know the value of dup should be the same in all 3 tables  but what if table3 doesn t have a matching value for dup  Then dup would be blank in the first example  and that would be a bummer

User · Answer

When using PDO for a database interacttion  one can use the PDO  FETCH NAMED fetch mode that could help to resolve the issue    sql    SELECT   FROM news JOIN users ON news user   user id    data    pdo- gt query  sql - gt fetchAll PDO  FETCH NAMED   foreach   data as  row        echo  row  column-name   0      from the news table     echo  row  column-name   1      from the users table     echo  row  other-column       any unique column name

User · Answer

Here s an answer to the above  that s both simple and also works with JSON results being returned  While the SQL query will automatically prefix table names to each instance of identical field names when you use SELECT    JSON encoding of the result to send back to the webpage  ignores the values of those fields with a duplicate name and instead returns a NULL value   Precisely what it does is include the first instance of the duplicated field name  but makes its value NULL  And the second instance of the field name  in the other table  is omitted entirely  both field name and value  But  when you test the query directly on the database  such as using Navicat   all fields are returned in the result set  It s only when you next do JSON encoding of that result  do they have NULL values and subsequent duplicate names are omitted entirely   So  an easy way to fix that problem is to first do a SELECT    then follow with aliased fields for the duplicates  Here s an example  where both tables have identically named site name fields   SELECT    w site name AS wo site name FROM ws work orders w JOIN ws inspections i WHERE w hma num NOT IN SELECT hma number FROM ws inspections  ORDER BY CAST w hma num AS UNSIGNED     Now in the decoded JSON  you can use the field wo site name and it has a value  In this case  site names have special characters such as apostrophes and single quotes  hence the encoding when originally saving  and the decoding when using the result from the database      decHTMLifEnc decodeURIComponent  jsArrInspections x   wo site name       You must always put the   first in the SELECT statement  but after it you can include as many named and aliased columns as you want  as repeatedly selecting a column causes no problem

User · Answer

You can do something like  SELECT news id as news id  user id as user id        And then  row  news id   will be the news id and  row  user id   will be the user id

User · Answer

You can do something like  SELECT news id as news id  user id as user id        And then  row  news id   will be the news id and  row  user id   will be the user id

User · Answer

You can do something like  SELECT news id as news id  user id as user id        And then  row  news id   will be the news id and  row  user id   will be the user id

User · Answer

Another tip  if you want to have cleaner PHP code  you can create a VIEW in the database  e g   For example   CREATE VIEW view news AS SELECT   news id news id    user id user id    user name user name      OTHER FIELDS   FROM news  users WHERE news user id   user id    In PHP    sql    SELECT   FROM view news

User · Answer

If you don t feel like aliassing you can also just prefix the tablenames   This way you can better automate generation of your queries  Also  it s a best-practice to not use select    it is obviously slower than just selecting the fields you need Furthermore  only explicitly name the fields you want to have   SELECT     news id  news title  news author  news posted       users id  users name  users registered  FROM      news  LEFT JOIN      users  ON      news user   user id

User · Answer

When using PDO for a database interacttion  one can use the PDO  FETCH NAMED fetch mode that could help to resolve the issue    sql    SELECT   FROM news JOIN users ON news user   user id    data    pdo- gt query  sql - gt fetchAll PDO  FETCH NAMED   foreach   data as  row        echo  row  column-name   0      from the news table     echo  row  column-name   1      from the users table     echo  row  other-column       any unique column name

User · Answer

You can do something like  SELECT news id as news id  user id as user id        And then  row  news id   will be the news id and  row  user id   will be the user id

User · Answer

You can either use the numerical indices   row 0   or better  use AS in the MySQL   SELECT    user id AS user id FROM

User · Answer

I just figured this out  It s probably a bad practice but it worked for me in this case   I am one of the lazy people who doesn t want to alias or write out every column name with a table prefix   You can select all of the columns from a specific table by using table name   in your select statement   When you have duplicated column names  mysql will overwrite from first to last  The data from the first duplicated column name will be overwritten when it encounters that column name again  So the duplicate column name that comes in last wins   If I am joining 3 tables  each containing a duplicated column name  the order of the tables in the select statement will determine what data I am getting for the duplicate column   Example   SELECT table1     table2     table3   FROM table1 LEFT JOIN table2 ON table1 dup   table2 dup LEFT JOIN table3 ON table2 dup   table3 dup    In the example above  the value of dup I get will be from table3   What if I want dup to be the value from table1   Then I need to do this   SELECT table3     table2     table1   FROM table1 LEFT JOIN table2 ON table1 dup   table2 dup LEFT JOIN table3 ON table2 dup   table3 dup    Now  table1 comes last  so the value of dup will be the value from table1   I got the value I wanted for dup without having to write out every single freaking column and I still get all of the columns to work with  Yay   I know the value of dup should be the same in all 3 tables  but what if table3 doesn t have a matching value for dup  Then dup would be blank in the first example  and that would be a bummer

User · Answer

You can either use the numerical indices   row 0   or better  use AS in the MySQL   SELECT    user id AS user id FROM

User · Answer

You can either use the numerical indices   row 0   or better  use AS in the MySQL   SELECT    user id AS user id FROM

User · Answer

You can set aliases for the columns that you are selecting    query    SELECT news id AS newsId  user id AS userId   OTHER FIELDS HERE  FROM news JOIN users ON news user   user id

User · Answer

If you don t feel like aliassing you can also just prefix the tablenames   This way you can better automate generation of your queries  Also  it s a best-practice to not use select    it is obviously slower than just selecting the fields you need Furthermore  only explicitly name the fields you want to have   SELECT     news id  news title  news author  news posted       users id  users name  users registered  FROM      news  LEFT JOIN      users  ON      news user   user id

User · Answer

Here s an answer to the above  that s both simple and also works with JSON results being returned  While the SQL query will automatically prefix table names to each instance of identical field names when you use SELECT    JSON encoding of the result to send back to the webpage  ignores the values of those fields with a duplicate name and instead returns a NULL value   Precisely what it does is include the first instance of the duplicated field name  but makes its value NULL  And the second instance of the field name  in the other table  is omitted entirely  both field name and value  But  when you test the query directly on the database  such as using Navicat   all fields are returned in the result set  It s only when you next do JSON encoding of that result  do they have NULL values and subsequent duplicate names are omitted entirely   So  an easy way to fix that problem is to first do a SELECT    then follow with aliased fields for the duplicates  Here s an example  where both tables have identically named site name fields   SELECT    w site name AS wo site name FROM ws work orders w JOIN ws inspections i WHERE w hma num NOT IN SELECT hma number FROM ws inspections  ORDER BY CAST w hma num AS UNSIGNED     Now in the decoded JSON  you can use the field wo site name and it has a value  In this case  site names have special characters such as apostrophes and single quotes  hence the encoding when originally saving  and the decoding when using the result from the database      decHTMLifEnc decodeURIComponent  jsArrInspections x   wo site name       You must always put the   first in the SELECT statement  but after it you can include as many named and aliased columns as you want  as repeatedly selecting a column causes no problem

User · Answer

You can set aliases for the columns that you are selecting    query    SELECT news id AS newsId  user id AS userId   OTHER FIELDS HERE  FROM news JOIN users ON news user   user id

User · Answer

If you don t feel like aliassing you can also just prefix the tablenames   This way you can better automate generation of your queries  Also  it s a best-practice to not use select    it is obviously slower than just selecting the fields you need Furthermore  only explicitly name the fields you want to have   SELECT     news id  news title  news author  news posted       users id  users name  users registered  FROM      news  LEFT JOIN      users  ON      news user   user id

User · Answer

Jason  You are correct except that php is the culprit and not mysql  If you put your JOIN in Mysql Workbench you will get three columns with the exact same name  one for each table  but not with the same data  some will be null if that table has no match for the JOIN     In php if you use MYSQL NUM in mysql fetch array   then you will get all columns  The problem is when you use mysql fetch array   with MYSQL ASSOC  Then  inside that function  php is building the return value like so    row  dup      value from table1    and later on      row  dup      value from table2          row  dup      value from table3    So you will get only the value from table3  The problem is that a result set from mysql can contain columns with the same name but associative arrays in php don t allow duplicate keys in arrays  When the data is saved in associative arrays  in php  some information is silently lost

User · Answer

Jason  You are correct except that php is the culprit and not mysql  If you put your JOIN in Mysql Workbench you will get three columns with the exact same name  one for each table  but not with the same data  some will be null if that table has no match for the JOIN     In php if you use MYSQL NUM in mysql fetch array   then you will get all columns  The problem is when you use mysql fetch array   with MYSQL ASSOC  Then  inside that function  php is building the return value like so    row  dup      value from table1    and later on      row  dup      value from table2          row  dup      value from table3    So you will get only the value from table3  The problem is that a result set from mysql can contain columns with the same name but associative arrays in php don t allow duplicate keys in arrays  When the data is saved in associative arrays  in php  some information is silently lost

User · Answer

If you don t feel like aliassing you can also just prefix the tablenames   This way you can better automate generation of your queries  Also  it s a best-practice to not use select    it is obviously slower than just selecting the fields you need Furthermore  only explicitly name the fields you want to have   SELECT     news id  news title  news author  news posted       users id  users name  users registered  FROM      news  LEFT JOIN      users  ON      news user   user id

User · Answer

Another tip  if you want to have cleaner PHP code  you can create a VIEW in the database  e g   For example   CREATE VIEW view news AS SELECT   news id news id    user id user id    user name user name      OTHER FIELDS   FROM news  users WHERE news user id   user id    In PHP    sql    SELECT   FROM view news

User · Answer

There are two approaches    Using aliases  in this method you give new unique names  ALIAS  to the various columns and then use them in the PHP retrieval  e g   SELECT student id AS FEES LINK  student class AS CLASS LINK  FROM students fee tbl  LEFT JOIN student class tbl ON students fee tbl student id   student class tbl student id   and then fetch the results in PHP    query    PDO stmt- gt fetchAll     foreach  query as  q        echo  q  FEES LINK       Using place position or resultset column index  in this  the array positions are used to reference the duplicated column names  Since they appear at different positions  the index numbers that will be used is always unique   However  the index positioning numbers begins at 0  e g   SELECT student id  student class  FROM students fee tbl  LEFT JOIN student class tbl ON students fee tbl student id   student class tbl student id   and then fetch the results in PHP    query    PDO stmt- gt fetchAll     foreach  query as  q        echo  q 0

User · Answer

There are two approaches    Using aliases  in this method you give new unique names  ALIAS  to the various columns and then use them in the PHP retrieval  e g   SELECT student id AS FEES LINK  student class AS CLASS LINK  FROM students fee tbl  LEFT JOIN student class tbl ON students fee tbl student id   student class tbl student id   and then fetch the results in PHP    query    PDO stmt- gt fetchAll     foreach  query as  q        echo  q  FEES LINK       Using place position or resultset column index  in this  the array positions are used to reference the duplicated column names  Since they appear at different positions  the index numbers that will be used is always unique   However  the index positioning numbers begins at 0  e g   SELECT student id  student class  FROM students fee tbl  LEFT JOIN student class tbl ON students fee tbl student id   student class tbl student id   and then fetch the results in PHP    query    PDO stmt- gt fetchAll     foreach  query as  q        echo  q 0

User · Answer

I had this same issue with dynamic tables. (Tables that are assumed to have an id to be able to join but without any assumption for the rest of the fields.) In this case you don't know the aliases before hand.

In such cases you can first get the table column names for all dynamic tables:

$tblFields = array_keys($zendDbInstance->describeTable($tableName));

Where $zendDbInstance is an instance of Zend_Db or you can use one of the functions here to not rely on Zend php pdo: get the columns name of a table

Then for all dynamic tables you can get the aliases and use $tableName.* for the ones you don't need aliases:

$aliases = "";
foreach($tblKeys as $field)
    $aliases .= $tableName . '.' . $field . ' AS ' . $tableName . '_' . $field . ',' ;
$aliases = trim($aliases, ',');

You can wrap this whole process up into one generic function and just have cleaner code or get more lazy if you wish :)

User · Answer

You can set aliases for the columns that you are selecting    query    SELECT news id AS newsId  user id AS userId   OTHER FIELDS HERE  FROM news JOIN users ON news user   user id

User · Answer

You can set aliases for the columns that you are selecting    query    SELECT news id AS newsId  user id AS userId   OTHER FIELDS HERE  FROM news JOIN users ON news user   user id

[php] How to resolve ambiguous column names when retrieving results?

Examples related to php

Examples related to sql

Examples related to mysql

Examples related to mysql-error-1052