[SOLVED] MySQL Multiple results from a single field to multiple field problem

[jedispyder]

Hello all, I'm very new to PHP and MySQL.  I'm working on a comic book database and for my question I'll describe 2 of the tables:

 

Table: Creators

id [int, primary key] (just the typical id)

name [varchar, foreign key] (this contains the names of comic book creators)

 

Table: Comics

id [int, primary key] (just the typical id)

num (contains issue number for the comic)

writer01 [int, links to Creator.id]

writer02 [int, links to Creator.id, null]

writer03 [int, links to Creator.id, null]

illustrator01 [int, links to Creator.id]

illustrator02 [int, links to Creator.id, null]

illustrator03 [int, links to Creator.id, null]

 

Occasionally there are multiple writers/illustrator on a comic book, so I'd like to be able to display all of that information if available (some issues may have just a 1 writer/1 illustrator).

 

When I tried a WHERE to display just Comics.writer01, it worked fine.  When I tried to create a WHERE to display Comics.writer01 and Comics.writer02 I got a "0 results returned".

 

MySQL Query for only Comics.writer01

SELECT Comics.num, Creators.name

FROM Comics, Creators

WHERE Comics.writer01 = Creators.id

 

MySQL Query for both Comics.writer01 and Comics.writer02

SELECT Comics.num, Creators.name

FROM Comics, Creators

WHERE Comics.writer01 = Creators.id

AND Comics.writer02 = Creators.id

 

As I stated earlier, I'm very new to MySQL (and really databases in general).  I have looked through a couple books trying to fully understand JOINS but just don't fully get it yet.  Sorry if its something simple that most people understand, its just evidently taking me longer than I thought...

[Eiolon]

I'd read into database normalization.

 

http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html

 

Looks like you have a many-to-many relationship going on here.  A comic can have multiple writers/illustrators and the writers/illustrators could have worked on multiple comics.

 

Also, is there ever going to be a chance when the comics have more than 3 writers or illustrators?

[jedispyder]

Thanks, I forgot about that possibility.  So when I looked up many-to-many, from what I understood I need a 3rd table to reference the two.

 

So I created:

Creator_Books [i relabeled "Comics" to "Books" for short naming purposes]

bid (int) which links to the Books.id

cid (int) which links to the Creators.id

 

And I'm guessing I now need to remove the Writer01,...,Illustrator01,..., from the Books database, since that info is being fed from the intermediary Creator_Books table, correct? 

 

I'm still having trouble with it, so I think I may have the wrong relationship set up.  In phpMyAdmin, I set up the relationship while in the Creator_Books table, linking them to the correct value.  Should it be the other way around, linking from the Books and Creators table into the Creator_Books table?

 

The Query I tried:

SELECT Books.name, Books.num, Creators.name

FROM Books, Creators

LEFT JOIN Creator_Books ON Creator_Books.cid = Creators.id

 

And while it did return results, it didn't return the right results.  For example, I was trying for it to say:

Creator X worked on Book X and Book Y

Creator Y worked on Book X, and Book Z

It returned that Creator X worked on Book X, Y, Z and Creator Y worked on Book X,Y,Z as well as listing them multiple times for each Book.

 

I'm guessing I have the Join wrong, but I tried it several ways and every time I tried doing "Creator_Books.bid = Books.id" it gave me an error...

[Eiolon]

Run this query, it should return a list of books, it's num, and respective creators.

 

SELECT b.name, b.num, c.name FROM books b, creators c, creators_books cb WHERE b.id = cb.bid AND c.id = cb.cid

[jedispyder]

Awesome!  Thank you very much, works perfectly.  I don't know I didn't try out "Where", guess it was because I kept seeing the "Join" and thought it would be that.