Logic/reality check before writing query

[learningcurve]

I am fairly new to PHP and want to someone to check my reasoning and logic for a php query.  I have a mySQL table that includes a field called authors.  This field has 1+ authors names in it.  If there is more than one author in the field, it has been concatenated in the following format: lastname1, firstinitial1., lastname2, first initial2., & lastname3, firstinitial3

 

Let's say I have three fields:

lastname1, first initial1.

lastname1, firstinitial1., lastname2, first initial2., & lastname3, firstinitial3.

lastname3, firstinitial3.

 

 

If I do a php count query on the author field I will get 1 count for the multiple field, 1 count for the lastname1 author, 1 count for the lastname3 author and 0 for the lastname2 author even though lastname1 author should be counted twice, correct? Is there anyway to write a query that will count each specific instance in the field as a hit?  Am I logically tracking or off the bubble?  Thanks!

[litebearer]

NOT a good idea to structure your table(s) that way.

Create a table named authors; id, fname, mname, lname

 

Aside...

what happens if:

brown, j (for jason)

brown, j (for jon)

brown, j (for john)

brown, j (for jim)

How will you distinguish (count them)?

 

[Maq]

If there is more than one author in the field, it has been concatenated in the following format: lastname1, firstinitial1., lastname2, first initial2., & lastname3, firstinitial3

There shouldn't be more than 1 author in a field.  Each "author entry" should be in a separate record.  I would set up your table something like this:

 

Authors table:

id  lastname  firstinitial  bookid

[litebearer]

@Maq wouldn't be better to have a separate book table also where the author id  was in the book table rather than the book id in the author table? (perhaps include ISBN in the book table also)

[Jessica]

@Maq wouldn't be better to have a separate book table also where the author id  was in the book table rather than the book id in the author table?

Not if one book can have two authors.

 

There should be 3 tables, since an author can have 1+ books and a book can have 1+ authors.

authors

author_books

books

[learningcurve]

I agree with you all about the formatting of the table.  Unfortunately, it was created like this many years ago by the person before me.  Since it is also used in several other places and queries, I cannot change it right now.  Maybe in the future.

[batwimp]

Do a query and post a sample output.