I have a database with two tables:
books, the fields are title_id, title, pages, publisher
author the fields are author_id, title_id, author

I can INSERT ok into either table as a speparate entry
but what I would like to do is enter the data into both fields from a form
instead of using phpmyadmin. It is the INSERT part I am stuck with.
Just to complicate it some of the books have more than one author :icon_rolleyes:

Any help or suggestions appreciated. Thanks

7 Years
Discussion Span
Last Post by drjohn

Just to complicate it some of the books have more than one author

No offence meant here, but problem seems like an outcome of bad table design. There are are currently two entities which I can see in your data model: Author and Title.

Now one Author can have multiple Titles, while a single Title can have multiple Authors. This is a many-many relationship and to model it correctly you need a third table (example AuthorTitleMap), which maps the title_id to the author_id and they together will form the primary key of the third table. (Also you would have to remove the title_id in the author table)

Also while inserting the data insert first into the Title Table, them the Author table and finally into the AuthorTitleMap.


What he says ^

And you can do this from a single form by using a series of queries, one after the other, each with it's own connection. (MySQL can't chain queries, each has to be a separate event.)

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.