How does the pagination with PHP + AJAX occur?

Question

I was browsing the site looking for a functional example of paging with AJAX + PHP, when I found this example

Excuse my ignorance, but the 'asynchronous loading' of AJAX causes me doubts..

However, a question arose: In the script, there is a limit made by AJAX of 6 records to be displayed by pages. Above all, there is no limit if I make one SELECT * FROM tabela. So, did you imply that if there are 500 records on the table, they will all be loaded, but only six per page? Wouldn’t that delay the flow of the site? I mean the loading time?

Other question: How could I change the array in the example for a SELECT, to be able to use a mysqli_fetch_array to search for each record line?

Note: If necessary, I will be posting the code (which is quite extensive) for better viewing.

Answer 1

In this example, AJAX actually displays the 6 in 6 records, but all of them are loaded. If there were a thousand records, for example, it would be a problem.

A simple alternative is to prepare a php page containing a SELECT limited to X records. You must pass by parameter the number of the pagination that will be shown and the page will return the records.

The SQL command can look like this (php):

$mysqli->query('SELECT * FROM tabela ORDER BY id DESC LIMIT '.$inicio.','.$limite.'');

The return of this search will be the records in the given interval. To know how it works: http://www.w3schools.com/php/php_mysql_select_limit.asp.

You should return this query as an array to AJAX. You can understand how to do this here: https://stackoverflow.com/questions/10220642/pass-php-array-into-javascript-array.

But for paging to work, you must calculate the $start variable.

To calculate this start, you can do it as follows:

$limite = 3; // Limite de registros por paginação
$pagina = (isset($_GET['page']))? $_GET['page'] : 1; // Recebe o número da paginação por método GET
$inicio = ($limite * $pagina) - $limite; // Cálculo do início

You can also get the total number of pages:

$result = $mysqli->query("SELECT * FROM tabela");
$total = $result->num_rows; // Quantidade total de registros
$numPaginas = ceil($total/$limite); // Quantidade total de páginas

Answer 2

In paging, the SELECT used in the bank needs to use LIMIT to indicate how many records it is to search for and OFFSET to tell you how many records to skip. On the link you posted doesn’t tell you how SELECT is done, it just mounts a Javascript array to use as a sample.

---

Another question: How I could change the array in the example for a SELECT, to use a mysqli_fetch_array to search for each record line?

Your ajax request will call a PHP script, this script in turn will fetch the data in the database using the method you want, be it PDO, mysql, mysqli, etc.

---

In order for PHP to know how many records to skip in SELECT, you need to enter the page number the user wants to view, this parameter will be sent by AJAX to the PHP script.

Example displaying 10 records per page
The ajax URL will be "script.php? page=1", "script.php? page=2", "script.php? page=3", etc...

// No script.php:
$registrosPorPagina = 10;
$pagina             = intval($_GET['pagina']);

// Calcula o offset de acordo com o número da página
$offset             = $pagina == 1 ? 0 : ($pagina * $registrosPorPagina) - $registrosPorPagina;

// Comando SELECT dinâmico, sempre irá buscar só 10 registros de acordo com a página
// offset 0: registros 1-10
// offset 1: registros 11-20
// offset 2: registros 21-30
$sql = "SELECT * FROM tabela LIMIT 10 OFFSET {$offset}";