PHP & MYSQL: How to resolve ambiguous column names

Question

I have two tables in my database:

NEWS ('id' - the news id, 'user' - the user id of the author)

USERS ('id' - the user id)

I want to make a SELECT * FROM news JOIN users ON news.user = user.id, now when I get the results in PHP it's something like:

$row = mysql_fetch_array($result), and get column names by $row['column-name']... how do I get the news ID and the user ID, having the same column name?

UPDATE: Thanks everybody for the quick answers. Aliases seem the best solution.

Answer 1

There are two approaches:

1. Using aliases; in this method you give new unique names (ALIAS) to the various columns and then use them in the PHP retrieval.

eg.

SQL $sql = SELECT student_id FEES_LINK, student_class CLASS_LINK from students_fee_tbl LEFT JOIN student_class_tbl ON students_fee_tbl.student_id=student_class_tbl.student_id

PHP $query = mysql_fetch_assoc($sql);//if using the old approach

$query = PDO->fetchAll(); //not exact syntax but this is the current approach

foreach($query as $q){
echo $q['FEES_LINK'];
}

2.

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.

eg.

$sql = 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

PHP

$query = mysql_fetch_assoc($sql);//if using the old approach

$query = PDO->fetchAll();//not exact syntax but this is the current approach

foreach($query as $q){

    echo $q[0];

}

the two works.

Hope it helps. :)

Answer 2

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'

Answer 3

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 :)

Answer 4

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

Answer 5

@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...

Answer 6

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";