Get total number of products by brand with multiple categories with PHP and MySQL

I have a query: SELECT brand FROM products WHERE brand='".$brand."' AND category='".$id."'".$categories_list."

The idea is to get a count of the total products within a category or across multiple categories by a category ID or IDs by a brand number.

The code as it sits:

foreach ($product_brands as $brand) {
    $products_sql = mysqli_query($mysql_link, "SELECT brand FROM products WHERE brand='".$brand."' AND category='".$id."'".$categories_list."");
    $brand_total = @mysqli_num_rows($products_sql);
    array_push ($product_brands_count, $brand_total);
}

It works correctly when only counting a single category. However, it displays incorrect counts when trying to count multiple categories.

The $categories_list adds OR category='".$categories_data['id']."' to the query if there are multiple categories shown.

I’ve tried to move the brand='".$brand."' around in the query statement, but that hasn’t worked for me.

Here are some of the counts I get.

Single category: — which is correct

Brand 1 = 3 
Brand 2 = 5

Multiple Categories — which is not

Brand 1 = 8
Brand 2 = 11

Answer

Your primary problem is a logical prescedence issue. You are generating a where clause like:

WHERE brand = ? AND category = ? OR category = ?

Since AND has higher prescendence than AND, this actually means:

WHERE (brand = ? AND category = ?) OR category = ?

This accepts rows whose category matches the second category parameter, regardless of their brand.

You need parentheses to control the prescendence:

WHERE brand = ? AND (category = ? OR category = ?)

This can be shortened with IN:

WHERE brand = ? AND category IN (?, ?)

But bottom line, I don’t see the point for looping over the brands in php, fetching all rows and then counting them. Your database can manage sets of rows much more efficiently with an aggregation query.

Something like this should be close to what you need:

select brand, count(*) no_products
from products
group by brand
where category in (?, ?)

Note that all above queries are parameterized (ie the parameters are not concatenated in the query string, but passed to the query at execute time instead). You do want to use this technique to make your code more efficient and prevent SQL injection.