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
– Strawberry

2013-12-12 18:17:38 +00:00
Commented Dec 12, 2013 at 18:17 — Strawberry
– Strawberry, Commented Dec 12, 2013 at 18:17

Funk Forty Niner · Accepted Answer · 2013-12-12 18:25:43Z

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, 2013 at 18:25

Funk Forty Niner

74.2k14 gold badges71 silver badges146 bronze badges

answered Dec 12, 2013 at 18:18

wesside

5,7605 gold badges32 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

wesside Over a year ago

I just got schooled ;)

Funk Forty Niner Over a year ago

Thought I'd lend a helping hand ;-)

Ady Smith Over a year ago

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!

wesside Over a year ago

MATCH(name) AGAINST('+"black tray"'); if you want them together. This explains the use cases simply: stackoverflow.com/questions/14572375/… Mind you, there are several more options, just check the documentation and I gaurantee you can find what you need.

Mathieu Dumoulin · Accepted Answer · 2013-12-13 17:24:12Z

2

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!

edited Dec 13, 2013 at 17:24

answered Dec 12, 2013 at 18:17

Mathieu Dumoulin

12.3k7 gold badges47 silver badges71 bronze badges

3 Comments

Ady Smith Over a year ago

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!

wesside Over a year ago

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

Mathieu Dumoulin Over a year ago

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!

Jacek Krysztofik · Accepted Answer · 2013-12-12 18:23:14Z

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, 2013 at 18:23

Jacek Krysztofik

1,3961 gold badge15 silver badges29 bronze badges

Collectives™ on Stack Overflow

Trying to create a keyword search in PHP and MySQL

3 Answers 3

4 Comments

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related