Complex pagination with AJAX POST and $.getJSON

Question

I have multiple checkboxes group whose value, if checked, is passed to a php page in an object with jquery, i get the results all good. Now I want to paginate the results. I am not sure how to go about this. As using pagination is not returning any value passed by AJAXPOST. My JS :

$(":checkbox").on('change', function() {
    var mygroup = {};
    $(':checkbox:checked').each(function(i) {
        var val = this.value;
        var name = this.name;
        mygroup[name] = (mygroup[name] || []).concat([val]);
    });

    var itemsPerPage = 5;

    if(typeof url == 'undefined' ){
    url = "finalprocess1.php?page=1&items_per_page="+itemsPerPage;
}
     $.ajax({ 
        type: "POST", 
        url: 'finalprocess.php',
        data: mygroup,
        success: function(data) {

        $.getJSON(url, function(data){       //NOT SURE IF I CAN DO THIS
        var result = userTemplate({users : data.users})  //UNDERSCORE TEMPLATING
        $("#theresult").html(result);
            });
        }
    });

});

you already have one response data in the success callback of POT method, you should use that — Deep, Jul 23 '16 at 16:42
you can either create your own pagination logic by slicing this list or you can use the already available jquery plugins for pagination. Read here https://plugins.jquery.com/tag/pagination/ — Deep, Jul 23 '16 at 16:45
You said to use the first response data generated from php. Do i do the pagination in the php page itself? — Somename, Jul 23 '16 at 16:55
@Deep If i do the pagination in the php page, i loose the ajax selection — Somename, Jul 23 '16 at 17:35

score 0 · Answer 1 · answered Jul 23 '16 at 18:19

0

You should add a LIMIT and OFFSET clause to your query on the php side, to take certain ranges according to your page limit: (example using plain sql, php and PDO)

$limit = $_GET['items_per_page'];  //Page limit

//Get request page, if not set to 1
!null == $_GET['page'] ? $page = $_GET['page'] : $page = 1;

//Get total rows count    
$totalRows = $this->db->query('SELECT FOUND_ROWS from items')->fetchColumn();

//Calculate number of pages
$totalPages = ceil($totalRows / $limit);

//Show next or previous button only when needed validation
$hasPrevPage = $page > 1 ? true : false;
$hasNextPage = $page < $totalPages ? true : false;

//Calculate offset
$offset = ($page - 1) * $limit;

$sql = "SELECT * from items
        LIMIT :pageLimit OFFSET :offset"; //The standard pagination clause

$query = $this->db->prepare($sql);
$query->bindValue(':pageLimit', $limit);
$query->bindValue(':offset', $offset);
$query->execute();
return $query->fetchAll();

Then on your view you should create two links, previous and next, mantaining your request URI and modifing the page parameter, +1 to page on next and -1 on previous so you can navigate the results

just as you did here:

url = "finalprocess1.php?page=1&items_per_page="+itemsPerPage;

but modifying the page parameter:

prevUrl = "finalprocess1.php?page=" + (page-1) + "&items_per_page=" + itemsPerPage;
nextUrl = "finalprocess1.php?page=" + (page+1) + "&items_per_page=" + itemsPerPage;

Make sure you pass these validations to your js to show the buttons only when needed, and prevent an offset error.

$hasPrevPage = $page > 1 ? true : false;
$hasNextPage = $page < $totalPages ? true : false;

answered Jul 23 '16 at 18:19

Manuel Azar

853
2
14
24

I dont want to show the php page .. I am calling it from AJAX. When I do this the ajax selection goes away. I am passing the checkbox values as an array in the php page. The values dont stay in the php page when i link the page number to the page. – Somename Jul 23 '16 at 18:25
You are not showing the php page, you just need to return the results range to the ajax request. And you also need to mantain the request URI in the js side to pass them to the $_GET side on php. Also, you should use GET method for search pages, since it only retrieves data, it does not input any. That way you can mantain the link in both sides, js and php – Manuel Azar Jul 23 '16 at 18:30
I am sorry, i am not able to build the pseudo code. :( Where do i put the condition? I am not using prepared statements .. can you please tell me in normal code? many thanks – Somename Jul 23 '16 at 18:45
Are you using a php framework? Please tell me the details of your project so i can generate an example – Manuel Azar Jul 23 '16 at 19:04
Im not using any framework. Just raw php. I have 3-4 group of checkboxes. http://stackoverflow.com/questions/38435588/how-to-pass-array-with-all-elements-with-jquery-to-php/. I am trying since long to paginate the results. I dont mind not using javascript template and doing it in php too. thanks a lot. – Somename Jul 23 '16 at 19:10
maybe this post will help you: http://stackoverflow.com/questions/5004233/jquery-ajax-post-example-with-php. Can you post your php code so we can see what you are doing? – Manuel Azar Jul 23 '16 at 19:40
`$conditionuser = array(); $conditionhouse = array(); if(!empty($_POST['student'])){ $student = $_POST['student']; $conditionuser[] = "'student' = '" . $student."'";} if(!empty($_POST['house'])){ $house = $_POST['house']; $conditions[] = "'house' = " . $house;} $where = "u.id IS NOT NULL "; if(count($conditionuser) > 0){ $where .= " AND (" . implode(" OR ", $conditionuser).")"; } if(count($conditionhouse) > 0){ $where .= " AND (" . implode(" OR ", $conditionhouse).")"; }` i can select and display using this WHERE clause, but how do i paginate the results? – Somename Jul 24 '16 at 05:51
`$sql = "SELECT u.id as userid, u.name as name, c.name as city FROM users u JOIN city uc ON u.id = uc.id_user JOIN city c ON uc.id_city = c.id WHERE $where "; //WHERE IS DEPEDING ON THE ITEMS SLECTED IN THE CHECKBOX.` THis is my SQL and i do `mysqli_fetch_assoc`. I cannot keep the selected values intact in php when i use pagination. thanks – Somename Jul 24 '16 at 05:53

Complex pagination with AJAX POST and $.getJSON

1 Answers1