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
--
Paul M. Foster
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
