# Calculate the total price of products for each store

I have an application that take the user location, check which store sell on that location and then calculate the total price for the products the user selected on the shopping cart. For example, if the user selected:

Product | Amount
Shoes   | 1
T-Shirt | 3
Pants   | 2
Hat     | 1
Gloves  | 2


Then I need to check every store that sell on the users location and how much would it cost for the user to buy all the products. This is what I have so far:

/**
* @POST
* Variables received via POST method
* $cart: Shopping cart with the products user selected; *$location:   Location Object with the user address;
*/

// Getting the information needed to start the query
$store_list = getStoreList($location); // Another function to return an array with each store ID that sell on the user location
$product_list = array_column($cart->products, 'id_product');
$product_total = count($cart->products); // Total amount of unique items on the ShoppingCart. In this example, would be 5 (not the sum of quantity)

// Select the list of products and the URL reference for the store
$query = Sql("SELECT a.price, a.id_product, b.url FROM tb_product_store a, tb_store b WHERE a.id_store = b.id AND a.id_store IN (".implode(',', array_map('intval',$store_list)).")
AND a.id_product IN (".implode(',', array_map('intval', $product_list)).") "); /** * Now I need to group the product list for each Store URL instead of * having all the products, no matter which store it belongs to in a * single array. * * turns this: * array( * {id_produtct: 1, price: 4.99, url: 'store-a'}, * {id_produtct: 2, price: 6.99, url: 'store-a'}, * {id_produtct: 1, price: 4.99, url: 'store-b'}, * {id_produtct: 2, price: 6.99, url: 'store-b'} * ); * * into this: * array( * 'store-a': array( * {id_produtct: 1, price: 4.99, url: 'store-a'}, * {id_produtct: 2, price: 6.99, url: 'store-a'} * ), * 'store-b': array( * {id_produtct: 1, price: 4.99, url: 'store-b'}, * {id_produtct: 2, price: 6.99, url: 'store-b'} * ) * ) */ function group_by($array, $key) {$return = array();
foreach($array as$val) {
$return[$val[$key]][] =$val;
}
return $return; }$newProductList = group_by($query, 'url'); /** * Now I do all the calculations on these lists to get: * total_price: Total sum of the product list (price * quantity) * total_item: Total unique items on the list */$stores = array();
foreach ($newProductList as &$row) {
$obj = new StdClass();$obj->total_price = 0;
$obj->total_item = 0; foreach ($row as &$rowS) {$obj->url = $rowS['url']; foreach ($cart->products as $rowP) { if (intval($rowS['id_product']) === intval($rowP['id_product'])) {$rowS['quantity'] = $rowP['quantity'];$rowS['price'] = $rowS['price'] *$rowS['quantity'];

$obj->total_price +=$rowS['price'];
$obj->total_item++; } } } array_push($stores, $obj); } /** * Now I create 2 arrays and fill them with just the necessary information * to display on the view for the user to select which store he wants to * buy from. * * These arrays are: * list_complete: A list of the stores that has all the items selected on the shopping cart * list_incomplete: A list of the stores that DOESN'T have all the items selected */$list = array(
'list_complete' => array(),
'list_incomplete' => array(),
);
foreach($stores as &$row) {
$total_price =$row->total_price;
$total_item = intval($row->total_item);

// Load all the other information for each Store (such as name, logo, address, etc..) based on the Store URL
$obj = getStoreDetail($row->url);
$obj->total_price =$total_price;
$obj->total_item =$total_item;

// Check if the store has the correct amount of items
if ($total_item ===$product_total) {
array_push($list['list_complete'],$obj);
} else if ($total_item <$product_total) {
$obj->missing_items =$product_total - $total_item; array_push($list['list_incomplete'], $obj); } } // Now I just sort the arrays based on total price and amount of items // Sort complete array_multisort( array_column($list['list_complete'], 'total_price'), SORT_ASC,
$list['list_complete'] ); // Sort incomplete array_multisort( array_column($list['list_incomplete'], 'total_item'), SORT_DESC,
array_column($list['list_incomplete'], 'total_price'), SORT_ASC,$list['list_incomplete']
);

// And finally I return the list of stores
return $list;  What I find the most difficult is the fact that I need to take in consideration the amount of items selected on the shopping cart. If it was only 1 item for each product, I could do something like this: $query = Sql::load("SELECT
SUM(a.price) as 'total_price',
COUNT(a.id_product) as 'total_item',
b.url

FROM
tb_product_store a,
tb_store b

WHERE a.id_store = b.id
AND a.id_store   IN (".implode(',', array_map('intval', $store_list)).") AND a.id_product IN (".implode(',', array_map('intval',$product_list)).")

GROUP BY b.url
ORDER BY total_item, total_price
");


Is there a way I can improve this code taking these requirements in consideration? Performance is always a concern, since this can have many stores on the list, but it's more important that it works properly instead of faster.

In my opinion your code is ok and well written.

You can try to implement this logic using MySQL stored procedures/functions. This will shift load from your WEB/PHP server to the database server and might require less RAM; BUT, it could be slower. You need to test it on real data and compare both approaches.

To improve the speed of the posted code I suggest using caching for these functions:

getStoreList($location); getStoreDetail($row->url);


Especially the second one can be a bottleneck because it is called in cycle.

Also you can free some memory by setting all variables which are not necessary anymore to null or by using unset() if you're inside the cycle. More information on topic here.

P.S. One more suggestion regarding your code: In my opinion it is better to give variables more meaningful names. For example $newProductList can be $groupedProductList, $list can be $calculatedOffers or just \$offers etc. Also, the table name tb_product_store should be tb_store_product.