[RESOLVED] Multiple Categories for data

BlackRaven

Alright, my brain is having a bit of trouble wrapping around the solution to a problem I have. I can think of several solutions, but they all seem like horrible hacks and aren't elegant in the slightest.

I'm working on a website for a publishing house. I'll be storing books in a MySQL database, with all of the information (title, author, isbn, price, publication date, description, etc, etc, etc). The issue is how I will be categorizing these books by genre.

I have many different genres. Books can belong to one or more genres. So for example, a book might be both 'Biography' and 'Non-Fiction'. Or, perhaps 'Crime' and 'Fiction' and 'Translated Fiction', or 'Crime' and 'Biography' and 'Non-Fiction'. Or maybe just 'Travel.' You get the idea. We haven't quite settled on the exact genres yet, but it is clear from the available titles that there will be books that fall into more than one category.

I've decided that on the page where you enter the information, the user will be able to use checkboxes to pick as many of these genres as they like for a book. My trouble is how to store it in the database. I considered having a bunch of fields in the book table (one for each genre) and setting them or unsetting them as neccesary (this would make it easy to insert, and not too bad to display). Alternatively, I could have one field, and comma-separate the genres (this would make them easy to display, but slightly ugly to edit).

How would you store this information?

jdorsch

This is a common structure and the solution is a many to many relationship. You will need three tables, your books table, your genre table, then a relationship table

tblBooks
bookName
bookID
bookAuthor
...

tblGenres
genreID
genreName
...

tblBookGenres
bookID
genreID

So if Moby Dick (bookID 15) is in genres classic (genreID 10) and adventure (genreID 38) you will have two records in tblBookGenres

bookID genreID
10 15
10 38

In your HTML name the checkboxes the same name with doublebrackets as follows

<input type="checkBox" value="10" name="genreIDs[]">Classic
<input type="checkBox" value="24" name="genreIDs[]">Romance
<input type="checkBox" value="38" name="genreIDs[]">Adventure

When the form is submitted you will have an array in PHP


$genreIDs = $_POST['genreIDs'] ;
$bookID = $_POST['bookID'] ;

foreach( $genreIDs as $genreID )
{
  // do some SQL
  // INSERT INTO tblBookGenres (bookID, genreID) VALUES( {$bookID}, {$genreID} )
}

BlackRaven

Just what I was looking for.

Cheers!

modifiedcontent

Is there a full working code example of this principle anywhere?

I'm not a real PHP programmer. I don't think I'd be able to fill in the blanks. How would you use this?

I've tried Dan LaFlamme's solution, but it's apparently full of errors. I couldn't get it to work.

If I can get a simple example working, I'll know how to adapt it to my situation.