MySQL joins, how to output relation "the proper way"

Question

Hello,

First of all, excuse my poor topic title. I simply have no idea how to formulate this question or what to Google after, so don't shoot me if this is something easy answerable.

Assume I have the following tables:

[AUTHORS] id, name

[NEWS] id, item, author_id

If I wanted to display a news item and output the corresponding author (stored in author_id), I'd do the following query:

SELECT * FROM `news` JOIN `authors` ON news.author_id = authors.id

And then outputting it by doing something like this

$Q = "SELECT * FROM news JOIN authors ON news.author_id=news.id";

$query = $this->lite->query($Q) or die($this->lite->error); 

$result=null; while($obj = $query->fetch_object()){
     $result.= '<li>'.$obj->item. 'by ' . $obj->name . '</li>';
}

Here, $obj->name would contain the name of the author and would successfully output.

So, what happens if the "name" field were called "item" instead? There would obviously be some conflicts. With my previous experience in Propel & symfony, I would do something like this: getAuthorId()->getName() - but for this project I'm required to write SQL from scratch.

Appreciate all answers! Thanks!

Answer 1

Never use * syntax in production code.

Rewrite your query as this:

SELECT  news.item AS news_item, author.name AS author_name
FROM    `news`
JOIN    `authors`
ON      news.author_id = authors.id

, aliasing your fields as necessary to avoid naming conflicts.

Answer 2

You would need to use a qualified name like news.item or authors.item.

Answer 3

You name them explicitly

SELECT  n.id, n.item, n.author_id,a.name,a.id 
FROM 
`news` as n JOIN `authors` as a 
ON 
n.author_id = n.id

;

Or perhaps more like

SELECT  n.id as newsid, n.item as newsitem, n.author_id as autohor_authorid,
a.name as authorname,a.id as authorid 
FROM 
`news` as n JOIN `authors` as a 
ON 
n.author_id = n.id;

You should only pull out the columns you actually need. It's considered bad style to do select * , always name the columns you're interested in, or it's easy to break code if e.g. another column is added to the table.