CODEWITHSUNDEEP
phpmysqlsqlpdosql-like
Rahul Khosla
Rahul Khosla

Reputation: 190

Related Products SQL Query

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.

Upvotes: 0

Views: 1706

Answers (2)

ob1quixote
ob1quixote

Reputation: 399

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
              )
;

Upvotes: 1

dog
dog

Reputation: 1

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

Upvotes: 0

Related Questions