PHP/MySQL Search Engine Query for keywords in a related table

Question

I am trying to create a search function for my website. I have a main table called 'releases' which contains all of the main pages to search through. I have a second table called releases_index which contains keywords for each page.

Example:

releases:

# releases_id, releases_title
'10001', 'Scarlet Witch'
'10002', 'Vision'

releases_index:

# index_id, index_releaseId, index_value
'1', '10001', 'Scarlet Witch'
'2', '10001', 'Television'
'3', '10001', 'WandaVision'
'4', '10002', 'Vision'
'5', '10002', 'Television'
'6', '10002', 'WandaVision'

I am struggling to create an SQL query that can return the correct results. For example, if on the website I searched for 'scarlet witch' I want it to return index_releaseId 10001. I also want it to return this if I search for 'scarlet witch wandavision'.

Originally I was trying to do this with something like the below, but the problem was that it was looking for rows that contained the entire search query rather than release ID's which matched all of the query terms.

WHERE index_value LIKE '%scarlet%' AND index_value LIKE '%witch%' AND index_value LIKE '%wandavision%'

Can anyone advise what the best way do to do this is? I am doing this in PHP with PDO/MySQL

do you want 100001 and 100002 and all the others that have at least one of the seachwordsß — nbk, Apr 04 '21 at 22:35
See https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query — Strawberry, Apr 05 '21 at 07:58
that's because you are using `AND` for all your terms so they must match all of the words (which should not return anything for your example data), try changing it to `OR` should do the trick — jumper85, Apr 05 '21 at 09:08
@jumper85 that would return release IDs which don't match all of the search terms which isn't what I want — Josh, Apr 05 '21 at 13:32
@nbk each result should match all search words. Based on the example given, if I searched for 'scarlet wandavision' then I'd expect to get 10001 back, same thing if I searched 'scarlet witch wandavision' or 'wandavision witch scarlet' — Josh, Apr 05 '21 at 13:36
now I think I understand. check this answer to another question: https://stackoverflow.com/a/22298184/4239703 in your case it should be enough, to just query `release_index` table with a `group_concat` on `index_value` and the use of `Find_In_Set` with your single words separated by `OR` in `HAVING` clause. hope this makes sense :-) — jumper85, Apr 05 '21 at 13:59
I added an answer with a version that should fit your needs. in my previous comment it should be `AND` instead of `OR` — jumper85, Apr 05 '21 at 14:05

jumper85 · Accepted Answer · 2021-04-05T15:10:37.790

0

Here is the version that that I would use to achieve this:

     SELECT index_releaseId, GROUP_CONCAT(index_value) as idx_values
       FROM releases_index
   GROUP BY index_releaseId
     HAVING idx_values LIKE '%scarlet%'
        AND idx_values LIKE '%witch%'
        AND idx_values LIKE '%wandavision%'

Side note: GROUP_CONCAT is a vendor specific function and only available in MySQL/MariaDB

edited Apr 05 '21 at 15:10

answered Apr 05 '21 at 14:05

jumper85

472
3
9

I think this might be on the right track, but when I run this I get no results – Josh Apr 05 '21 at 14:55
can you tell me what collation you use? it might be a issue with case sensitive search. – jumper85 Apr 05 '21 at 14:59
utf8_general_ci - just tried with correct case's and still didn't return anything – Josh Apr 05 '21 at 14:59
ok, then case sensitive is not the issue here because general usually ignores casing – jumper85 Apr 05 '21 at 15:04
now it should work, problem was, that `Find_In_Set` looks for exact match, which is not the case for scarlet or witch, because in the set only Scarlet Witch exists – jumper85 Apr 05 '21 at 15:11
I think this will work, will build it into my site now and see how it does. Thank you:) – Josh Apr 05 '21 at 15:25

score 0 · Answer 2 · answered Apr 05 '21 at 14:34

As you search for each search words that have to be valid

CREATE TABLE table1 (
  `index_id` VARCHAR(3),
  `index_releaseId` VARCHAR(7),
  `index_value` VARCHAR(15)
);

INSERT INTO table1
  (`index_id`, `index_releaseId`, `index_value`)
VALUES
  ('1', '10001', 'Scarlet Witch'),
  ('2', '10001', 'Television'),
  ('3', '10001', 'WandaVision'),
  ('4', '10002', 'Vision'),
  ('5', '10002', 'Television'),
  ('6', '10002', 'WandaVision');

SELECT DISTINCT `index_releaseId`
 FROM table1 t1
 WHERE EXISTS(SELECT 1 FROM table1 WHERE `index_releaseId` = t1.`index_releaseId` AND  `index_value` LIKE '%Scarlet%') 
AND EXISTS(SELECT 1 FROM table1 WHERE `index_releaseId` = t1.`index_releaseId` AND  `index_value` LIKE '%Witch%') 
AND EXISTS(SELECT 1 FROM table1 WHERE  `index_releaseId` = t1.`index_releaseId` AND  `index_value` LIKE '%WandaVision%')

| index_releaseId |
| :-------------- |
| 10001           |

db<>fiddle here

PHP/MySQL Search Engine Query for keywords in a related table

2 Answers2