php - Optimize SQL query in foreach() -
i have android app, virtual store.
in app made query fill list --> category --> subcategory --> products
. problem here optimisation in foreach
.
how can optimize foreach?
shop::setidshop(tools::getvalue('boutique')); $cntxt = context::getcontext(); $cntxt->shop = new shop(tools::getvalue('boutique')); $categorie = new category(tools::getvalue('id_category')); $result_product_count = db::getinstance()->executes(' select count(p.`id_product`) totalproducts `'._db_prefix_.'product` p , `'._db_prefix_.'product_shop` ps p.`active` = 1 , p.`id_product` = ps.`id_product` , ps.`id_shop` = '.$cntxt->shop->id); $count = $result_product_count[0]['totalproducts']; $promos = product::getpricesdrop($id_lang = 1, $page_number = 0, $nb_products = $count, $count = false, $order_by = null, $order_way = null, $beginning = false, $ending = false, $context = $cntxt); $products = $categorie->getproducts($context->language->id,0,$result_product_count[0]['totalproducts'] , null ,null , false , $active = true, $random = false, $random_number_products = 1, $check_access = true, $context = $cntxt); $sql_gestion_stock = 'select value `ps_configuration` name="ps_stock_management" , id_shop="'.$cntxt->shop->id.'"'; $result_gestion_stock = db::getinstance()->executes($sql_gestion_stock); $sql_order_stock = 'select value `ps_configuration` name="ps_order_out_of_stock" , id_shop="'.$cntxt->shop->id.'"'; $result_order_stock = db::getinstance()->executes($sql_order_stock); $resultat = array(); $row = array(); foreach($products $p) { $prod = new product($p['id_product']); $order_out_of_stock = configuration::get('ps_order_out_of_stock','','',$cntxt->shop->id); $stock_management = configuration::get('ps_stock_management','','',$cntxt->shop->id); $sql_stock = 'select out_of_stock ' ._db_prefix_. 'stock_available id_product="'.$p['id_product'].'" , id_shop="'.tools::getvalue('boutique').'"'; $out_of_stock = db::getinstance()->executes($sql_stock); $stock_dispo = $out_of_stock[0]['out_of_stock']; if( $stock_management == 0 ){ $qty = 9999; $out_of_stock_mobile = 1; } else{ if($stock_dispo == 0){ $qty = $prod->getrealquantity($p['id_product'],0,0,tools::getvalue("boutique")); $out_of_stock_mobile = 0; } elseif( $stock_dispo == 1 ){ $qty = 9999; $out_of_stock_mobile = 1; } elseif( $stock_dispo == 2 ){ if($order_out_of_stock == 1){ $qty = 9999; $out_of_stock_mobile = 1; } else{ $qty = $prod->getrealquantity($p['id_product'],0,0,tools::getvalue("boutique")); $out_of_stock_mobile = 0; } } } $link = new link(); $img=''; $imglink = ''; if (count($prod->getimages($cntxt->language->id ,$context = $cntxt ))>0) { $imgcover = product::getcover($prod->id, $context = $cntxt); $imglink = $link->getimagelink($prod->link_rewrite[1] , $imgcover["id_image"] , 'medium_default'); $imglink = 'http://'.$imglink; $largeimglink = $link->getimagelink($prod->link_rewrite[1] , $imgcover["id_image"] , 'large_default'); $largeimglink = 'http://'.$largeimglink; } $row['hassold']= 0; if (count(promos)>0) { foreach($promos $promo) { if ($promo['id_product'] == $p['id_product']) { $rowprod = array('id_product' => $p['id_product'],'out_of_stock' => $prod->out_of_stock); $productprop = $prod->getproductproperties($id_lang = 1 , $rowprod , $context = $cntxt ); $now = time(); $date1 = strtotime($productprop["specific_prices"]["to"]); $diff = abs($date1 - $now); $tmp = $diff; $retour = array(); $retour['second'] = $tmp % 60; $tmp = floor( ($tmp - $retour['second']) /60 ); $retour['minute'] = $tmp % 60; $tmp = floor( ($tmp - $retour['minute'])/60 ); $retour['hour'] = $tmp % 24; $tmp = floor( ($tmp - $retour['hour']) /24 ); $retour['day'] = $tmp; $row['hassold']= 1; $row['temps_restant']= $retour['day'].'j '.$retour['hour'].':'.$retour['minute'].':'.$retour['second']; } } } $row['image']= $imglink; $row['titre']=$prod->name["1"]; $row['description']=strip_tags($prod->description["1"]); $row['largeimage']= $largeimglink; $images = $prod->getimages((int)$cntxt->language->id); $imagesmeduim=array(); $imageslarge=array(); $j=0; foreach($images $i) { if (!$i['cover']) { $imglink_list = $link->getimagelink($prod->link_rewrite[1] , $i["id_image"] , 'medium_default'); $largeimglink_list = $link->getimagelink($prod->link_rewrite[1] , $i["id_image"] , 'large_default'); $imagesmeduim[$j]=$imglink_list; $imageslarge[$j]=$largeimglink_list; $j++; } } $row['qty']= $qty; $row['out_of_stock'] = $out_of_stock_mobile; $row['id_product']=$p['id_product']; $row['sous_titre']=strip_tags($prod->description_short["1"]); $row['prix_promotion']= (string)$prod->getprice(true,null,2); $row['prix_orig']= tools::ps_round((string)$prod->getpricewithoutreduct(), 2); foreach ($row $key => $value) { if ($value == null) { $row[$key]=''; } } $row['images_meduim']= $imagesmeduim; $row['images_large']= $imageslarge; $resultat[]=$row; } echo json_encode($resultat);
}
i think have not use 2 table in statement in sql query , use inner join because it's faster
select count(p.`id_product`) totalproducts `'._db_prefix_.'product` p inner join `'._db_prefix_.'product_shop` ps on p.`id_product` = ps.`id_product` p.`active` = 1 , ps.`id_shop` = '.$cntxt->shop->id);
and don't understand why use
$order_out_of_stock = configuration::get('ps_order_out_of_stock','','',$cntxt->shop->id); $stock_management = configuration::get('ps_stock_management','','',$cntxt->shop->id);
in foreach, time same maybe sould out of foreach ?
----edit----
so maybe can other thing don't use foreach promo
like
if(isset($promo[$p['id_product']]) { $rowprod = array('i.....
instead of
if (count(promos)>0) { foreach($promos $promo) { if ($promo['id_product'] == $p['id_product']) {
but if lot of data , action i'm not sure server velocity change anythink .
Comments
Post a Comment