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

Popular posts from this blog

aws api gateway - SerializationException in posting new Records via Dynamodb Proxy Service in API -

asp.net - Problems sending emails from forum -