0

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

DarkBee
  • 16,592
  • 6
  • 46
  • 58
JoyMan
  • 1
  • Nice, localhost link. – Oen44 Dec 05 '18 at 18:01
  • localhost urls are only accessible to you. We - as remote people - can not access **local**host – Martin Dec 05 '18 at 18:01
  • Hello JoyMan ... welcome. First off, your code is subject to SQL Injection attacks. Please read up on filter_input() and what an SQL Injection attack is. Next, consider whether your application would benefit from using a FullText Search Engine, such as Lucene, SphinxSearch, ElasticSearch, etc. LIKE searches directly in the database can be made to work OK, but if you want *relevant* results you're gonna have some issues ... issues that the Lucene/Sphinx/Elastic teams have already addressed in their software. – Kevin_Kinsey Dec 05 '18 at 18:02
  • 1
    Does [this question](https://stackoverflow.com/questions/9736284/mysql-where-in) hold the answer for you? – Martin Dec 05 '18 at 18:03
  • Also, in order to search *all* categories, it should be as simple as omitting the category test in the SQL. But you might not want to do that if you have millions of rows to search ... – Kevin_Kinsey Dec 05 '18 at 18:04
  • define "multiple categories"? IF it's *every* category, then you simply search the Products table. If it's a user choice of categories then use [this link from my comment above](https://stackoverflow.com/questions/9736284/mysql-where-in) – Martin Dec 05 '18 at 18:18
  • @Martin [Here is a screenshot of the website](https://i.imgur.com/LXOlqPL.png) So the USB novelties is highlighted which means now I'm in that category. I know when I search the term 'Pizza' it shows up in that category. However I know that that particular USB with the term 'Pizza' is in 2 other categories. That's what I mean with multiple categories. Cause at the moment when I click to a different category it just switches without remembering that I clicked on USB novelties and searches for a 'Pizza' product in a different category. I tried the post you linked didn't work unfortunately. – JoyMan Dec 05 '18 at 18:42
  • @Kevin_Kinsey as of now I'm aware of SQL injection this is just a little project I wanted to try out. I actually have wrote a little code that searches the database without selecting any category here it is: `(isset($_GET['producttitle']) && $_GET['producttitle'] != "") { $like = "%" . $_GET['producttitle'] . "%"; $query = $readconn->prepare("SELECT S.StockItemID, S.StockItemName, S.Photo, S.UnitPrice, S.Photo FROM stockitems S WHERE StockItemName LIKE ?"); $query->execute(array($like));` – JoyMan Dec 05 '18 at 18:50
  • @JoyMan so you want: `"find this product with id X in categories that are NOT current category"` and output th result as additional categories. – Martin Dec 06 '18 at 10:07

1 Answers1

0

I think you should transform the where part of your query to somethng like

WHERE SG.StockGroupID IN (" . $categories . ") ...

where $categories should be a string like '1,2,4' created using a UI that allow the User to pick the categories he needs.

Martin
  • 22,212
  • 11
  • 70
  • 132
Luca Lupidi
  • 164
  • 5