一种最小化我的SQL查询的方法

Question

I wanted to minimize sql queries as possible in order to save some load on my server. I have this mysql queries when a form is submitted,

$updateglobal_products_data = array(
                        'products_type' => tep_db_prepare_input($HTTP_POST_VARS['products_type']),
                        'global_category_id' => tep_db_prepare_input($HTTP_POST_VARS['global_categories']),
                        'display_product' => tep_db_prepare_input($HTTP_POST_VARS['display_product']),
                        'products_description' => tep_db_prepare_input(strip_tags($HTTP_POST_VARS['products_description'][1])),
                        'products_name' => tep_db_prepare_input(stripslashes($HTTP_POST_VARS['products_name'][1])),
                        'products_image' => $products_image->filename,
                        'products_last_modified' => tep_db_prepare_input($gcurrent_datetime),
                        'products_quantity' => (int)tep_db_prepare_input($HTTP_POST_VARS['products_quantity']),
                        'products_model' => tep_db_prepare_input($HTTP_POST_VARS['products_model']),
                        'products_price' => $products_price,
                        'products_date_available' => $products_date_available,
                        'products_weight' => (float)tep_db_prepare_input($HTTP_POST_VARS['products_weight']),
                        'products_status' => tep_db_prepare_input($HTTP_POST_VARS['products_status']),
                        'products_tax_class_id' => tep_db_prepare_input($HTTP_POST_VARS['products_tax_class_id']),
                        'manufacturers_id' => (int)tep_db_prepare_input($HTTP_POST_VARS['manufacturers_id']));

$sql_data_array = array('products_quantity' => (int)tep_db_prepare_input($HTTP_POST_VARS['products_quantity']),
                        'products_type' => tep_db_prepare_input($HTTP_POST_VARS['products_type']),
                        'img_display' => tep_db_prepare_input($HTTP_POST_VARS['image_display']),
                        'products_model' => tep_db_prepare_input($HTTP_POST_VARS['products_model']),
                        'products_price' => tep_db_prepare_input($HTTP_POST_VARS['products_price']),
                        'products_date_available' => $products_date_available,
                        'products_weight' => (float)tep_db_prepare_input($HTTP_POST_VARS['products_weight']),
                        'products_status' => tep_db_prepare_input($HTTP_POST_VARS['products_status']),
                        'products_tax_class_id' => tep_db_prepare_input($HTTP_POST_VARS['products_tax_class_id']),
                        'manufacturers_id' => (int)tep_db_prepare_input($HTTP_POST_VARS['manufacturers_id']));

$update_sql_data = array('products_last_modified' => 'now()');
$sql_data_array = array_merge($sql_data_array, $update_sql_data);

  $wpdb->update( TABLE_PRODUCTS, $sql_data_array, array( 'products_id' => $products_id ));
  $wpdb->update( TABLE_GLOBAL_PRODUCTS, $updateglobal_products_data, array( 'blog_id' => $blog_id, 'products_id' => $products_id ));

  $delete_rps = "DELETE from " . TABLE_RELATED_PRODUCTS . " where products_id = '" . $products_id . "'";
  $wpdb->query("DELETE from " . TABLE_RELATED_PRODUCTS . " where products_id = '" . $products_id . "'");

  if(mysql_query($delete_rps)) {
    foreach($insert_rp_ids1 as $id){ 
    $rps_each2 = array('products_id' => $products_id, 'related_products_ids' => $id);
    $wpdb->insert(TABLE_RELATED_PRODUCTS, $rps_each2);
    }
  }

$wpdb->query("DELETE from " . TABLE_RELATED_PRODUCTS . " where related_products_ids = '" . $products_id . "'");

foreach($insert_rp_ids1 as $rp_ids)
 {

$result_rp = mysql_query("SELECT related_products_ids, products_id FROM ".TABLE_RELATED_PRODUCTS." where products_id = '" . $rp_ids ."'");
 if(!mysql_num_rows($result_rp))
   {
     $rps_each2 = array('products_id' => $rp_ids, 'related_products_ids' => $products_id);
     $wpdb->insert(TABLE_RELATED_PRODUCTS, $rps_each2);

   }
 }

Is there a way to minimize these sql queries? or is my code okay?

Answer 1

Generally speaking, this code is a little hard to understand. Where are the comments? It seems you are doing several operations involving more than one table, but it's not obvious exactly what.

Some thoughts:

You are looping twice over $insert_rp_ids1 - can you merge these loops? Would be better to have distinct INSERT, UPDATE and DELETE sections, clearly labelled with comments IMO.

You are inserting only if delete is successful. Why not insert either way?

Additionally, some of your variable names are a bit opaque. What exactly are $rps_each2 and $insert_rp_ids1 anyway? Names like $listOfProductIDsToInsert are more descriptive and therefore more maintainable for the future, especially if someone else has to deal with this code.

Your indenting is a bit funny. Assuming this is not a copy/paste error, this matters in making the code readable, so it needs fixing.

Other than that, your SQL seems quite compact and efficient.