Merging result from 2 columns with same name and not over-writing one

Question

I have a simple MySQL query like:

SELECT * 
  FROM `content_category` CC , `content_item` CI 
 WHERE CI.content_id = '" . (int)$contentId . "'
   AND CI.category_id = CC.category_id
   AND CI.active  = 1

Both tables have a column called configuration one of which gets overwritten in the query i.e only content_item.configuration is returned in the result.

Short of implicitly naming and aliasing the columns like

SELECT CC.configuration as `category_configuration`,
       CC.category_id as `.....

is there a way of selecting ALL data i.e * from both and resolve those duplicate column names in a non-destructive way.

Answer 1

You don't need to alias ALL the columns, just the one conflicting one:

SELECT *,CC.configuration as cc_conf, CI.configuration as ci_conf FROM `content_category` CC , `content_item` CI WHERE
CI.content_id = '" . (int)$contentId . "'
AND CI.category_id = CC.category_id
AND CI.active  = 1

Answer 2

This demonstrates one of the many reasons why using the * wildcard is not a good practice all the time. All the columns are returned in the result set, but if you access them via an associative array or via object properites in your host language (e.g. PHP or Ruby) you can naturally only have one of the columns associated with each key or object property.

Solutions:

• Fetch them all and reference the columns by ordinal position.

• Stop using the wildcard for one table or the other, and give column aliases.

• Rename your columns to be distinct.

• Define a VIEW with the column aliasing spelled out, and query from the view.