Duplicate results help needed!!

[macmatt]

I am producing a site for a book seller, and have now been supplied with a list of subcategories to complement the categories that I was already using.

My categories and subcategories are all stored in one table (categories) with the columns - id(INT), cat_type(INT) and category(TEXT). The cat_type column connects subcategories to their categories, ie a row with cat_type 0 is a category and a row with any other cat_type is a subcategory of the category whose id matches the cat_type. This is all fine and I can extract a list of subcategories that belong to a particular category to populate a selection menu once the user has chosen a category.

However, if the user doesn't then select a subcategory I would like to display all the books within that category and it's relevant subcategories. This is what I have for the query:

	$query_category = sprintf("SELECT DISTINCT category.category, category.cat_type, book_type.type, books.id, books.type_id, books.category_id,
books.title, books.author, books.author2, books.author3, books.`year`, books.pages, books.price, books.oldprice,
	books.pic 
FROM category, books, book_type
WHERE (books.category_id = category.id OR books.category_id = category.cat_type) 
AND (category.id = %s OR category.cat_type = %s)
AND books.type_id = book_type.id ORDER BY books.title ASC", $catid_category, $catid_category);

This works fine unless there are books that only belong to the category and not a specific subcategory, when each book is listed four times. Ideally, all books will be assigned to a subcategory, but it is possible that they won't so I need this to work.

Apologies for the ridiculously long post, but any help would be greatly appreciated. 🙂

[steadyguy]

Perhaps this is not the sort of response you're looking for, but ... I think you're doing this the complicated way ... I think it might be easier if you used separate tables for the categories and subcategories - with an extra column in the subcategories indicating to which category it belongs. This could even be a foreign key, which would help if the structure ever changes much. This would probably help with that selection menu, too:

categories
ID SMALLINT UNSIGNED
name VARCHAR(16)

subCategories
ID SMALLINT UNSIGNED
catId SMALLINT UNSIGNED
name VARCHAR(16)

books
ID INT UNSIGNED
catId SMALLINT UNSIGNED
subCatId SMALLINT UNSIGNED
title TINYTEXT
etc ...

With that, you can do this easy query:

SELECT b.id, b.title
FROM books b
WHERE b.catId = '1' OR b.subCatId = '2';

Which gives you all books which are either in category 1 or subcategory 2 - these don't necessarily have to be related, but you can force the relation with foreign keys, and generating the select boxes (or whatever) from the other tables will also be easier with separate category and subcategory tables (with a LEFT JOIN and more than likely, less PHP). Over all, it's faster, and easier to read, not to mention understand.

My 2 cents.

p.s. the sprintf() function (and it's relatives) is rather resource-expensive ... especially when you can use variable interpolation.