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

Popular posts from this blog

asynchronous - C# WinSCP .NET assembly: How to upload multiple files asynchronously -

i using winscp .net assembly uploading files. want upload multiple files asynchronous way. have created method works single upload. public class uploadingdata { private sessionoptions _sessionoptions; private session _session; //connection etc private void onconnected() { foreach (var fileinfo in localfileslist) { var task = task.factory.startnew(() => uploadfilesasync(fileinfo)); } } private async task uploadfilesasync(string file) { string remotefilepath = _session.translatelocalpathtoremote(file, @"/", "/test_data_upload"); var uploading = _session.putfiles(file, remotefilepath, false); //when done await task.run(() => thread.sleep(1000)); } } please suggest me correct way. thanks you can use backgroundworker class this: string[] images = new string[50]; foreach (var path in images) { backgroundworker
Read more

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

i getting "__type": "com.amazon.coral.service#serializationexception" as reply in postman & in test console in api gateway trying post record directly dynamodb using api proxy services.. referring aws article - https://aws.amazon.com/blogs/compute/using-amazon-api-gateway-as-a-proxy-for-dynamodb/ here's mapping { "tablename": "tablenamegoeshere", "item": { "id" : "$context.requestid" "eventname" : "$input.path('$.eventname')", "timestamp" : $input.path('$.timestamp'), "answers": "$util.parsejson($input.path('$.answers'))" } } update: did asked ... , worked if try add array of json objects gives me above same error - here's trying now. please - couldnt find on google well #set($inputroot = $input.path('$')) { "tablename": "answer", "item": {
Read more

asp.net - Problems sending emails from forum -

i have been redesigning site has message board, has been written guy looked after site. it has few problems , written in .asp , uses .mdb files database. you can see whole thing here . feel free post if helps, test thing have set using original files , new database. the basic problem when posts message, supposed email sent them, have posted it. when replies, originator supposed message, replier has been posted. i’d have thought @ point in process there should script/file in postmessage.asp? the postmessage.asp page below. <% option explicit %> <!-- #include virtual="common/adovbs.inc" --> <html> <head> <meta http-equiv="content-type" content="text/html; charset=windows-1252"> <meta name="generator" content="microsoft frontpage 4.0"> <meta name="progid" content="frontpage.editor.document"> <title>new page 2</title> <base target="_self"
Read more