Order by date / price

Question

I want to show database records ordered by Datetime or Price when users click on links sort by Datetime or sort by Price. Should I add a condition into the query ?

 $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

You already have this clause:

$sql .= " ORDER BY Datetime DESC";

All you need, is to add ordering parameter to your query, say $_GET['order'] as integer index(1-based) in your whitelist orders where sign points to order direction.

$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}";

Answer 2

In simple way you can use if else condition for sort query only. According to your condition like this.

$sql = "Rest of the Query..";
if($_GET['order'] == 'price') {
   $sql .= " ORDER BY Price DESC";
} else {
   $sql .= " ORDER BY Datetime DESC";
}

When user click Sort by Price or Datetime then query will executed as per the condition.

(Or)

You can update query case condition like below example.

ORDER BY 
CASE WHEN "price" = $_GET['order'] THEN price END DESC,
CASE WHEN "datetime" = $_GET['order'] THEN datetime END DESC

(or)

ORDER BY 
CASE $_GET['order'] 
WHEN "price" THEN price END DESC,
WHEN "datetime" THEN datetime END DESC

etc...

Whatever you choose booth will be same performance and efficiency.