29 votes

How to display data and advanced paging filters with ajax?

I have a page that works correctly, even the filter to paginate more rows in the same page, that is to say that through the filter, I can show 10 or 50 rows or more.

The small defect that has the code, is that the page reloads, changing the amount of rows that are displayed, and the same thing happens with the buttons of the pagination.

This is my code, everything is working on the same page index2.php

<div id="wrapper">
    <div class="container">
        <div id="news-header" class="bootgrid-header container-fluid">
            <div class="row">
                <div class="col-sm-12 actionBar">
                    <div class="search-bar">
                        <input type="text" id="myInput" onkeyup="myFunction()" placeholder="What are you looking for?">
                    </div>
                    <div class="actions btn-group">
                        <?php
                            $select_quantity = '';
                            if (isset($_POST['amount_show'])) :
                                $select_quantity = $_POST['amount_show'];
                            endif;
                        ?>
                        <form method="post" action="<?php echo htmlspecialchars($_SERVER["PHP_SELF"]);?>">
                            <select id="amount_show" name="amount_show" onchange="this.form.submit()">
                                <option value="10" <?php if ($select_quantity==10) echo "selected"; ?>>10</option>
                                <option value="25" <?php if ($select_quantity==25) echo "selected"; ?>>25</option>
                                <option value="50" <?php if ($select_quantity==50) echo "selected"; ?>>50</option>
                                <option value="100" <?php if ($select_quantity==100) echo "selected"; ?>>100</option>
                            </select>
                        </form>
                    </div>
                </div>
            </div>
        </div>
        <?php
            if (isset($_GET['page'])) :
                $page = $_GET['page'] ?: '';
            else :
                $page = 1;
            endif;

            if (isset($_POST['amount_show'])) :
                $records_by_page = $_POST['amount_show'];
            else :
                $records_by_page = 10;
            endif;

            $localization_sql = ($page-1) * $records_by_page;

            $sql = "SELECT id,name,email
                    FROM users
                    ORDER BY id DESC LIMIT $localization_sql, $records_by_page";
            $stmt = $con->prepare($sql);
            $stmt->execute();
            $stmt->store_result();
            if ($stmt->num_rows>0) :

            echo '<table id="myTable" class="table table-condensed table-hover table-striped bootgrid-table">
            <thead>
                <tr>
                    <th>Id</th>
                    <th>Name</th>
                    <th>Email</th>
                    <th>Action</th>
                </tr>
            </thead>
            <tbody>';

            $stmt->bind_result($id,$name,$email);
            while ($stmt->fetch()) :
                echo '<tr>
                    <td>'.$id.'</td>
                    <td>'.$name.'</td>
                    <td>'.$email.'</td>
                    <td>Edit</td>
                </tr>';
            endwhile;
            echo '</tbody>';
            echo '</table>';
            $stmt->close();

    /**
    *
    * Botones ATRAS / SIGUIENTES
    *
    */

            $sql = "SELECT * FROM users";
            $stmt = $con->prepare($sql);
            $stmt->execute();
            $stmt->store_result();

            $BD_records = $stmt->num_rows;
            $stmt->close();
            $con->close();

            $total_page = ceil($BD_records / $records_by_page);
            $prev = $page - 1;
            $next = $page + 1;
            echo '<div class=pagination>
            <ul class="pagination">';
            if ($prev > 0) :
                echo "<li><a href='index2.php?page=1'><i class='icon-angle-double-arrow'></i></a></li>";
                echo "<li><a href='index2.php?page=$prev'><i class='icon-angle-left'></i></a></li>";
            endif;

            for ($i=1; $i<=$total_page; $i++) :
                if ($page==$i) :
                    echo "<li><a class=active>". $page . "</a></li>";
                else :
                    echo "<li><a href='index2.php?page=$i'>$i</a></li>";
                endif;
            endfor;


            if ($page < $total_page ) :
                echo "<li><a href='index2.php?page=$next'><i class='icon-angle-right'></i></a></li>";
                echo "<li><a href='index2.php?page=$total_page'><i class='icon-angle-double-right'></i></a></li>";
            endif;

            echo '</ul></div>';

            else :
                $stmt->close();
            endif;

        ?>
    </div>
</div>

I have made the following settings due to the recommendations from JSON, to which I have recommended.

ajax.php

if (isset($_GET['page'])) :
                $page = $_GET['page'] ?: '';
            else :
                $page = 1;
            endif;

            if (isset($_POST['amount_show'])) :
                $records_by_page = $_POST['amount_show'];
            else :
                $records_by_page = 10;
            endif;

$sql = "SELECT id,name,email
FROM users
ORDER BY id DESC LIMIT $localization_sql, $records_by_page";
$result = $con->query($sql);
$data_rows = array();
$result = $con->query($sql);
while($row = mysqli_fetch_assoc($result)) {
    $data_rows[] = $row;
}

echo json_encode($data_rows, JSON_PRETTY_PRINT);

I found an ajax code which I have called script.js, by ajax

$(document).ready(function() {  
 $.ajax({
            type: "GET",
            url: "ajax.php",
            dataType: "json",
            success: function(data) {
                tableRows = '';
                for (let i = 0; i < data.length; i++) {
                    tableRows += `
                    <tr>
                        <td>${data[i].id}</td>
                        <td>${data[i].name}</td>
                        <td>${data[i].email}</td>
                        <td>Edit<td>
                    </tr>`;
                }
                $("#tbody-insert").html(tableRows);
            }
    });              
});

I show the data without problem.

<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<script type="text/javascript" src="script.js"></script>
<table id="myTable" class="table table-condensed table-hover table-striped bootgrid-table">
    <thead>
        <tr>
            <th>Id</th>
            <th>Name</th>
            <th>Email</th>
            <th>Action</th>
        </tr>
    </thead>
    <tbody id="tbody-insert">

    </tbody>
</table>

Now my problem is, as you show the buttons of the pagination and the filter to display more row of results.

How do I send this information to the ajax and display the information sent?

I can explain.

5voto

IniDesing.com Points 1441

We have in the browsers different default actions it is important to keep in mind, the articles in English , we can find great information and with performances of real to understand its operation it is also important to get to know more thoroughly the language.

Now to begin making changes should be detected, first of all the possible executions that will reload the page, in this case:

  • The select option -> #amount_show sends a new value for method POST to the pager.
  • And finally the generation of the links in the pager by using labels <a> that tells you the script php what records should be retrieved via the method GET application.

Then, as there is no inconvenience in the Script PHP, all you have to go driving from the code ajax.

Therefore, the code ajax, would be as such:

$(document).ready(function() {
    //Cuando cambiemos el valor del select option
    //mediante su #id obtendremos la información sobre el evento

    $('#amount_show').change(function(evt) {
      // Cancelamos la acción del navegador que tiene por defecto.
      evt.preventDefault()
      // Obtenemos la URL de destino del formulario
      url = $(this).parent().attr('action')
      // Llamamos a la función que hará la solicitud.
      ajaxLoad(url)
    });

    // Siguiendo el mismo modelo del código para toda acción que
    // Deseemos enviar y mostrar al paginador.
    $('.items').on('click', '.pagination li a', function(evt) {
      evt.preventDefault()
      url = $(this).attr('href')
      ajaxLoad(url)
    });

    function ajaxLoad(url) {
      query_params = {
        amount_show: $('#amount_show').val()
      };
      // Mostramos una animación por cada evento que realicemos.
      $('.items').html('<div class="loading">Loading...</div>')
      $.ajax({
        type: "GET",
        url: url,
        data: $.param(query_params),
        //Obtenemos los datos.
        success: function(data) {
          //Insertamos los datos en una etiqueta
          //que tenga la clase .items
          $('.items').fadeOut('1000', function() { $(this).html(data) }).fadeIn('1000')
        }
      });
    }
  });

Recommendations

References

4voto

Niche Points 917

Brief introduction about the use of AJAX

In the first place, means Asynchronous Javascript and XML. In summary, AJAX is to obtain information from the server (Operations server-side or data of the database) that has not been loaded with the site. For example, blocks PHP (delimited by the tags <?php ?>) are part of the code of the site that the server is running, prior to the delivery of the content to the client.

Having explained this, given that the information that you're filtering on the table is information that is already loaded in the page, there is an operation AJAX what you're doing, but you're running a client-side script to show/hide records from the table based on the text entered in the input of the filter.

According to your question, I understand that what happens is that your filtering function only operates on the records that are displayed on the page, and does not show enough logs to fill in the table according to the size of the page. For example, if the page is set to display 50 records, the filter shows only 39 records of the current page.

For the filter to show 50 records (according to the previous example), you must submit a request asynchronous to the server. Let's say, your site should have a URL (for example https://tusitio.com/filtrarUsuarios.php) to make an asynchronous query by sending the parameters by which you are going to filter, so that the PHP returns:

  1. A response in format JSON/XML or any format of your choice with the list of results corresponding to the page; or
  2. The item table is loaded with records corresponding to the page (<table></table>).

Once obtained the response from the server, you must position the elements dynamically in the table. If it is option 1, you must use the function document.createElement() to create HTML elements (tr and td) to place them on your table, and if it is option 2, replacing the HTML of the table using innerHTML. My suggestion is to use the option 1, since that is the standard for consumption of APIs and web services.

Since the way that you used in your example makes a POST to the same page where you load the logs, I understand that the filter does not work asynchronously. That is to say, the page reloads with the filtered records once you do the submit of the form. You will have to modify your function myFunction() to make the AJAX call using an implementation similar to the following:

// Instancia de Request para llamar la URL
var request = new XMLHttpRequest();

// Capturar el evento de cambio de estado del request
request.onreadystatechange = function() {
  if(request.readyState === 4) {
    if(request.status === 200) { 
      // Ejecutar código de creación de registros
    } else {
      // Ejecutar código de captura de error en la
      // llamada de la URL
    } 
  }
}

// Enviar el request
request.open('POST', 'filtrarUsuarios.php'); // URL de ejemplo

The code shown above is the implementation most basic of an AJAX call. The call is asynchronous because the code that will create records dynamically executed until the response is received from the call to the URL.


Update question

In view of that you are going to choose poe r option 2 of the above mentioned (that is to get HTML code of AJAX call to insert it dynamically into our page), the file ajax.php should return the HTML for the composed table, which we will take the answer of the call (via ) and use it to replace the contents of a container (a new element <div class="table-container" />). Thus, the separation of the content generated by the query to the database is something like this:

        if (isset($_GET['page'])) :
            $page = $_GET['page'] ?: '';
        else :
            $page = 1;
        endif;

        if (isset($_POST['amount_show'])) :
            $records_by_page = $_POST['amount_show'];
        else :
            $records_by_page = 10;
        endif;

        $localization_sql = ($page-1) * $records_by_page;

        $sql = "SELECT id,name,email
                FROM users
                ORDER BY id DESC LIMIT $localization_sql, $records_by_page";
        $stmt = $con->prepare($sql);
        $stmt->execute();
        $stmt->store_result();
        if ($stmt->num_rows>0) :

        echo '<table id="myTable" class="table table-condensed table-hover table-striped bootgrid-table">
        <thead>
            <tr>
                <th>Id</th>
                <th>Name</th>
                <th>Email</th>
                <th>Action</th>
            </tr>
        </thead>
        <tbody>';

        $stmt->bind_result($id,$name,$email);
        while ($stmt->fetch()) :
            echo '<tr>
                <td>'.$id.'</td>
                <td>'.$name.'</td>
                <td>'.$email.'</td>
                <td>Edit</td>
            </tr>';
        endwhile;
        echo '</tbody>';
        echo '</table>';
        $stmt->close();

/**
*
* Botones ATRAS / SIGUIENTES
*
*/

        $sql = "SELECT * FROM users";
        $stmt = $con->prepare($sql);
        $stmt->execute();
        $stmt->store_result();

        $BD_records = $stmt->num_rows;
        $stmt->close();
        $con->close();

        $total_page = ceil($BD_records / $records_by_page);
        $prev = $page - 1;
        $next = $page + 1;
        echo '<div class=pagination>
        <ul class="pagination">';
        if ($prev > 0) :
            echo "<li><a href='index2.php?page=1'><i class='icon-angle-double-arrow'></i></a></li>";
            echo "<li><a href='index2.php?page=$prev'><i class='icon-angle-left'></i></a></li>";
        endif;

        for ($i=1; $i<=$total_page; $i++) :
            if ($page==$i) :
                echo "<li><a class=active>". $page . "</a></li>";
            else :
                echo "<li><a href='index2.php?page=$i'>$i</a></li>";
            endif;
        endfor;


        if ($page < $total_page ) :
            echo "<li><a href='index2.php?page=$next'><i class='icon-angle-right'></i></a></li>";
            echo "<li><a href='index2.php?page=$total_page'><i class='icon-angle-double-right'></i></a></li>";
        endif;

        echo '</ul></div>';

        else :
            $stmt->close();
        endif;

The a result of this page should be embedded in the main page dynamically, through of the function callback function $.ajax().

<script type="text/javascript">
$(document).ready(

function() { 
    $('.pagination li a').on('click', function(){
        // Podés crear una pantalla modal para
        // mostrar mientras carga el archivo ajax.php

        var page = $(this).attr('data');       

        // Podés asignar los parametros como JSON para
        // enviarlos en la función $.ajax
        var data = {
            page: page, 
            amount_show: $('#amount_show').val() // Obtener el valor del elemento select
        };

        $.ajax({
            type: "GET",
            url: "ajax.php",
            data: data,
            success: function(data) {

                // Reemplazar el contenido del div que creamos
                // para ubicar la tabla
                $('.table-container').html(data);

            }
        });
        return false;
    });              
});    
</script>

The function jQuery html() replaces the current content of the element $('.table-container') that we will place our table.

The parent page should be something like this:

<div id="wrapper">
    <div class="container">
        <div id="news-header" class="bootgrid-header container-fluid">
            <div class="row">
                <div class="col-sm-12 actionBar">
                    <div class="search-bar">
                        <input type="text" id="myInput" onkeyup="myFunction()" placeholder="What are you looking for?">
                    </div>
                    <div class="actions btn-group">
                        <?php
                            $select_quantity = '';
                            if (isset($_POST['amount_show'])) :
                                $select_quantity = $_POST['amount_show'];
                            endif;
                        ?>
                        <form method="post" action="<?php echo htmlspecialchars($_SERVER["PHP_SELF"]);?>">
                            <select id="amount_show" name="amount_show" onchange="this.form.submit()">
                                <option value="10" <?php if ($select_quantity==10) echo "selected"; ?>>10</option>
                                <option value="25" <?php if ($select_quantity==25) echo "selected"; ?>>25</option>
                                <option value="50" <?php if ($select_quantity==50) echo "selected"; ?>>50</option>
                                <option value="100" <?php if ($select_quantity==100) echo "selected"; ?>>100</option>
                            </select>
                        </form>
                    </div>
                </div>
            </div>
        </div>
        <div class="table-container">
        </div>
    </div>
</div>

The code shown above probably needs a little more work on your part, but I hope that I have conveyed clearly what you need to do.

0voto

I'm going to be admitted to be as clear as possible:

1 - you have to understand that PHP runs on the server side, and returns HTML that is interpreted by the browser .

2 - The topic of ajax/jquery is executed on the side of the clinet (browser) .

With this in mind consider the following:

  • The href of pagination , and the onchange of the select must refer to a function Javascript interpreted by the Browser.

  • The function in the browser is the one that has to fetch the data to display. which can be a PHP that is running on the server. $.ajax

  • The data returned by the PHP can be implemented in your HTML as you show the code that you show us:

$.ajax({
 type: "GET",
 url: "ajax.php",
 data: dataString,
 success: function(data) {
$('.items').fadeIn(2000).html(data);
 $('.pagination li').removeClass('active');
 $('.pagination li a[data="'+page+'"]').parent().addClass('active');

}
});

I hope it will be useful.

0voto

Juan Carlos Hdz Points 931

In the file where you have the form to add and set the following: in the form define id="form-pagination" and the onsubmit="return false;" to not reload the page, also add two div where you will insert the results table and another div where you added the page .

<div id="wrapper">
    <div class="container">
        <div id="news-header" class="bootgrid-header container-fluid">
            <div class="row">
                <div class="col-sm-12 actionBar">
                    <div class="search-bar">
                        <input type="text" id="myInput" onkeyup="myFunction()" placeholder="What are you looking for?">
                    </div>
                    <div class="actions btn-group">
                        <?php
                            $select_quantity = '';
                            if (isset($_POST['amount_show'])) :
                                $select_quantity = $_POST['amount_show'];
                            endif;
                        ?>
                        <form method="post" id="form-pagination" onsubmit="return false;">
                            <input name="page" id="page" type="hidden" value=1>
                            <select id="amount_show" name="amount_show" >
                                <option value="10" <?php if ($select_quantity==10) echo "selected"; ?>>10</option>
                                <option value="25" <?php if ($select_quantity==25) echo "selected"; ?>>25</option>
                                <option value="50" <?php if ($select_quantity==50) echo "selected"; ?>>50</option>
                                <option value="100" <?php if ($select_quantity==100) echo "selected"; ?>>100</option>
                            </select>
                        </form>
                    </div>
                </div>
            </div>
        </div>
        <div id="contenido_table"  class="col-sm-12">
        </div>
        <div id="pagination" class="col-sm-12">
        </div>
    </div>
</div>

In the file ajax.php you can return two parts the content of the table and pagination in the items that are found within the buttons of the paging to define the attribute page="". and return the pagination and the table of results

    header('Content-Type: application/json');
    $html_table="";
    if (isset($_GET['page'])) :
        $page = $_GET['page'] ?: '';
    else :
        $page = 1;
    endif;

    if (isset($_POST['amount_show'])) :
        $records_by_page = $_POST['amount_show'];
    else :
        $records_by_page = 10;
    endif;

    $localization_sql = ($page-1) * $records_by_page;

    $sql = "SELECT id,name,email
            FROM users
            ORDER BY id DESC LIMIT $localization_sql, $records_by_page";
    $stmt = $con->prepare($sql);
    $stmt->execute();
    $stmt->store_result();
    if ($stmt->num_rows>0) :
        $html_table.='<table id="myTable" class="table table-condensed table-hover table-striped bootgrid-table">
    <thead>
        <tr>
            <th>Id</th>
            <th>Name</th>
            <th>Email</th>
            <th>Action</th>
        </tr>
    </thead>
    <tbody>';

    $stmt->bind_result($id,$name,$email);
    while ($stmt->fetch()) :
        $html_table.='<tr>
            <td>'.$id.'</td>
            <td>'.$name.'</td>
            <td>'.$email.'</td>
            <td>Edit</td>
        </tr>';
    endwhile;
    $html_table.='</tbody>';
    $html_table.='</table>';
    $stmt->close();

/**
*
* Botones ATRAS / SIGUIENTES
*
*/
    $html_pagination="";
    $sql = "SELECT * FROM users";
    $stmt = $con->prepare($sql);
    $stmt->execute();
    $stmt->store_result();

    $BD_records = $stmt->num_rows;
    $stmt->close();
    $con->close();

    $total_page = ceil($BD_records / $records_by_page);
    $prev = $page - 1;
    $next = $page + 1;
    $html_pagination.='<div class=pagination>
    <ul class="pagination">';
    if ($prev > 0) :
        $html_pagination.="<li><a page='1'><i class='icon-angle-double-arrow'></i></a></li>";
        $html_pagination.="<li><a page='$prev'><i class='icon-angle-left'></i></a></li>";
    endif;

    for ($i=1; $i<=$total_page; $i++) :
        if ($page==$i) :
            $html_pagination.= "<li><a class=active>". $page . "</a></li>";
        else :
            $html_pagination.= "<li ><a page='$i'>$i</a></li>";
        endif;
    endfor;


    if ($page < $total_page ) :
        $html_pagination.= "<li ><a page='$next' href=''><i class='icon-angle-right'></i></a></li>";
        $html_pagination.="<li ><a page='$total_page' href=''><i class='icon-angle-double-right'></i></a></li>";
    endif;

    $html_pagination.='</ul></div>';

    else :
        $stmt->close();
    endif;
$data=array();
$data["html_table"]=$html_table;
$data["html_pagination"]=$html_pagination;
echo json_encode($data, JSON_PRETTY_PRINT);

in your script.js to add questions to the functions, in the event that involves the pagination updates the value of the page field that we added on the form so that when you call the function enviarFormulario only take the content of the form and send.

   //se ejecuta el submit del formulario form-pagination al inicio
$(document).ready(function() {
    enviarFormulario();
});
//evento para que se ejecute al dar click sobre los botones de pagination
$(document).on('click', '.pagination li a', function(e){
    var page=parseInt($(this).data("page"));
    if(!isNaN(page)){
        $("#form-pagination #page").val(page);
        enviarFormulario();
    }
    e.preventDefault();
});
//evento para que se ejecute al cambiar el valor de amount_show
$(document).on('change', '#amount_show', function(event){
    $("#form-pagination #page").val(1);
    enviarFormulario();
});
//funcion para que se ejecute al realizar el submit en el formulario form-pagination
function enviarFormulario(){
    $.ajax({
            type: "GET",
            data: $("#form-pagination").serialize(),
            url: "ajax.php",
            dataType: "json",
            success: function(data) {
                $("#contenido_table").html(data["html_table"]);
                $("#pagination").html(data["html_pagination"]);
            }
    });  

}

I hope that it will be of great help.

HolaDevs.com

HolaDevs is an online community of programmers and software lovers.
You can check other people responses or create a new question if you don't find a solution

Powered by:

X