MYSQL LEFT JOIN with multiple rows (php search function)

Question

i have these two tables: products:

id	name
1	product 1
2	product 2

and featuretoproduct:

id	productid	featureid	featurecat
1	1	7	3
2	1	5	3
3	2	5	3

i am trying to search a product that have some feature id, and i have 3 options:

if i search featureid 7, i need to get product 1 only. if i search featureid 5, i need to get product 1 and 2. if i search featureid 5 and featureid 7, i need to get product 1 only.

this is my php function:

function Get_products3($id,$q)
{   
    $query = "SELECT p.* 
          FROM products p
          INNER JOIN featuretoproduct fp 
          ON p.id = fp.productid
            ".$q."
                    GROUP BY fp.productid ORDER BY p.id ASC";
    $Container_db = Sdba::db();
        $Container_db->query($query);
        return $Container_db->result();
}

and i have a string that handle the final query string of left join:

$query = " WHERE ( ";

second part:

$features = ["7","5"]; //this can be change to different variations like ["5"] or ["7"] or ["7","5"]...
foreach($features as $item):
            $query.="fp.featureid = ".$item." AND ";
        endforeach;

//last part (close the query string and remove " AND " from last loop):
$query = substr($query, 0, -5)." )";

here i am calling the function:

$checker = Get_products3($_GET["id"],$query);

i tryed to put OR instead AND in the loop but then if i choose

["7","5"]

i get the two products instead of product 1 only.

any help please?

You need to _count_ the number of matching records per product, and make sure that number is at least as big as the number of features you are searching for (in a HAVING clause.) — CBroe, Feb 23 '23 at 13:21
sorry, i meant with the function. i mean, if i send the count($features) to the function, how will my query looks like? — michael, Feb 23 '23 at 13:25
Your WHERE clause needs to select the records with matching feature IDs using OR (or via `IN()`), because you want _all_ the records for the product, that match the given features. You still GROUP BY your product, and then demand that the COUNT of records in the group matches that of your criteria - `HAVING COUNT(*) = 2` That `2` you'll determine dynamically, based on how many entries you have in $features. — CBroe, Feb 23 '23 at 13:29

score 0 · Accepted Answer · answered Feb 23 '23 at 13:42

You need to count the number of matching records per product, and make sure that number is as big as the number of features you are searching for.

Your WHERE clause needs to select the records with matching feature IDs using OR (or via IN()), because you want all the records for the product, that match the given features. You still GROUP BY your product, and then demand that the COUNT of records in the group matches that of your criteria - HAVING COUNT(*) = 2

That 2 you'll determine dynamically, based on how many entries you have in $features.

MYSQL LEFT JOIN with multiple rows (php search function)

1 Answers1