learningcurve Posted May 3, 2012 Share Posted May 3, 2012 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! Quote Link to comment https://forums.phpfreaks.com/topic/262017-logicreality-check-before-writing-query/ Share on other sites More sharing options...
litebearer Posted May 3, 2012 Share Posted May 3, 2012 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)? Quote Link to comment https://forums.phpfreaks.com/topic/262017-logicreality-check-before-writing-query/#findComment-1342689 Share on other sites More sharing options...
Maq Posted May 3, 2012 Share Posted May 3, 2012 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 Quote Link to comment https://forums.phpfreaks.com/topic/262017-logicreality-check-before-writing-query/#findComment-1342690 Share on other sites More sharing options...
litebearer Posted May 3, 2012 Share Posted May 3, 2012 @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) Quote Link to comment https://forums.phpfreaks.com/topic/262017-logicreality-check-before-writing-query/#findComment-1342693 Share on other sites More sharing options...
Jessica Posted May 3, 2012 Share Posted May 3, 2012 @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 Quote Link to comment https://forums.phpfreaks.com/topic/262017-logicreality-check-before-writing-query/#findComment-1342695 Share on other sites More sharing options...
learningcurve Posted May 3, 2012 Author Share Posted May 3, 2012 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. Quote Link to comment https://forums.phpfreaks.com/topic/262017-logicreality-check-before-writing-query/#findComment-1342725 Share on other sites More sharing options...
batwimp Posted May 3, 2012 Share Posted May 3, 2012 Do a query and post a sample output. Quote Link to comment https://forums.phpfreaks.com/topic/262017-logicreality-check-before-writing-query/#findComment-1342730 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.