php - 按日期/价格订购

Question

当用户单击按日期时间排序或按价格排序的链接时，我想显示按日期时间或价格排序的数据库记录。我应该在查询中添加条件吗？

 $sql = "SELECT p.title AS Title, p.date_published) AS Datetime , p.price   AS Price, c.name AS Category FROM products p

           INNER JOIN categories c
              ON c.id = p.category
           INNER JOIN brands b
              ON b.id = p.brand
              .
              .
              .
    ";

    if (isset($_GET['search'])){

          $locations = array();
          $getters = array();
          $queries = array();

          foreach($_GET as $key => $value) {
          .
          .
          .
          }

          if (!empty($brands)) {
            $brd_q = implode(",",$brands);
          }

          if(!empty($getters)) {

            foreach($getters as $key => $value){
              ${$key} = $value;
              switch($key) {
                case 'search':
                array_push($queries, "(p.title LIKE '%$search%' || p.description LIKE '%$search%' || p.number LIKE '%$search%')");
                break;
                case 'scategory':
                array_push($queries, "p.category = $scategory");
                break;
                case 'sbrands':
                array_push($queries, "p_brd.brand_id IN ($brd_q)");
                break;
                .
                .
                .
              }
           }
        }
         
         if(!empty($queries)) {
             $sql .= " WHERE ";
             $i=1;
             foreach($queries as $query) {
                 if ($i < count($queries)) {
                     $sql .= $query." AND ";
                 }else{
                     $sql .= $query;
                 }
                 $i++;
             }

         }
        
          $sql .= " ORDER BY Datetime DESC";
      } 

Answer 1

你已经有了这个条款:

$sql .= " ORDER BY Datetime DESC";

您所需要的只是将排序参数添加到您的查询中，例如将 $_GET['order'] 作为白名单订单中的整数索引(从 1 开始)，其中符号指向订单方向。

$orders = ['Datetime', 'Price'];

if (empty($_GET['order'])) $_GET['order'] = -1; // set default order

$index = abs($_GET['order'])-1;
$ord = isset($orders[$index]) ? $orders[$index] : $orders[0];
$direction = $_GET['order'] > 0 ? 'ASC' : 'DESC';

$sql .= " ORDER BY {$ord} {$direction}";