Unable to use multiple ORDER BY in php using AJAX

Question

Am trying to use order by date and price low to high in this statement

SELECT * FROM allpostdata WHERE sts = '1' AND mca='Vehicle' ORDER BY pdt DESC, prs ASC

i used echo $query; to get the query and here is how i did,

looking for ORDER BY pdt DESC, is default query i want prs to be user option .

when i select order by low to high or high to low the statement changes but query does nothing, its not sorting by price

How do i sort by prs any solution?

pdt means Date and prs means Price

HTML

<div class="dropdown-menu dropdown-menu-right" aria-labelledby="sortby">
     <li class="dropdown-item">
         <div class="md-radio my-1">
              <input type="radio" class="filter_all sort" name="sort" id="asc" value="ASC">
              <label for="asc">Price : Low to High</label>
          </div>
      </li>
      <li class="dropdown-item">
           <div class="md-radio my-1">
                <input type="radio" class="filter_all sort" name="sort" id="desc" value="DESC">
                <label for="desc">Price : High to Low</label>
           </div>
       </li>
    </div>

Script

$(document).ready(function () {
            filter_data();
            function filter_data() {
                $.post(
                        "fetch.php",
                        {
                            action: 'fetch_data',
                            cate: get_filter('cate'),
                            brand: get_filter('brand'),
                            model: get_filter('model'),
                            sort: get_filter('sort'),
                            date: get_filter('date')
                        }
                )
                        .done(function (data) {
                            $('.filter_data').html(data);
                        });
            }
            function get_filter(class_name) {
                var filter = [];
                $('.' + class_name + ':checked').each(function () {
                    filter.push($(this).val());
                });
                return filter;
            }
            $('.filter_all').click(function () {
                filter_data();
            });
        });

PHP

if (isset($_POST["action"])) {
$query = "SELECT * FROM allpostdata WHERE sts = '1' AND mca='Vehicle'";

if (!empty($_POST['cate'])) {
    $query .= " AND sca IN (" . str_repeat("?,", count($_POST['cate']) - 1) . "?)";
} else {
    $_POST['cate'] = []; // in case it is not set 
}

if (!empty($_POST['brand'])) {
    $query .= " AND product_brand IN (" . str_repeat("?,", count($_POST['brand']) - 1) . "?)";
} else {
    $_POST['brand'] = []; // in case it is not set 
}

if (!empty($_POST['model'])) {
    $query .= " AND mdl IN (" . str_repeat("?,", count($_POST['model']) - 1) . "?)";
} else {
    $_POST['model'] = []; // in case it is not set 
}
$query .= " ORDER BY pdt DESC";
if (!empty($_POST['sort'])) {
    if ($_POST["sort"][0] == "ASC" || $_POST["sort"][0] == "DESC") { //simplistic whitelist
        $query .= ", prs " . $_POST['sort'][0];
    }
}
echo $query;
$stmt = $conn->prepare($query);
$params = array_merge($_POST['cate'], $_POST['brand'], $_POST['model']);
$stmt->execute($params);
$result = $stmt->fetchAll();
$total_row = $stmt->rowCount();
$output = '';

What is pdt? You realize that the query as you have it will sort on that field first, and prs will only be a tie-breaker between records where pdt is the same? — Greg Schmidt
– Greg Schmidt, Commented Dec 19, 2019 at 4:36
So, what I expect should be happening here is that you'll have things sorted by date, and things with the same date are then sorted by price. Does that match what you're seeing? — Greg Schmidt
– Greg Schmidt, Commented Dec 19, 2019 at 4:42
Well then, you should not be including the "pdt DESC" in every single query you do, only when the price sort isn't requested. — Greg Schmidt
– Greg Schmidt, Commented Dec 19, 2019 at 4:47
Then you need to skip adding ORDER BY pdt DESC when price is triggered. — Barmar
– Barmar, Commented Dec 19, 2019 at 4:47

Barmar · Accepted Answer · 2019-12-19 05:08:11Z

1

Add a third button for "Sort by date".

<div class="dropdown-menu dropdown-menu-right" aria-labelledby="sortby">
  <li class="dropdown-item">
    <div class="md-radio my-1">
      <input type="radio" class="filter_all sort" name="sort" id="asc" value="ASC">
      <label for="asc">Price : Low to High</label>
    </div>
  </li>
  <li class="dropdown-item">
    <div class="md-radio my-1">
      <input type="radio" class="filter_all sort" name="sort" id="desc" value="DESC">
      <label for="desc">Price : High to Low</label>
    </div>
  </li>
  <li class="dropdown-item">
    <div class="md-radio my-1">
      <input type="radio" class="filter_all sort" name="sort" id="date" value="date">
      <label for="date">Date : High to Low</label>
    </div>
  </li>
</div>

if (empty($_POST['sort']) || $_POST['sort'][0] == "date") {
    $query .= " ORDER BY pdt DESC";
} elseif ($_POST["sort"][0] == "ASC" || $_POST["sort"][0] == "DESC") {
    $query .= " ORDER BY prs " . $_POST['sort'][0];
}

edited Dec 19, 2019 at 5:08

answered Dec 19, 2019 at 4:48

Barmar

789k57 gold badges554 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Greg Schmidt Over a year ago

This is going to result in no sort order if someone happens to send something other than ASC or DESC for the sort parameter.

Barmar Over a year ago

Garbage in, garbage out. Parameter validation should be done separately and it should just reject that.

sanoj lawrence Over a year ago

it works, After triggering ORDER BY prs how do i trigger ORDER BY pdt DESC, Lie three option order by date, price low, price high.

Barmar Over a year ago

I thought you only wanted ORDER BY prs when price is triggered.

Barmar Over a year ago

Are you asking how to uncheck the radio buttons after you've selected one of the sort orders? You can't. You could add a third option "Sort by date".

|

Collectives™ on Stack Overflow

Unable to use multiple ORDER BY in php using AJAX

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related