How to merge data using php.

Question

Currently my MySQL data stored like below

product             | total
------------------------------------------
puma,adidas         | 100.00,125.00     
puma                | 80.00
reebok,adidas,puma  | 70.00,100.00,125.00
adidas,umbro        | 125.00,56.00      

How to combine, explode, merge and total it like this in php?

puma     485.00
adidas   350.00
reebook  70.00
umbro    56.00

Answer 1

I don't know what your result set looks like, but the logic should be the same:

$combined = array();
foreach ($results as $result) {
    $productsArr = split(",", $result['product']);
    $totalsArr = split(",", $result['total']);

    // we'll assume both arrays are always the same size
    $prodCount = count($productsArr);
    for($i = 0; $i < $prodCount; $i++) {
        $combined[$productsArr[$i]] += (float)$totalsArr[$i];
    }
}
print_r($combined);

Answer 2

I have a quick question about your data structure: Why the heck is your data structure like that???

That being said, look to normalize your data (avoid duplicate data), create line items that belong to orders, a products table, etc...

products
--------
id
name
price


orders
------
id
created


order_items
------------
id
order_id
product_id
quantity

Now I can do queries like give me the top 5 largest totals in orders. What's the most popular item I sell? Let me change the name of this product but not have my entire data fall apart.