Related Products SQL Query

Question

I am trying to show something similar to related products on on my website. I have tested the following query, and I get no errors; but I get no results either.

<?php 
    $relatedStmt = $db->query("SELECT * FROM items WHERE tag LIKE id = 1 LIMIT 3");
?>

I also tried %1% and it displayed ALL the results, I assumed the code was thinking the query was just SELECT * FROM items.

The tags in the column are displayed in the following format: tagone two three four, so I am trying to display the products that have similar tags, hence why I used the LIKE clause.

Table screenshot

The current query is to relate with this query: SELECT * FROM items WHERE id = 1. So LIKE id = 1 is to find the tags that match this query to show the related products.

Think of `LIKE` as `CONTAINS` - `WHERE tag LIKE "%red%" will find all rows where the tag *contains* the string `red` — random_user_name, Jun 21 '14 at 12:47
@ncdreamy I have updated the question with a table screenshot. — Rahul Khosla, Jun 21 '14 at 12:52
Ok, so what are you trying to extract from that table? You query makes no sense when looking at the table. — NcDreamy, Jun 21 '14 at 12:52
@ncdreamy I am trying to check the `tag` column. So if it matches with the current sql query on the page, then show the similar products. The current SQL query is `SELECT * FROM items WHERE id = 1`. — Rahul Khosla, Jun 21 '14 at 12:54
per screen print your tag column hold different kind of value. not sure what this is `WHERE tag LIKE id = 1`? what's the desired result? — Rahul, Jun 21 '14 at 13:00
@Rahul Im wanting to display the products that have the same tag(s) as the id=1 product. — Rahul Khosla, Jun 21 '14 at 13:02
in that case you can simply do `SELECT * FROM items WHERE tag = (select tag from items where id = 1)` but that same as saying `SELECT * FROM items WHERE id = 1`. I am really confused with your question. — Rahul, Jun 21 '14 at 13:05
@Rahul, that query gave a result but it doesn't explode the tags (as the LIKE clause emulated). So the tag column has to be the exact match, there not separated by " "'s. — Rahul Khosla, Jun 21 '14 at 13:12
@Rahul Also just tried `where id like 1`, it gave the same result. — Rahul Khosla, Jun 21 '14 at 13:15
The way you are trying is totally wrong. `WHERE tag LIKE id = 1` makes no sense at all and completely a wrong construct. You should simply do `where id =1`. If there is specific result needed then edit your post and clarify the same. I think you are totally confused. — Rahul, Jun 21 '14 at 13:18
@Rahul the LIKE clause, gathers something LIKE something, as = gathers something EXACTLY as something. Example; john walked down the road. = john walked down the road. | john walked down the road LIKE john. — Rahul Khosla, Jun 21 '14 at 13:20
Given your table screenshot, am I correct that what you expect from your related entries query is item id 5? — ob1quixote, Jun 21 '14 at 13:22

score 1 · Answer 1 · edited May 23 '17 at 10:32

LIKE doesn't work the way you seem to expect. It's a character for character comparison, with wildcards, between the operands.

Of the mysql functions, closest to what you want is probably LOCATE or FIND_IN_SET. There are split solutions in other questions, e.g. "Can Mysql Split a column?" and "Split a MYSQL string from GROUP_CONCAT into an ( array, like, expression, list) that IN () can understand". However, even so there is no good way to compare the individual tags in your concatenated tag column with the individual tags of the other rows in your table.

Therefore, I think you'd be better off moving the tags into their own table with item_id as a foreign key. Then the solution is trivial (SQLFiddle):

-- Related Items
SELECT *
  FROM items
 WHERE id in (SELECT DISTINCT item_id
                FROM tags t
                JOIN (SELECT tag
                        FROM tags
                       WHERE item_id = 1
                      ) t1 ON t1.tag = t.tag
               WHERE item_id != 1
              )
;

score 0 · Answer 2 · answered Jun 21 '14 at 12:53

0

i am unsure about that id = 1 thing in your query but anyway you could try this: SELECT * FROM items WHERE tag LIKE '%search_this_tag%' LIMIT 3

answered Jun 21 '14 at 12:53

dog

1
1

1

Note that without ORDER BY, LIMIT is pretty much meaningless – Strawberry Jun 21 '14 at 12:55
I'm not trying to search an exact tag. Im wanting to match the tags with the id=1 row. – Rahul Khosla Jun 21 '14 at 12:55
@Strawberry Thanks, i'll get that changed on my side. – Rahul Khosla Jun 21 '14 at 12:56

Related Products SQL Query

2 Answers2