我想尽可能减少sql查询，以节省服务器上的一些负载。提交表单时，我有这个 mysql 查询，
$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);
}
}
有没有办法最小化这些sql查询？或者我的代码可以吗？
一般来说，这段代码有点难以理解。评论在哪里？您似乎正在执行涉及多个表的多个操作，但具体是什么并不明显。
一些想法：
您在 $insert_rp_ids1 上循环两次 - 您可以合并这些循环吗？最好有不同的插入、更新和删除部分，并清楚地标有 IMO 的评论。
仅当删除成功时，才插入。为什么不以任何一种方式插入？
此外，您的一些变量名称有点不透明。$rps_each2 和 $insert_rp_ids1 到底是什么？像$listOfProductIDsToInsert这样的名称更具描述性，因此将来更易于维护，尤其是在其他人必须处理此代码的情况下。
你的缩进有点好笑。假设这不是复制/粘贴错误，这对于使代码可读很重要，因此需要修复。
除此之外，您的SQL似乎非常紧凑和高效。