Re: multiple categories

Paul M Foster wrote: > On Mon, Mar 02, 2009 at 11:50:15AM -0500, PJ wrote: > > >> I have a list of some 60 categories for a book database and am wondering >> what would be the best approach for inserting and selecting the data. >> My choices are to >> (1.)simply use one field in a books table and enter the categories for >> each book separated by a comma (or whatever) as a text field and then >> use full text search to select the category(ies) for each book (out of >> the 60+ categories any one book might have maybe up to 3 or 4 categories. >> This seems like it might be cumbersome for selecting as there could be a >> rather lengthy search (if there would be 10,000 books or more). >> Or >> (2.) use an intersecting table with foreign keys linking the categories >> to the books. >> >> Now, inserting the categories for method 1 is simply a matter of >> entering the categories in the field as text. But entering the >> categories for method 2 would be a rather lengthy and complicated >> conditional script using a dropdown select table to link the categories >> and the book through the 3rd table. >> >> Or is there some other magical solution? I hope my explanation is not >> too confusing... :-) >> > > This is not confusing, and it's standard fare for any PHP/SQL developer. > Your proper choice is #2. The categories table would look like this: > > id integer not null autoincrement primary key > name varchar(25) > > Assuming you need more than one category for books, you're looking at > another linking table, where: > > bookid integer references books (id) > catid integer references categories (id) > > (This is pseudocode. I leave actual implementation and SQL niceties to > the OP.) > > The drop-down box is simple. Just harvest the categories from the > categories table, as: > > SELECT * FROM categories ORDER BY catname; > > Gather these into an array, and put the following in your PHP page: > > <select name="catid"> > <?php foreach ($catrecord as $cat): ?> > <option value="<?php echo $cat['id']; ?>"> > <?php echo $cat['name']; ?> > </option> > <?php endforeach; ?> > </select> > > This is working code. I've written hundreds of these, and this is the > way you do it (yes, you can do the syntax differently, if you like, but > the idea is the same). > > You might want to invest in a book like "PHP Cookbook" which has a great > deal of working code for you to look at and emulate. It's pretty much > all examples and their explanations. > > Paul > > Thank you, Paul. I'm plodding along and things are going pretty well... just have to rack my brain a LOT. Appreciate the suggestions. I'll figure out how this will work. More lessons to learn. :-) PJ -- Phil Jourdan --- pj@ptahhotep.com http://www.ptahhotep.com http://www.chiccantine.com