splitting of semicolon delimited list within single field

mysql list delimited field column split

[jackalandwolf]

Hi all, I can't seem to find a solution anywhere. Would appreciate any help.

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.

[Demaestro]

is it possible for you to change the import and the DB a bit?

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

[ZydoSEO]

I would suggest you never store a list of authors in a single database field. It's not normalized and it leads to issues like what you are encountering now. You are trying to represent a many-to-many relationship using a single table.

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]