Request for related products

Question

I have a detailed page for products, and I want to add a module for "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 create a query that retrieves all products that match at least 2 of these tags, with the exception of the main product identifier displayed on the details page. For instance:

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 if the best way to do this is SQL ... maybe a PHP function using an array?

Thank.

Answer 1

. Products, ProductTags.

explode() . array_intersect, . count() > 1, ,

:

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

, . , productName tags.

PHP: array_intersect -

product_tags, :

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

SQL . . , !

Answer 2

, , , , , . , .

,    LIKE '% searchtag%'

. product_tags .