MySQL计数和php尝试优化效率和无冗余

Question

我的查询运行正常。但我正在努力寻找优化的最佳方法，而不必重复我的$sqlRecCount和$records_count (我想知道是否有可能不需要重复GET)。这就是我现在所拥有的：

if ((int)$_GET['products_id'] === 13) {
    $sqlRecCount = "select count(*) as recTotal from table_sql_1";
    $recCnt = $db->Execute($sqlRecCount);
    $records_count = $recCnt->fields['recTotal'];
}
elseif ((int)$_GET['products_id'] === 2) { 
    $sqlRecCount = "select count(*) as recTotal from table_sql_2";
$recCnt = $db->Execute($sqlRecCount);
    $records_count = $recCnt->fields['recTotal'];
} else {
    $records_count = "Updating...";
}

Answer 1

$id = intval($_GET['products_id']);

if ($id == 13 || $id == 2) {
    $sqlRecCount = "select count(*) as recTotal from table_sql_" . 
                                                       ($id==13?'1':'2');
    $recCnt = $db->Execute($sqlRecCount);
    $records_count = $recCnt->fields['recTotal'];
} else {
    $records_count = "Updating...";
}

ps:如果您有一组与product_id没有直接对应关系的表，您可以将代码片段重写为

$id = intval($_GET['products_id']); // casting to int is not required here
$tables = array('13'=>'1', '2'=>'2', and so on);  

if (isset($tables[$id])) {
    $sqlRecCount = "select count(*) as recTotal from table_sql_" . $tables[$id];
    $recCnt = $db->Execute($sqlRecCount);
    $records_count = $recCnt->fields['recTotal'];
} else {
    $records_count = "Updating...";
}

ps：@downvoter -有什么评论吗？

Answer 2

显然应该以正确的方式进行

if ($id = (int)$_GET['products_id']) {
    $sql = "SELECT count(*) as total FROM table_sql WHERE products_id=$id";
    $res = $db->Execute($sql);
    $records_count = $res->fields['total'];
}

，或者根据您的db API语法来选择类似的内容。