代码缩写、排序依据和切换

Question

你能帮我缩短代码吗？重点是，我希望根据给定的$T以不同的顺序从数据库中提取数据。有什么想法吗？

public function rewriteQuery($T){

switch ($T) {

    case 0:
        $query="SELECT id, title FROM kshome WHERE del=0 AND active=1 ORDER BY FIELD(id,1,2,3,4,5,6)";  
        break;
     case 1:
        $query="SELECT id, title FROM kshome WHERE del=0 AND active=1 ORDER BY FIELD(id,2,3,4,5,6,1)";  
        break;
     case 2:
        $query="SELECT id, title FROM kshome WHERE del=0 AND active=1 ORDER BY FIELD(id,3,4,5,6,1,2)";    
        break;
     case 3:
       $query="SELECT id, title FROM kshome WHERE del=0 AND active=1 ORDER BY FIELD(id,4,5,6,1,2,3)";  
        break;
     case 4:
      $query="SELECT id, title FROM kshome WHERE del=0 AND active=1 ORDER BY FIELD(id,5,6,1,2,3,4)";  
        break;
    default:
       $query="SELECT id, title FROM kshome WHERE del=0 AND active=1 ORDER BY FIELD(id,6,1,2,3,4,5)";  
    break;

    }        

  return $query;             

} 

Answer 1

缩短这段时间的一种方法是创建一个返回order by字段的数组。

如下所示：

$order_by_array = array(
  0 => "(id,1,2,3,4,5,6)", 
  1 => "(id,2,3,4,5,6,1)", 
  2 => "(id,3,4,5,6,1,2)", 
  3 => "(id,4,5,6,1,2,3)", 
  4 => "(id,5,6,1,2,3,4)"
);

然后，您的rewriteQuery($T)函数可以重写为：

public function rewriteQuery($T){
  $order_by = "(id,6,1,2,3,4,5)"; // Default
  if (array_key_exists($T, $order_by_array)) {
    $order_by = $order_by_array[$T];
  }

  $query = "SELECT id, title FROM kshome WHERE del=0 AND active=1 ORDER BY FIELD{$order_by}";
  ...
}

Answer 2

除了消除构造查询字符串中的冗余之外，我看不到更多可以缩短的内容：

public function rewriteQuery($T){

$query = "SELECT id, title FROM kshome WHERE del=0 AND active=1 ORDER BY FIELD";
switch ($T) {
    case 0:
        $query.="(id,1,2,3,4,5,6)";  
        break;
     case 1:
        $query.="(id,2,3,4,5,6,1)";  
        break;
     case 2:
        $query.="(id,3,4,5,6,1,2)";    
        break;
     case 3:
       $query.="(id,4,5,6,1,2,3)";  
        break;
     case 4:
      $query.="(id,5,6,1,2,3,4)";  
        break;
    default:
       $query.="(id,6,1,2,3,4,5)";  
    break;

    }        

  return $query;             

Answer 3

虽然我更喜欢其他答案，以防您的排序是固定的，但这里有一个更灵活的解决方案：

function rewriteQuery($T) {
    $query = "SELECT id, title FROM kshome WHERE del=0 AND active=1 ORDER BY FIELD(id,";
    $order = array("1", "2", "3", "4", "5", "6");

    if($T < count($order)) {
        for($i = $T, $end = false;;$i++) {
            if($i == count($order)) {
                $i = 0;
                $end = true;
            }

            if($end && $i == $T) {
                break;
            }

            $query .= $order[$i] . ",";
        }

        $query = substr($query, 0, -1) . ")";

        return $query;
    } else {
        return "ERROR";
    }
}

$index = 5;

echo rewriteQuery($index);

请参阅PHPfiddle。

基本上，你必须意识到你的$T只不过是你想要开始你的订单的索引(给定你的订单是一个数组)。一旦你意识到这一点，你就可以实现以下逻辑：

以$T为起点，我们遍历数组，直到到达终点，然后将$i设置为零，并激活一个结束条件以不运行无限循环。$i将继续增加，直到达到$T，然后退出循环。剩下的就是去掉后面的,了。我只需将其替换为)以完成查询。