Complex Mysql/php display

Rhiannon

I've been searching for a while now on how to do this. I have a book database. The way it is set up there are categories and sub-categories. Each book is able to fit in more than one category/sub-category for searching purposes. The fields I have for each book are the book_id, title, author, isbn, category_id, owner_id, location_id, sub_category_id, and rating. Each sub_cat_id is different (duh) and each category has many sub categories.

For example - a book Called MySQL & mSQL could fit in the database category in sub-categories mysql, msql, and sql and also fit in the programming category in sub-categories mysql, msql, and sql. This would mean that one book would have 6 different listings. Some books would have more.

I'm not sure how to perform a search for a book and have it only show the book once if a person, for example, does a seach on the ISBN and doesn't pick a category/sub-category. I'm completely lost. I hope I explained this semi-well, if not I can try to explain more.

Thnx a lot

mrmufin

The fast and dirty way to accomplish this is to put either a "SELECT DISTINCT title FROM book..." at the start of your query, OR stick a LIMIT clause at the end of the SELECT statement.

The better way to handle this is to re-evaluate your data structure so that only distinct results are retrieved from ANY query. So a book called "MySQL and mSQL" would, indeed be available when searching various sub-categories... Perhaps you could use those 'additional' results to create links to those other categories, since there is at least some plausible relationship there. But at the end of the search, users expect distinct results...

Have fun...

Rhiannon

I understand what you're trying to say. My query right now is pulling information from 5 tables using joins. I'm not sure how it'll work using the different tables to select a distinct book. I tried selecting a distinct b.title, . . . . from book b etc, but with my joins it still showed the 3 test cases of MySQL & mSQL that I have in the database. I'm completely lost as to how to get one to show and the others not.

jerdo

Might I suggest an easy solution to the ISBN search issue? Try not using the category when searching by ISBN at least I think ISBNs are unique. Otherwise I would look at changing your DB structure and maybe create boolean columns for the categories and maybe search by those.