I need to write a WHERE clause against a varied number of categories in my database. How would I best approach this?
This is what I'm doing: I am creating a search page to display results from my database of events. It works fine and sorts and filters according to the criteria I have. I want to add the ability to filter by category as well. Each event in my table can have multiple categories. I want to make it so that when I mark that category in my search form, it displays items from that category only.
Therefore I can check the box "Category ID 24" in my search and filter form and it will output events like
EVENT: Magic Show
When: Oct 9th, 2:00pm
Categories: 24, 32, 15
since Category 24 was one of the categories for this event.
Right now I have it "working" by taking each result and doing a second SQL call to check to see if it belongs to that category, which is resource-intensive but effective. I also got a "better" version working where it checks against a string of category ids and decides whether to show it. Effective, but still not proper code.
What makes this difficult are my tables. I have a table Events, which contain information about each event, a table Categories, which lists all possible categories, and one called EventCategories which assigns each eventID to a categoryID, one per row.
Here's my SQL:
SELECT
e.ID,
e.Title,
GROUP_CONCAT(c.ID SEPARATOR ' · ') as Categories
FROM
Events e
LEFT JOIN EventCategories ec ON ec.EventID = e.ID
LEFT JOIN Categories c ON c.ID = ec.CategoryID
WHERE
".$cond."
ORDER BY
e.Title DESC
The Group Contact works so that when I type $row['Categories'] it displays something like 23 · 28 · 26. This is great, but I'd need to put this in my WHERE clause to check against these numbers, and that's where I'm stuck.
I need to make this so that I can say something like "WHERE all events with category '23'" will display. I'm grabbing the category from the POST data, do that's not a problem, but the tricky part is writing the WHERE clause for a varied number of categories.
Any help would be appreciated!
Aucun commentaire:
Enregistrer un commentaire