Unable to sort data by price in PHP an AJAX

Question

Am trying to sort data from sql based on price Low to High and high to Low so i tried with below code and it doesn't sort by ASC or DESC

i get Syntax error or access violation basically i tried every solution, and couldn't sort data by price.

HTML

<li class="dropdown-item">
 <input type="radio" class="filter_all sort" name="sort" id="new" value="new">
 <label for="new">Date Published</label>                                                        
</li>
<li class="dropdown-item">
 <input type="radio" class="filter_all sort" name="sort" id="asc" value="ASC">
 <label for="asc">Price : Low to High</label>
</li>
<li class="dropdown-item">
  <input type="radio" class="filter_all sort" name="sort" id="desc" value="DESC">
  <label for="desc">Price : High to Low</label>
</li>

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')
                        }
                )
                        .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'";


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 
}

if (!empty($_POST['sort'])) {
    $query .= " GROUP BY prs ORDER BY prs (" . str_repeat("?,", count($_POST['sort']) - 1) . "?)";
} else {
    $_POST['sort'] = []; // in case it is not set 
}

$stmt = $conn->prepare($query);
$params = array_merge($_POST['cate'], $_POST['brand'], $_POST['model'], $_POST['sort']);
$stmt->execute($params);
$result = $stmt->fetchAll();
$total_row = $stmt->rowCount();
$output = '';

Can some one help me how do i sort data based on price.

https://stackoverflow.com/questions/2542410/how-do-i-set-order-by-params-using-prepared-pdo-statement — 04FS, Nov 25 '19 at 15:04
you can't parameterise the ORDER BY clause. Just whitelist the allowed values (for validation) and then include the requested value by string concatenation — ADyson, Nov 25 '19 at 15:06
you're asking me how to join two strings together to make a bigger string? — ADyson, Nov 25 '19 at 15:16
@ADyson am no good in `php`, if i have a small example i could make it — sanoj lawrence, Nov 25 '19 at 15:20
@sanojlawrence `SELECT * FROM allpostdata WHERE sts = '1' ORDER BY price ASC` or `SELECT * FROM allpostdata WHERE sts = '1' ORDER BY price DESC` . Also, take a look at here https://www.w3schools.com/sql/sql_orderby.asp — Alcaeus D, Nov 25 '19 at 15:23
See answer below. Like I said, it's literally just joining two strings together...which you already know how to do because you already are doing it in lots of places! — ADyson, Nov 25 '19 at 15:33

ADyson · Accepted Answer · 2019-11-27T13:49:32.993

1

You can't parameterise items in the the ORDER BY clause.

To resolve this safely, just have a simple whitelist of the allowed values (for validation) and then include the requested value using regular string concatenation:

So, if the $_POST["sort"] variable is an array with always exactly one item in it, and that item will contain either ASC or DESC then it would be something like this:

if (!empty($_POST['sort'])) { 
  if ($_POST["sort"][0] == "ASC" || $_POST["sort"][0] == "DESC") { //simplistic whitelist
    $query .= " GROUP BY prs ORDER BY prs " . $_POST['sort'][0]; 
  } 
}

(N.B. you also had some incorrect brackets there in the SQL).

And then change the parameter list to exclude the "sort" value:

$params = array_merge($_POST['cate'], $_POST['brand'], $_POST['model']);

edited Nov 27 '19 at 13:49

answered Nov 25 '19 at 15:36

ADyson

57,178
14
51
63

i tried there are no changes its not sorting according to price. – sanoj lawrence Nov 25 '19 at 15:40
ok...and have you tried to debug why that might be happening? Log (or echo, if you prefer) what the value of $_POST["sort"] is when this script runs. And also check the final value of $query, too, so you can see exactly what query is being executed. Maybe add some logging so you can see whether the code is entering the `if` blocks or not...this is all basic debugging work you can and should be doing in order to investigate a problem of this nature. Start doing that, and suddenly you'll find it's a lot quicker and easier to find the root causes of problems – ADyson Nov 25 '19 at 15:43
when i echo `$_POST['sort']` i get `Array to string conversion` error – sanoj lawrence Nov 25 '19 at 15:45
Right. So you are submitting multiple values for "sort"? Or is it really only ever one value, but you decided to put it into an array for some reason? That is the fault of your JavaScript code, if that's what is happening. I can't see any need to submit an array there, unless there is something I don't know about your page? – ADyson Nov 25 '19 at 15:46
am passing **only one value** what is the solution,is there any other method? – sanoj lawrence Nov 25 '19 at 15:50
Is there ever any possibility that you would send more than one value for "sort" from your page? If not then the obvious solution is to change your JavaScript so it doesn't submit arrays without needing to. Don't make the code more complicated than it needs to be! – ADyson Nov 25 '19 at 15:51
But if you don't want to change the JavaScript for some reason, then you can easily work round it by just always selecting the first item in the array, e.g. `$query .= " GROUP BY prs ORDER BY prs " . $_POST['sort'][0];` – ADyson Nov 25 '19 at 15:52
please have a look at my code https://pastebin.com/94exzH4s and fetch file https://pastebin.com/M5xqnPMP – sanoj lawrence Nov 25 '19 at 15:57
what am I looking for specifically? That is a very big piece of code. – ADyson Nov 25 '19 at 16:27
then u can find on question. can i do it by changing code..? if so how can i do ? – sanoj lawrence Nov 25 '19 at 16:29
Sorry I don't understand exactly what you're asking me to look at in your code. I've already suggested a way to get your application working. If that isn't helping you, you need to give a more specific explanation of what your current problem is. – ADyson Nov 27 '19 at 10:56
i tried replacing your code but it doesn't work, not sorting according to price. Is there any other way to sort by price – sanoj lawrence Nov 27 '19 at 12:48
have you done any of the basic debugging I suggested? at the very least, do `echo $query;` just before you do `$stmt->execute`, so that you can see what the real SQL query is that you're going to run. Check to ensure it includes the ORDER BY clause in the SQL. If it doesn't, then do `var_dump($_POST);` to see what values you are actually receiving in the PHP. – ADyson Nov 27 '19 at 13:11
P.S. it would make sense to also run the same query direct in your SQL database, to ensure it actually produces the results you are expecting. That way we can ensure the problem is not related to the data or something else outside the PHP or JS code – ADyson Nov 27 '19 at 13:15
Yes i did when i do `echo $query` this is what i get `SELECT * FROM allpostdata WHERE sts = '1'` even after selecting sort option. – sanoj lawrence Nov 27 '19 at 13:16
sort query is not triggred – sanoj lawrence Nov 27 '19 at 13:17
Ok. So move onto outputting the POST values as I suggested also. – ADyson Nov 27 '19 at 13:17
`vardump($_POST)` values `array(5) { ["action"]=> string(10) "fetch_data" ["sort"]=> array(1) { [0]=> string(3) "ASC" } ["cate"]=> array(0) { } ["brand"]=> array(0) { } ["model"]=> array(0) { } }` – sanoj lawrence Nov 27 '19 at 13:23
Ah of course, I realised, it's the whitelist `if` which needs updating as well to read from the first item in the array. My mistake (although you perhaps could have realised too, when you see what that statement is doing). See the updated answer, above, for new version of the code. – ADyson Nov 27 '19 at 13:50
i replaced this `$query .= " GROUP BY prs ORDER BY prs " . $_POST['sort'][0];`with this and it works now `$query .= " ORDER BY prs " . $_POST['sort'][0]` – sanoj lawrence Nov 27 '19 at 13:54
1

Ok good. I did wonder why you needed the GROUP BY...but I couldn't see your database so I didn't want to comment on that – ADyson Nov 27 '19 at 13:55

Unable to sort data by price in PHP an AJAX

1 Answers1