Multiple authors for one article

[saeed_violinist]

Hi!

 

I really got confused about this, I am trying to make an article system. if it was 1 author fore each article it was not a problem. These articles may have only one or multiple authors each.

 

here is my example tables. I really appriciate if you show any approach tho handle this. I seted up tables like this:

 

table articles :

----------------------------------------

article_id |  title  |  content  | authors

----------------------------------------

    0      |    test  |  blahblah  |  1,2 

    1      |  test2  | blahblah  |  1,2,3

----------------------------------------

 

table authors:

-------------------------

author_id |  name | email

-------------------------

      1      | Sean  |  blah

      2      |  Sara  |  blah

      3      | David  | blah

 

 

I want to pass everything in an array of 1 dimension (if possible)

 

please assist!

[trq]

In this case you want a third table 'article_authors' used to join the articles and authors together. eg;

 

[pre]

table articleauthors :

----------------------------

article_id |  author_id

----------------------------

    0      |    1

    0      |    2

    1      |    1

    1      |    2

    1      |    3

----------------

[/pre]

[saeed_violinist]

Thanks a lot! it seems to be more practical.

 

can you provide a sample mysql select for this condition?

[trq]

To get the author names for the article 'test'.

 

SELECT DISTINCT authors.name
FROM article_authors, articles, authors
WHERE article_authors.author_id = articles.id
AND articles.title = 'test'

 

Would return Sean and Sara.

[saeed_violinist]

To get the author names for the article 'test'.

 

SELECT DISTINCT authors.name
FROM article_authors, articles, authors
WHERE article_authors.author_id = articles.id
AND articles.title = 'test'

 

Would return Sean and Sara.

 

Thanks, but I tried this and it will return only 1 aouthor . or it is my bad, can you check it again please?

[kickstart]

Hi

 

Think he has forgotten to link in the authors table

 

SELECT DISTINCT c.name
FROM articles a
INNER JOIN article_authors b ON a.id = b.author_id
INNER JOIN authors c ON b.author_id = c.id
WHERE a.title = 'test'

 

All the best

 

Keith

[saeed_violinist]

Thanks!