Query for related products

Question

I have detail page for products and i want to add a module of "related products".

In the db table "products" i store a value called "tags" for each product, something like "tag1, tag2, tag3, tag4".

Now i need to build a query that retrieve all products that match at least 2 of this tags, excluding the id of the main product displayed in the detail page. For example:

Main product

Product Name | tag1, tag2, tag3, tag4

Related Products:

Product Name | tag1, tag3, tag5

Product Name | tag3, tag4, tag6, tag7

I'm not sure the best way to do that is SQL... maybe a PHP function using array?

Thanks.

Answer 1

It's not a great idea to store a multi-valued attribute in a single field. Ideally, you would have a Products table, a Tags table and a ProductTags Table.

However, you could select the Tags for the Product and use explode() to get an array of Tags. The for each other product, do the same and use array_intersect to get an array of common elements. Then use count() > 1 to determine if it's related

So:

function getRelatedProducts($productName)
{
    $productResults = mysql_query("SELECT * FROM products WHERE productName = '$productName' LIMIT 0,1");

    $relatedProducts = array();

    if(mysql_num_rows($productResults) == 1)
    {
        $product = mysql_fetch_array($productResults);
        $tags = explode(",",$product['tags']);

        $otherProducts = mysql_query("SELECT * FROM products WHERE productName != '$productName'");

        while($otherProduct = mysql_fetch_array($otherProducts))
        {
            $otherTags = explode(",",$otherProduct['tags']);
            $overlap = array_intersect($tags,$otherTags);
            if(count($overlap > 1)) $relatedProducts[] = $otherProduct;
        }
    }

    return $relatedProducts;
}

It's a bit rough and ready but it should work. This code assumes you have columns called productName and tags.

PHP:array_intersect - Manual

If you go ahead with a product_tags table, you can use the following code to find related products:

function getRelatedProducts($productId)
{
    $sql = "SELECT p.*,COUNT(*) AS matchedTags FROM products p
            INNER JOIN product_tags pt ON pt.product_id = p.id
            WHERE pt.tag_id IN (SELECT tag_id FROM product_tags WHERE product_id = $product_id)
            GROUP BY p.id
            HAVING COUNT(*) > 1";

    $results = mysql_query($sql);

    $relatedProducts = array();

    while($result = mysql_fetch_array($results))
    {
        $relatedProducts[] = $result;
    }

    return $relatedProducts;
}

The important part is the SQL at the start of the function. It will give you the related products. Do with them what you will!

Answer 2

Others have pointed out that this,comma,separated,tag,list is not such a good idea. That's true, which I know the hard way.

If you MUST use it, you can use the table server to filter your result set with WHERE tag LIKE '%searchtag%'

But this search term is going to be quite slow, and is going to return false positive hits. You're FAR better off creating a product_tags table with a row for each tag for each product.