Forum Moderators: open
I have imported a large csv database of Scientific Journals into MySQL.
For the sake of simplicity, let's say that the field names are 'title' & 'author'.
Some of the 'titles' have multiple, semicolon delimited 'authors'.
For example 'Boudra DB; Maillet KA; Chassignet EP;'
This database is to be updated online via 2 text fields 'title' and 'author'
I'm making use of an AJAX based, suggest feature on the 'author' field. Thus when the user types into the 'author' text field, any similar entries currently in the database are 'suggested'.
SELECT DISTINCT $field FROM $database WHERE $field LIKE '%".$text."%' LIMIT 5;
I would like the suggest feature to suggest individual authors, but not groups. The problem is that the suggest displays the entire semicolon delimited author field.
So, if I type 'B', instead of just getting 'Brown' & 'Bang', I also get
'Boudra DB;Maillet KA;Chassignet EP;'
Is there a way for me to tell MySQL to treat these semicolons as delimiters inside fields? If not, what would you suggest?
Looking forward to your thoughts and ideas.
It would be better if you had a two new tables called author and book_has_author
Author table:
author_id, f_name, l_name
book_has_author table:
author_id, book_id
If it is not too late I would suggest 3 tables to store this type of information similar to the following:
Table name tBooks:
------------------------
BookID(auto increment / auto generated primary key)
Title
Description
etc... (all the attributes of a book)
Table named tAuthor:
--------------------
AuthorID (auto increment / auto generated primary key)
LastName
MiddleName
FirstName
DOB
Nationality
etc... (all attributes of an author that you might want to track)
And most importantly... a relationship table to relate one or more authors to a book or to relate one or more books to an author.
Table named tBookAuthor:
------------------------
BookID (foreign key reference back to tBook.BookID)
AuthorID (foreign key reference back to tAuthor.AuthorID)
With this setup, if you need a list of books then select from tBook. If you need a list of authors then select from tAuthor. If you need the author(s) for a book join tBook to tBookAuthor to tAuthor where BookID = ?. If you need a list of books by an author then join tAuthor to tBookAuthor to tBook where AuthorID = ?. etc...
I would suggest breaking the author name into pieces because it is likely that sometimes you will want to sort by last names in the form "Last, First Middle" and other times you will want to list or display the author in form "First Middle Last".
Hope this helps...
[edited by: ZydoSEO at 6:02 pm (utc) on Oct. 1, 2008]
