I have a pagination that works correctly, even the filter to paginate more rows on the same page, that is to say that through the filter I can show 10 or 50 rows or more.
The small defect that the code has is that the page is reloaded, changing the number of rows that are displayed and the same thing happens in the pagination buttons.
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 configurations due to the recommendations of JSON
, which have been recommended to me.
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
, and throughajax
$(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 display the data with no 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, how to show pagination buttons and filter show more result row.
How do I have to send this information to ajax and display the sent information?
can you explain to me
Tenemos en los navegadores diferentes acciones predeterminadas es importante tener en cuenta, en los artículos en ingles podemos encontrar una gran información y con ejecuciones reales para entender su funcionamiento también es importante ir conociendo a más a fondo el lenguaje.
Ahora para comenzar efectuar cambios se debe detectar primeramente las posibles ejecuciones que harán recargar la página, en este caso:
select option -> #amount_show
que envía un nuevo valor por métodoPOST
al paginador.<a>
que le dice alscript php
qué registros debe recuperar a través del métodoGET
solicitud.Entonces como no existe inconvenientes en el
Script PHP
, todo debes ir manejando desde el códigoajax
.Por lo tanto, el código
ajax
, quedaría de tal forma:Recomendaciones
Referencias
.load
(artículo en inglés).Brief introduction on the use of AJAX
First of all , ajax means A synchronous Javascript in X ML . In short, AJAX is used to obtain information from the server (Server-side operations or data from the database) that you have not uploaded with the site. For example, the PHP blocks (delimited by the tags
<?php ?>
) are part of the site code that the server executes, prior to delivering the content to the client .Having explained this, since the information you are filtering in the table is information that is already loaded in the page , it is not an AJAX operation that you are doing, but rather you are executing a client-side script to show/hide records of the table based on the text entered in the filter input.
From your question, I understand that what is happening is that your filter function only operates on the records that are displayed on the page, and it does not display enough records to fill the table based on the size of the pagination. For example, if paging is set to display 50 records, the filter displays only 39 records from the current page.
In order for the filter to return 50 records (based on the example above), you must send an asynchronous request to the server. Let's say, your site must have a URL (for example https://yoursite.com/filtrarUsuarios.php ) to which an asynchronous query is made sending the parameters by which you are going to filter, so that PHP returns:
<table></table>
).Once you get the response from the server, you must dynamically place the elements in the table. If it's option 1, you should use the function
document.createElement()
to create HTML elements (tr
andtd
) to place them in your table, and if it's option 2, replacing the HTML of the table usinginnerHTML
. My suggestion is to use option 1, since it is the standard for consuming APIs and web services.Since the way you used in your example does a POST to the same page, where it loads the records, I understand that the filter doesn't work asynchronously. That is, the page reloads with the filtered records once you submit the form. You'll need to modify your function
myFunction()
to make the AJAX call using an implementation similar to the following:The code shown above is the most basic implementation of an AJAX call. The call is asynchronous because the code that will dynamically create the records is executed until the response from the URL call is received.
question update
Given that you are going to choose option 2 of those mentioned above (which consists of obtaining HTML code from the AJAX call to insert it dynamically in our page), the file
ajax.php
must return the HTML formed from the table, which we will take from the response of the call (via javascript ) and we will use to replace the content 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:The result of this page must be embedded in the main page dynamically, through the function
callback
of the function$.ajax()
.The jQuery function
html()
replaces the current content of the element$('.table-container')
that we will create to place our table on.The parent page should look something like this:
The code shown above probably needs a little more work on your part, but I hope I've clearly conveyed what you need to do.
I will try to be as clear as possible:
1 - You must understand that PHP is executed on the server side and returns HTML that the browser interprets.
2 - The ajax/jquery theme is executed on the client side (browser).
With this in mind, consider the following:
Los href de paginación , y el onchange del select deben hacer referencia a una funcion Javascript interpretada por el Browser.
La función en el browser es el que tiene que ir a buscar los datos a mostrar. el cual puede ser un PHP que se ejecuta en el server. $.ajax
Los datos que devuelva el PHP se pueden implementar en tu HTML tal como te muestra el código que nos muestras:
Espero que te sea de utilidad.
En el archivo donde tiene el formulario agregar y definir lo siguiente: en el form definir un id="form-pagination" y el onsubmit="return false;" para que no se recargue la pagina, ademas agregar dos div donde se insertara la tabla de resultados y otro div donde se agregara la paginación .
En el archivo ajax.php puedes devolver dos partes el contenido de la tabla y la paginación, en los elementos que se encuentran dentro de los botones de la paginación se definirá el atributo page="". y se regresara la paginación y la tabla de resultados
en tu script.js agregaras las funciones, en el evento que involucra a la paginacion se actualiza el valor del campo page que agregamos en el formulario para que al momento de llamar la funcion enviarFormulario solo se tome el contenido del formulario y se envié.
Espero que te sea de gran ayuda.
Te voy a dejar un ejemplo que he probado en Localhost según tu código, estuve haciendo pruebas ya que estoy aprendiendo jQuery / JavaScript y que mejor aprendizaje que pelearse con el código :)
El sistema de filtración dispone de:
He cambiado el parámetro
data: $.param(query_params)
pordata: jQuery.param(query_params),
jQuery.param ()
toma una matriz de pares clave-valor y la convierte en una cadena que puede usar como cadena de consulta en solicitudes HTML.Tambien he modernizado el código AJAX.
una de ellas
$.ajax({
porvar request = $.ajax({
Base de Datos
Posible ejemplo:
AJAX
Layout HTML
La pregunta es un tanto antigua y conviene actualizar, no solo para optimizar, también para facilitar el mantenimiento a futuro. Mis recomendaciones:
data-id="id-de-cliente"
En PHP vas a recibir en
$_GET
lo siguiente: pagina, elementos, tipo y nombre. Y debes validar cada uno para saber si se deben incluir en la consulta, de preferencia, con asignación ternaria; por ejemplo:Ya teniendo todas las variables, debes analizar cada una para saber si es necesario agregarla a filtros y vas a necesitar ejecutar dos consultas, una para contar las filas y otra para obtenerlas. Revisa los comentarios en el código para saber qué hace cada cosa.
En este ejemplo solo se agregaron dos opciones para filtrar, pero fácilmente puedes agregar más:
loadAjax()
drawTable()
to display that column->bind_result()
and add the data in the array