Hello folks,
I’m currently facing an issue and I hope that somebody can give me some insight on this. I’ll start by explaining what my script is supposed to do and what I’m facing right now.
Basically, the script acts as a search engine for this website I’m building. In the backend the admin can create tags that can act as filter options(if the admin wants it) that will be displayed on to the search page.
Detailed version:
So to start, I made four tables in my database. tags, tag_groups, item_tags, items.
tag_groups acts as a group for the tags (self-explanatory)
tags belongs to tag_groups
item_tags belongs to tags and also belongs to items (this acts as a bridge between items and tags so that an item can support more than one tag. It contains 3 id’s, the primary key of the table itself, the primary key of the item table and the primary key of the tag table)
After I created the table, I then started by working with the filter field in the following step order.
- I displayed all of the filter options(tags) containing all the ID’s in their input fields.
<?php
foreach($tagGroups as $group)
{
echo "<h4>".$group->name."</h4>";
switch($group->type)
{
case "checkbox":
$list = CHtml::listData($group->tags, 'tid', 'name');
echo $form->checkBoxList($search, "tagsSelected", $list, array("name" =>"Search[tagsSelected][".$group->tgid."][]"));
break;
case "radio":
$list = CHtml::listData($group->tags, 'tid', 'name');
echo $form->radioButtonList($search, "tagsSelected", $list, array("name" =>"Search[tagsSelected][".$group->tgid."][]"));
break;
}
}
- When I submit the form, I manage to put all of the selected filter tags in an array, ready to check if the database has any links in item_tags for these tags. All in here.
$tagsSelected = array_filter($_POST['Search']['tagsSelected']);
- The tricky part and this is where I’m stuck, basically, with the list of tags that I have that are selected by the user, I try to filter these tags in item_tags. So if like 3 tags are selected, and only 2 of them are in item_tags for a specific item, then it should not display that item because it does not meet the conditions that the user requested. An example is;
Bob wants to buy a table from the store, but he’s very into tables so he has some conditions for these tables, the conditions are: They must be round, flat, and small. So in that search engine he ticks in these three conditions. In our database there is a table that -almost- meet his conditions; it’s flat and small, but alas it’s not round. Therefore, the page returns “Sorry, the table you were looking for is not in our database.”
TL;DR:
I need to filter items through the database based on tags(filter option), but I can’t seem to figure out how. If 2 out of 3 tags are attached to an item, it’s not supposed to show up that item. Is there a certain way to do this?
At last.
So that’s my problem, I’ve been brainstorming for hours and googling, but can’t find any possible ideas on how to approach this. Is there an easy SQL command or is there a way to approach this via Yii or PHP? I’d love to hear your insight and ideas. Thanks in advance
Hope any of you scripters can come up with ideas!