My SQL query updates all the stocks in database, but it is not working very efficiently and sometimes I get 504 timeout errors. The code works fine. How can I make it work better.
P.S: Please ignore lack of prepared statements, I'll add them later.
Some information about tables (Wordpress Woocommerce Plugin default tables):
wp_posts: This table includes posts. (Posts can be a product, or product variation. Example a product is butterfly t-shirt, a product variation is butterfly t-shirt red large).
wp_postmeta: This table includes meta information about posts. For example, if a product variation is instock, or what color it is, or what size it is.
//This array gives, which products are there, and their respective categories.
$allProducts = array("Fermuarlı Kapşonlu Sweatshirt" => "'2653','2659'","Kapşonlu Sweatshirt" => "'2646','2651'","Sweatshirt" => "'2644','2650'","Kadın Tişört" => "'2654','2656'","Atlet" => "'2655','2657'","Tişört" => "'2643','2304'");
//Below arrays gives information about, which product variations are out of stock.
$tisort_OutOfStock =array();
$atlet_OutOfStock =array("all_colors"=>"'3xl','4xl','5xl'");
$kadin_tisort_OutOfStock =array("all_colors"=>"'xxl','3xl','4xl','5xl'");
$sweatshirt_OutOfStock =array("beyaz"=>"'xxl','3xl','4xl','5xl'","kirmizi"=>"'xxl','3xl','4xl','5xl'","bordo"=>"'5xl'","antrasit"=>"'5xl'");
$kapsonlu_sweatshirt_OutOfStock =array("gri-kircilli"=>"'5xl'");
$fermuarli_kapsonlu_sweatshirt_OutOfStock =array("gri-kircilli"=>"'5xl'","siyah"=>"'5xl'");
//Reset stocks before updating.
$resetStocks = "UPDATE wp_postmeta set meta_value = 'instock' where meta_key = '_stock_status'";
$wpdb->query($resetStocks);
echo "Stoklar are reseted<br>";
//Foreach product, foreach color, update if product doesn't have stock.
foreach( $allProducts as $key => $urun ){
switch ($key) {
case "Kadın Tişört": $tempArray = $kadin_tisort_OutOfStock; break;
case "Fermuarlı Kapşonlu Sweatshirt": $tempArray = $fermuarli_kapsonlu_sweatshirt_OutOfStock; break;
case "Kapşonlu Sweatshirt": $tempArray = $kapsonlu_sweatshirt_OutOfStock; break;
case "Sweatshirt": $tempArray = $sweatshirt_OutOfStock; break;
case "Atlet": $tempArray = $atlet_OutOfStock; break;
case "Tişört": $tempArray = $tisort_OutOfStock; break;
}
foreach( $tempArray as $color => $size ){
$query = "UPDATE wp_postmeta set meta_value = 'outofstock' where meta_key = '_stock_status' and post_id in
(
select post_id from (select * from wp_postmeta) AS X where meta_key = 'attribute_pa_beden' and meta_value in (".$size.")
and post_id in (select post_id from (select * from wp_postmeta) AS Y where meta_key = 'attribute_pa_renk' and ((meta_value = '".$color."') OR ('".$color."' = 'all_colors')))
and post_id in (select id from wp_posts where post_type = 'product_variation' and post_parent in (select object_id FROM wp_term_relationships where term_taxonomy_id in (".$urun.")))
)";
global $wpdb;
$updatedRowCount = $wpdb->query($query);
}
}