Return the most frequent result by field value in a for loop?

Question

I have a for-loop that returns the manufacturer name corresponding to a list of products:

for ($i = 0, $n = sizeof($order->products); $i < $n; $i++) {
    $v_query = tep_db_query("SELECT manufacturers_id FROM ".TABLE_PRODUCTS." WHERE products_id = '".$order->products[$i]['id']."'");
    $v = tep_db_fetch_array($v_query);
    $mfg_query = tep_db_query("SELECT manufacturers_name FROM ".TABLE_MANUFACTURERS." WHERE manufacturers_id = '".$v['manufacturers_id']."'");
    $mfg = tep_db_fetch_array($mfg_query);

echo $mfg['manufacturers_name'];
}

How do I return just one manufacturer name of the most frequently appearing one in the list? I can't seem to get the results properly assigned as an array in order to do an array count/search.

I'm a rookie to this, can you give an example? – user3888854 Oct 22 '14 at 21:28 — user3888854, Oct 22 '14 at 21:28

score 0 · Accepted Answer · answered Oct 22 '14 at 22:51

First of all you could insert all your product_ids in one array, to put it in one query:

$productIds = array();
for ($i = 0, $n = sizeof($order->products); $i < $n; $i++) {
   $productIds[] = $order->products[$i]['id'];
}
$productIds = array_unique($productIds);

Than you have to do it in one query for all product_ids, like this:

$sql = "SELECT manufs.manufacturers_id, manufs.manufacturers_name, COUNT(*) as manufacturers_count 
   FROM ".TABLE_PRODUCTS." prods 
   JOIN ".TABLE_MANUFACTURERS." manufs
   ON prods.manufacturers_id = manufs.manufacturers_id 
   WHERE products_id IN ('". implode("', '", $productIds) ."')
   GROUP BY manufs.manufacturers_id
   ORDER BY manufacturers_count DESC";

It will get you all manufacturers_id's, their names along with the number of rows present in the database, sorted descending (from high to low).

EDIT:

I noted that you don't use order's id in any way, so the query above (and the one you posted - also) will look among all orders you have and count most popular products. If you want to get most popular product for one/several orders, you need to add a JOIN with your orders table and then add a specific condition to your WHERE clause.

And tons of unnecessary sql queries ;) – Kleskowy Oct 22 '14 at 23:39 — Kleskowy, Oct 22 '14 at 23:39

Return the most frequent result by field value in a for loop?

1 Answers1