How to sort data already fetched from database with jquery/ajax?

Question

Below is my query which fetches data with filters:

$statement = $pdo->prepare("SELECT * FROM posts WHERE subid IN (:key2) AND Poscode=:postcode2 AND pricing=:rate2");
    $statement->execute(array(':key2' => $key2,':postcode2'=>$postcode,':rate2'=>$rate));
 // $row = $statement->fetchAll(PDO::FETCH_ASSOC);
  $json = array();
 while( $row = $statement->fetch()) {

     array_push($json, array("name" => $row['Name'], "id" => $row['PostUUID'],"rate" => $row['pricing'],"reputation" => $row['Reputation'],"plus" => $row['ReviewPlus'],"neg" => $row['ReviewNeg'],"weekM" => $row['week_morning'],"weekA" => $row['week_afternoon'],"weekE" => $row['week_evening'],"endM" => $row['weekend_morning'],"endA" => $row['weekend_afternoon'],"endE" => $row['weekend_evening']));
 }

    header('Content-Type: application/json');

    echo json_encode($json);

This is my ajax

$("form").on("submit", function () {
    var data = {
        "action": "test"
    };

    data = $(this).serialize() + "&" + $.param(data);
    $.ajax({
        type: "POST",
        dataType: "json",
        url: "ajax2.php", //Relative or absolute path to response.php file
        data: data,
        success: function (data) {

            $("#main_content").slideUp("normal",function(){

            //$(".the-return").html("<br />JSON: " + data+"<br/>");
            for (i = 0; i < data.length; i++) {

$(".the-return").append("<div class='inside_return'>Name:" + data[i].name + "<br/>Id:" + data[i].id + "Pricing:" + data[i].rate + "<br/>Reputation:" + data[i].reputation+"<br/>Review Plus:" + data[i].plus+"<br/>Review Negative:" + data[i].neg+"<br/><h1>Availability</h1>Week Morning:" + data[i].weekM+"<br/>Week Afternoon:" + data[i].weekA+"<br/>Week Evening:" + data[i].weekE+"<br/>Weekend Morning:" + data[i].endM+"<br/>Weekend Afternoon:" + data[i].endA+"<br/>Week Evening:" + data[i].endE+"</div>");
            //alert(data[i].name) 
        }



            });
        }
    });
    return false;


});

Now I already have data displaying in a page with specified filters,meaning he result must be tally with the subid, poscode and rate collected from user input.

I'm giving the user to sort the already fetched data by review, rank and so on...It must be swift(ajax). But hw do I go about it? Can anyone give an idea please.

The result are not in table row forms but they are displayed in div block per record.

How can I use jquery to achieve this, say if user clicked on 'sort by rank'.

I have some idea: Instead of firing a new query to the db each user sort, how can I loop trough the array of data received in json above and just sort from there?

$('#rank').on("click", function(){ //how to sort here});

EDITED PART:

referring to this link:Sorting an array of JavaScript objects

I'm trying something like:

var sort_by = function(field, reverse, primer){

   var key = primer ? 
       function(x) {return primer(x[field])} : 
       function(x) {return x[field]};

   reverse = !reverse ? 1 : -1;

   return function (a, b) {
       return a = key(a), b = key(b), reverse * ((a > b) - (b > a));
     } 
}

// Sort by price high to low
data.sort(sort_by('rate', true, parseInt));

// Sort by city, case-insensitive, A-Z
data.sort(sort_by('Name', false, function(a){return a.toUpperCase()}));

But it's not working, can anyone help to??

Check out this link on how to sort json values. But this will get realy complecated when you bring in all the conditions... [sorting ajax json elemnts](http://gabrieleromanato.name/jquery-sorting-json-objects-in-ajax/) — Harigovind R, May 07 '15 at 08:26
@HarigovindR, thanks for the link, I'm trying something like this:homes.sort(function(a,b) { return parseFloat(a.price) - parseFloat(b.price) } );..........but not working of course; just hsaring with you if you know how o sort this way throughout the result.. — sherly, May 07 '15 at 08:41
Would it be at all possible for you to pass along with the ajax query the sorting details? Like {field: id, order:DESC} ? It would really ease the process for you. — Reasurria, May 07 '15 at 09:21
@Reasurria , you mean in the query like this? $statement = $pdo->prepare("SELECT * FROM posts WHERE subid IN (:key2) AND Poscode=:postcode2 AND pricing=:rate2 ORDER BY Poscode DESC LIMIT 60"); — sherly, May 07 '15 at 09:25
@sherly Yes. Then you can build the query dynamically depending on the sorting details you sent to the script. So "Poscode" will be whatever you passed through. EDIT: of course this is out of the question of your data is going to be huge. — Reasurria, May 07 '15 at 09:27
@Reasurria, I thought of it but it might be troublesome as I want to sort by multiple type namely, rank, rate,review, gender and so forth...SO each time I have to fetch a different query data or is there any other effective way? — sherly, May 07 '15 at 09:32
@sherly No I think you are right not to take this route then. It will become troublesome to get data you already have every time. I'll see if I can think of something to help you with the sorting. — Reasurria, May 07 '15 at 09:36

Reasurria · Answer 1 · 2015-05-07T10:14:31.803

This solution tries to use the built in sort.

// I assume in the code that there are 2 properties "name" and "rating".

// a and b are of the same type as the data
function ratingComparer(a, b)
{
   if(parseInt(a.rating) < parseInt(b.rating))
     return -1;
   if(parseInt(a.rating) > parseInt(b.rating))
     return 1;

   return 0;
}

function nameComparer(a,b)
{
    // Same thing as with rating except we do string comparisons with a.name
    // and b.name
}

//Sort by rating
data.sort(ratingComparer);

// Sort by name
data.sort(nameComparer);

See this https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Array/sort link. The section right above the Example section is especially relevant to your situation I think.

EDIT: I think the problem with your implementation is that the comparison function expects 2 parameters of the type to be sorted. You'll probably have to use a switch or something to choose the correct comparison function instead of having a dynamic one like you have tried to use.

How to sort data already fetched from database with jquery/ajax?

1 Answers1