Extract multiple values from single field and join

[thomasrye]

I'm not extremely well versed in MySQL... this may end up being a fundamental or basic in database design and implementation. (by the way I'm using PHP)

I'll give the trimmed down example of what I'm working with:

Story Table
id     title     authors     datewritten
 

Authors Table
id     username     password     name
 

The problem is that each story has multiple authors and each author has written multiple stories.

So I don't know if I need to rethink my database structure or understand something different about how the tables can work together, but my IDEA of how I want to display it / make it work would be:

An HTML Table that displays the list of stories would look like:

Date            Title                      Authors
12 Dec 2007     Adventures of an Alien     Mike Smith, Tom Johnson
24 Jan 2008     Where Pride Went           Tom Johnson, Dan Walters
 

But in the database the AUTHORS field is "2,6" to reference authors of id "2" and "6" then select their name and display it in this case.

I've been reading about INNER JOIN and JOINS in general, but I guess I just can't get my head around it yet.

[debasisdas]

Since you are selecting all the data from a single table why use join at all.

If both the tables have relation try to use this.

select datewritten,title,authors from story,author where story.authors=author.id

[mwasif]

Hi thomasrye,

Welcome to TSDN!!!

You can manage your database at least in 2 ways

1. The way you are doing right now :-). And display the results in a loop and execute another query to fetch author names within that loop.
2. Create a third table (intermediate table) that will link authors and story table. The fields will be
   authorid, storyid
   And you don't need to keep authors field in story table. In this approach you can easily and effectively search which author has written how many books.

Let use know if you need more help.