I have this search function with categories and when you click on this category you will search within that category, however I want to make it so that I'm able to search a keyword within multiple categories. This is my form:
<form method="get" action="zoek.php">
<input type="text" name="producttitle" placeholder="Productnaam">
<input type="hidden" name="prodgroup" value="<?= (isset($_GET['prodgroup'])) ? $_GET['prodgroup'] : '' ?>">
<input type=submit class="btn btn-primary" value="Zoek...">
</form>
This is the code where you clicked on a category and searched a word
<?php
if (isset($_GET['prodgroup']) && $_GET['prodgroup'] != "" && isset($_GET['producttitle']) && $_GET['producttitle'] != "") {
$category = $_GET['prodgroup'];
$prodtit = $_GET['producttitle'];
$query = $readconn->prepare("
SELECT S.StockItemID, S.StockItemName, S.Photo, S.UnitPrice, S.Photo, S.Brand
FROM stockitems S JOIN stockitemstockgroups SISG ON S.StockItemID = SISG.StockItemID
JOIN stockgroups SG ON SISG.StockGroupID = SG.StockGroupID
WHERE SG.StockGroupID = '" . $category . "'
AND S.StockItemName LIKE '%" . $prodtit . "%'
");
These are the categories with their ID's:
.--------------.---------------------.
| StockGroupID | Col2 |
:--------------+---------------------:
| 1 | Novelty Items |
:--------------+---------------------:
| 2 | Clothing |
:--------------+---------------------:
| 3 | Mugs |
:--------------+---------------------:
| 4 | T-Shirts |
:--------------+---------------------:
| 5 | Airline Novelties |
:--------------+---------------------:
| 6 | Computing Novelties |
:--------------+---------------------:
| 7 | USB Novelties |
:--------------+---------------------:
| 8 | Furry Footwear |
:--------------+---------------------:
| 9 | Toys |
:--------------+---------------------:
| 10 | Packaging Materials |
'--------------'---------------------'
This table has all the items and as you can see some items can be in multiple categories.
.-----------------------.--------------.
| StockItemStockGroupID | StockGroupID |
:-----------------------+--------------:
| 4 | 1 |
:-----------------------+--------------:
| 1 | 2 |
:-----------------------+--------------:
| 2 | 2 |
:-----------------------+--------------:
| 3 | 3 |
:-----------------------+--------------:
| 5 | 4 |
:-----------------------+--------------:
| 6 | 7 |
:-----------------------+--------------:
| 8 | 7 |
:-----------------------+--------------:
| 7 | 8 |
:-----------------------+--------------:
| 9 | 9 |
:-----------------------+--------------:
| 10 | 2 |
'-----------------------'--------------'
here is also another table that has all the names of the items so I can search based on keywords. But I don't think I need that here. So what I've tried to do is change the WHERE statement to a IN like this:
WHERE SG.StockGroupID IN (SELECT StockGroupID FROM stockitemstockgroups)
however it still won't hold multiple categories. This is the URL
http://localhost/kbs/zoek.php?producttitle=pizza&prodgroup=1
While I think it's supposed to be like this:
http://localhost/kbs/zoek.php?producttitle=pizza&prodgroup=1&prodgroup=2
