Retrieving MYSQL Join values from PHP

Question

I have two or more tables which hold values under the same column name. Now when I join these two tables the column names stay the same. When retrieving these values in PHP ($row['name']) I encounter problems since the column 'name' is used twice.

Are there any possible means of separating these two columns inside the query?

  SELECT *
    FROM stories s
    JOIN authors a ON a.id = s.authorid

Table stories id, name, content, date

Table authors id, name, date

When i join these two i get one table with similar 'name' columns.

Is there anyway to separate the two tables so the author table has a prefix in front of it? E.g. authors_name /authors_*

Answer 1

Yes, change your SQL this way :

SELECT 
  s.Id as StoryId, 
  s.Name as StoryName,
  a.Id as AuthorId, 
  a.Name as AthorName,
FROM stories s
JOIN authors a ON a.id = s.authorid

Then in php, use StoryId, StoryName, AuthorId and AthorName instead of Id or Name.

Hope it helps you.

Answer 2

Yes there is!

SELECT *, stories.name AS s_name, authors.name AS a_name
FROM stories s
JOIN authors a ON a.id = s.authorid

And there you have it. All fields plus two extra! ;)

Hope it helps.

Answer 3

Sorry you can't really do prefixing on a field level, but if you can call 2 queries, just use

SELECT s.*
FROM stories s
JOIN authors a ON a.id = s.authorid

The result set will only contain stories fields and you can use the fields as you normally would with php. Then just do the same again for authors.