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.
# releases_id, releases_title '10001', 'Scarlet Witch' '10002', 'Vision'
# 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
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