Trying to create a keyword search in PHP and MySQL

Question

I am building a website that will have a searchable product database. For the sake of simplicity, my table has three columns:

id
img (a reference to an image of the product)
tags (varchar 255, a list of tags used for searching, ex. "chair wood brown")

I have the query:

$query = "SELECT `img` FROM `products` WHERE `tags` LIKE '%$q%'";

This works exactly how I want it to, with the only problem being that it would only select rows if the tags were in the order searched. For example, if there were 3 rows:

Row 1 with tags "black tray"
Row 2 with tags "tray black"
Row 3 with tags "black tray layer"

And you searched for "black tray", both Row 1 and Row 3 would display, but not Row 2.

I'm trying to get all three rows to be displayed, no matter the order of words. I want, for example, if you searched "black tray", for them all to be displayed, but if you searched "tray layer", then only Row 3 would be displayed. Perhaps this will require re-thinking the original query, I'm not sure.

Many thanks!

Aha, the dark arts of regex – Strawberry Dec 12 '13 at 18:17 — Strawberry, Dec 12 '13 at 18:17

score 3 · Accepted Answer · edited Dec 12 '13 at 18:25

3

You need to use MATCH AGAINST which is far more powerful. Plus you can index it all and improve the speed as well.

Consult the following pages below:

edited Dec 12 '13 at 18:25

Funk Forty Niner

74,450
15
68
141

answered Dec 12 '13 at 18:18

wesside

5,622
5
30
35

I just got schooled ;) – wesside Dec 12 '13 at 18:27
1

Thought I'd lend a helping hand ;-) – Funk Forty Niner Dec 12 '13 at 18:29
I have tried to use this too but I couldn't find a way to make it so it only selects rows that have all of the searched tags, and not some of them. Is there a way to do this? Many thanks! – Ady Smith Dec 13 '13 at 15:42
`MATCH(name) AGAINST('+"black tray"');` if you want them together. This explains the use cases simply: http://stackoverflow.com/questions/14572375/mysql-search-for-string-and-number-using-match-against Mind you, there are several more options, just check the documentation and I gaurantee you can find what you need. – wesside Dec 13 '13 at 15:45

Mathieu Dumoulin · Answer 2 · 2013-12-13T17:24:12.167

It might be too late, but if possible, instead, use another table for your tags in the form of:

product_id | tag
1          | black
1          | tray
2          | tray
2          | black
3          | black
3          | tray
3          | layer

This will allow you to lookup any product that as one or more tag using this:

SELECT DISTINCT products.* 
FROM products LEFT JOIN tags ON tags.product_id = products.id 
WHERE tags.tag IN ('black', 'red', 'light')

EDIT

If you want all tags to be present, you have to issue x queries to x tags to search using this:

SELECT *
FROM
    (SELECT DISTINCT products.* 
    FROM products LEFT JOIN tags ON tags.product_id = products.id 
    WHERE tags.tag = 'black') AS set_a
INNER JOIN
    (SELECT DISTINCT products.* 
    FROM products LEFT JOIN tags ON tags.product_id = products.id 
    WHERE tags.tag ='red') AS set_b ON set_a.product_id = set_b.product_id
INNER JOIN
    (SELECT DISTINCT products.* 
    FROM products LEFT JOIN tags ON tags.product_id = products.id 
    WHERE tags.tag = 'light') AS set_c ON set_b.product_id = set_c.product_id

This will intersect all records found and leave you with only potential records. Note that this is a really heavy request, i'm not aware if the INTERSECT keyword was implemented yet in MySQL which would be a much better approach i'm sure!

This works, however it still selects products that has any of the tags searched, and I want it to select products that has ALL of the tags searched. Does that make sense? Is there a way to do that with your method? Thanks for all your help! — Ady Smith, Dec 13 '13 at 15:20
This is a solid approach, but I would create a cached table, indexed. I would have a table like this in InnoDB to leverage FK's. But, you can always create a script to build a cached script indexed. Would be much faster, plus you can leverage `MATCH AGAINST` — wesside, Dec 13 '13 at 15:48
If you want all tags to be present in your search thats a completly different issue. Then you have to issue the same request more than once and use an INTERSECT which in fact is an INNER JOIN between two queries. look at my edit, posted soon! — Mathieu Dumoulin, Dec 13 '13 at 17:21

score 0 · Answer 3 · answered Dec 12 '13 at 18:23

0

I would recommend storing single-word tags in a separate relation (table) and:

preg_replace('\s+', "','", $q);
$query = "SELECT `img` FROM `products` LEFT JOIN `tags` USING (`id`) WHERE `tags`.`content` IN ('$q')";

answered Dec 12 '13 at 18:23

Jacek Krysztofik

1,266
1
13
29

Trying to create a keyword search in PHP and MySQL

3 Answers3