PHP/MySQL: Many-to-many/intersect table question

Question

I'm not really sure how to phrase the question, so let me just give an example of the problem:

Suppose there's a table which maps items to categories. Each item can have any number of categories, and each category can of course hold any number of items. So you have a table that looks like this:

items_categories

id item_id category_id

The problem is, I want to select all item id's which have specific category id's. For example, select all item_id's with category_id's of 1 and 2: I want to find all items that are associated with categories both 1 and 2. Obviously I can't use an AND statement, and an OR statement would return all item_id's with either category, but not necessarily both.

Here is my solution and the best thing I can think of: select all item_ids with category_id equal to 1 OR 2; iterate through the results in PHP and keep track of how many item_ids are associated with a category_id; and then unset all item_ids in the results that don't have the specified number of categories. Here's a snippet of my code:

// assume $results is an array of rows from the db
// query: SELECT * FROM items_categories WHERE category_id = 1 OR category_id = 2;
$out = array();
foreach ($results as $result)
{
    if (isset($out[$result['item_id']]))
        $out[$result['item_id']] ++;
    else
        $out[$result['item_id']] = 1;
}
foreach ($out as $key=>$value)
{
    if ($value != 2)
        unset($out($key));
}
return array_keys($out); // returns array of item_ids

Obviously if you have lots of different categories, you're selecting and processing way more information than you should theoretically need to. Any ideas?

Thanks!

Edit: Here's an example of a table and the information I want from it:

id item_id category_id
1 1 1
2 1 2
3 2 1
4 3 2

So say I'm interested in getting all of the items with categories 1 and 2. How do I get item #1 from my example table, given that I want only items with categories #1 and #2? If I select everything with categories 1 or 2 (as in my example above), I have to select the whole table in this case and "manually" remove item_id's 2 and 3, since they aren't associated with both category 1 and category 2. Hope this helps clarify a little.

Final edit: I figured it out, despite my apparent inability to describe what I'm trying to do, heh. Here's the query I came up with, for the record:

SELECT *
FROM
(
    SELECT item_id, COUNT(*) as count
        FROM items_categories
        WHERE category_id IN (1, 2)
    GROUP BY item_id
) table_count
WHERE count = 2;

In this case, the "(1, 2)" could be replaced with "(category_id1, category_id2, ...)", and the "2" at the end would be replaced with the number of categories I'm searching for.

So it finds out how many categories match the criteria for each item, and since I only want items where ALL the categories match, it only selects those where the number of categories equals the number of categories I'm looking for. This is of course assuming there are no duplicate categories or anything like that.

Thanks for the responses!

Answer 1

It seems that what is troubling you is that you are forced to do a linear search which of course takes O(n) time, but if you select elements from your database in sorted order, then can't you just use a binary search in O(lg n) time?

I hope this helps, If not, then maybe I misunderstood your question and I'd like you to clarify it a little bit.

Answer 2

SELECT
 foo
FROM
 bar
WHERE
foo IN (1,2) 

is this what you are looking for?

Answer 3

This is something you should be getting the database to do rather than PHP.

SELECT item_id                 # We want a list of item ids
FROM cat_items                 # Gets the item ID list from the cat_items table
WHERE cat_id IN (1, 2, 7, 11)  # List of categories you want to search in
GROUP BY item_id;              # As the same item can appear in more than one category this line will eliminate duplicates

This query does assume that the data in cat_items is accurate, in other words that the category and item IDs point to valid entries in the categories and items tables respectively. If you're using a database with foreign key support (The InnoDB engine for MySQL, Postgres, etc) enforcing foreign keys is not difficult.

To get a list of IDs in each category in the format you want, that's easily done on the SQL side too.

SELECT * 
FROM cat_items 
WHERE cat_id IN (1, 2, 7, 11)
GROUP BY cat_id, item_id;

If you just want a count of how many items are in each category you can also do that in SQL

SELECT cat_id, COUNT(item_id) AS items
FROM cat_items 
WHERE cat_id IN (1, 2, 7, 11)
GROUP BY cat_id;

If you need more data than just the ID then you can join against the table you need the data from.

SELECT items.* 
FROM cat_items 
JOIN items ON cat_items.item_id = items.id 
WHERE cat_id IN (1, 2, 7, 11)
GROUP BY item_id;

Answer 4

SELECT item_id FROM items_categories WHERE category_id = 1 AND item_id IN (SELECT item_id FROM items_categories WHERE category_id = 2)