mercredi 16 octobre 2019

How can I put multiple checkbox results in a WHERE clause in SQL?

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