PHP/MySQL: Realizing a many-to-many relationship, this one's a doozy 1

[king1ear]

Here's the problem: I have many authors who have done many publications, though it is not always one author each publication, it is usually about three authors to one. Of course these three seperate authors work on other papers also. Now this looks like a many-to-many relationship to me, and that is fine and good, but I have no idea how to implement the whole process. I know a couple of things, like that I need a lookup table between authors and publications, but I'm very inexperianced with such a system. This is what I need help with:

When I insert the authors and publications how do I pass the primary keys from both to the lookup table, since MySQL needs to assign them both the keys first? What is a good design for this kind framework? Should I first make a page that shows all the authors and then a link next to the author that says "add publication" which takes me to a page where I can pick from the available publications? Or vice versa? Would that even work?

I know, that all sounds very confusing, but I guess what I'm looking for is a plan to go off of, or an example, even theoretical, that someone has done before.

Hope someone can help. I can provide more details if need be.

 

[sleipnir214]

You will probably make extensive use of mysql_insert_id().

The process isn't that much different from recording a one-to-many relationship.

Get the key for the publication. Either insert a new publication and retrieve its record's key using mysql_insert_id(), or search for the key of an existing publication record.

Get the key for an author. Either insert a new author and retrieve her key record's key using mysql_insert_id(), or search for the key of an existing author record.

Insert the publication-key/author-key keypair into the lookup table to record the relationship. Want the best answers? Ask the best questions:

http://www.tuxedo.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[king1ear]

That does the trick! Thank you, D