CODEWITHSUNDEEP
phpsqlmysqlleft-join
SyntaxError
SyntaxError

Reputation: 13

Retrieving MYSQL Join values from PHP

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_*

Upvotes: 1

Views: 953

Answers (3)

Shane
Shane

Reputation: 697

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.

Upvotes: 0

Frankie
Frankie

Reputation: 25165

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.

Upvotes: 1

Manitra Andriamitondra
Manitra Andriamitondra

Reputation: 1249

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.

Upvotes: 5

Related Questions