record selection in joined table

Hi there all,

I was wondering how to do the following:-

I have a books database in mySql which has tables of books and authors (amongst many others) and I want to create a book entry page. I am trying to create a single page for insertion into books table but I have a problem in that if I make a select box for authors and build options from the authors table, I am left with 500 choices of author!! Also I have the problem that I may need to create an author if it does not exist in the authors table. Can anyone suggest a neat way of doing this? The books table has fields for authorids (i have several author fields as sometimes more than one author per book!)

TIA

Nick
URL

>I want to create a book entry page.


>a select box for authors ... 500 choices of author!!

Make an author select page... should probably be a javascript popup page, where you can select the letter of the authers last name from the alphabet (26 letters), then from there you can narrow down that list, and you can do that in drill down style or create a small list box.


>Also I have the problem that I may need to create an author if it does not exist in the authors table.

Create a small button next to the author id field in your main form that opens a small window to enter a new author in, and passes the new author id back to the main form window (hope you know javascript)


>The books table has fields for authorids (i have several author fields as sometimes more than one author per book!)

You should have a table like this:

authors_books
==============
| bookid | authorid |
==============

authors
==============
| authorid | name |
==============

books
==============
| bookid | name |
==============


Your authors_books table can have more than one authorid for each bookid... now you can just query this table for the authors of the books and if there is more than one, you have a list of authors.

Thanks for the post laidbak!

Only trouble is ..... I don't know javascript! Can u suggest any good sources? Also would it be easy for the javascript to auto-enter the selected author, in the original semi-completed book entry from?

I have created authors1-5 in the books table rather than using a lookup table. Is this OK or will it affect performance?

Thanks once again


NIck

>Thanks for the post laidbak!

No Problem

>Only trouble is ..... I don't know javascript!

Learn it...
Try www.google.com (learn how to use it)
type in javascript tutorial

>javascript to auto-enter the selected author, in the original semi->completed book entry from?

Use CSS

>I have created authors1-5 in the books table rather than using >a lookup table. Is this OK or will it affect performance?

Trust me... you don't want to do this...
It may seem like it makes sense now to add 5 author fields because you obviously figure 5 would be enought right?
Suppose it is not... But then again, look at it this way.. many times it won't even be filled. Why hastle with wasted space when it is not filled, and run the risk of running out of space when a book has 12 authors.

http://www.amazon.com/exec/obidos/t...767064?v=glance


See what I mean...