Good afternoon, colleagues! I have the usual code in PHP. There is a base in phpMyAdmin. Inside there is a table "clients". The number of entries, i.e. clients, exceeded 200 pcs. In the browser there is a head file "clients.php", in which through the div:

<script> $('#client_table').load('clients_table.php'); </script>

load the child file "clients_table.php" containing a table with data from phpMyAdmin - "clients". There are more and more records, and the list of customers is loaded longer and longer. How to make the downloadable file "clients_table.php" - pagination? To record displayed 10 pieces at a time with the possibility of paging. The number of customers will grow further! Everywhere I searched, I did not find a suitable one.

Here is the contents of the "clients_table.php" file:

 <?php require_once('lib/database.php'); date_default_timezone_set('Asia/Bishkek'); $db->orderBy("fio","asc"); $clients = $db->get('clients'); $i=1; $search=@$_GET['search']; $search_fio=@$_GET['search_fio']; if($search_fio){ $db->where("fio", "%$search_fio%", 'like'); $clients = $db->get('clients'); } elseif($search){ $db->where("tel_no",$search); $clients = $db->get('clients'); } ?> <table class="table table-bordered" id="catalog"> <thead class="thead-default"> <tr> <th>#</th> <th>Ф.И.О.</th> <th>Номер телефона</th> <th>Дата регистрации</th> <th>Действия</th> </tr> </thead> <tbody> <?php foreach ($clients as $visitor) { ?> <tr> <td><?php echo $i++;?></td> <td><a href="view_client.php?cid=<?php echo $visitor['id']; ?>"><?php echo $visitor['fio']; ?></a></td> <td><?php echo $visitor['tel_no']; ?></td> <td><?php echo date('G:id/m/Y',$visitor['date_reg']); ?></td> </td><td align="center"> <a href="javascript:void(0)" class="edit_client" data-client_id="<?php echo $visitor['id'];?>" data-fio="<?php echo $visitor['fio'];?>" data-tel_no="<?php echo $visitor['tel_no'];?>" data-toggle="modal" data-target="#edit_client">Править / <a href="process.php?client_id=<?php echo $visitor['id'];?>&link_target=del_client"> Удалить</a> </td> </tr> <?php } ?> </tbody> </table> <style type="text/css"> th { text-align: center; } td { text-align: center; } </style> <script> $(".edit_client").click(function() { var client_id=$(this).data('client_id'); var fio=$(this).data('fio'); var tel_no=$(this).data('tel_no'); $('#client_id').val(client_id); $('#fio').val(fio); $('#tel_no').val(tel_no); }); </script>

PS Inside the connection to database.php is a connection of classes from MysqliDb.php , taken from github. As a result, $db-> Thanks in advance! :-)

3 answers 3

Add an optional GET parameter to specify the page, for example, page . Based on this parameter and the number of records you want to see on the page, determine the indent for the query to the database:

 $offset = ($page-1) * $recordsPerPage; // если страницы нумеруем с 1

The number of pages depends on the number of entries:

 $pagesCount = ceil($recordsCount / $recordsPerPage);

Do not forget to specify the limit of records in the query to the database equal to $recordsPerPage .

  • github.com/joshcam/PHP-MySQLi-Database-Class/blob/master/… :) - E_p
  • I wonder why the newcomer, i.e. I, colleagues put -4 for this question? Of course, if a person is experienced, then my question seems to be irrelevant for him, but for me, any task is the conquest of another peak! Not good at it, UGLY! - aiba

Is not it easier to chop in the sql query?

 SELECT * FROM table WHERE ID > (page_id * limit) LIMIT limit //page_id - берется из гет запроса, limit = задается какойнибудь константой в пхп к примеру //кол-во страниц - entries_count / limit
  • And if you delete a couple of entries on the page? - xEdelweiss
  • Correct answer!
  • Hello! After much trial and error, a good pagination video was found for those who have normal functions and arrays.
  • I remade it under my own PLO.

1) Here is the original (referring to the video on youtube):

 <?php // Line by line explanation : youtube.com/watch?v=T2QFNu_mivw include_once("mysqli_connection.php"); // This first query is just to get the total count of rows $sql = "SELECT COUNT(id) FROM testimonials WHERE approved='1'"; $query = mysqli_query($db_conx, $sql); $row = mysqli_fetch_row($query); // Here we have the total row count $rows = $row[0]; // This is the number of results we want displayed per page $page_rows = 10; // This tells us the page number of our last page $last = ceil($rows/$page_rows); // This makes sure $last cannot be less than 1 if($last < 1){ $last = 1; } // Establish the $pagenum variable $pagenum = 1; // Get pagenum from URL vars if it is present, else it is = 1 if(isset($_GET['pn'])){ $pagenum = preg_replace('#[^0-9]#', '', $_GET['pn']); } // This makes sure the page number isn't below 1, or more than our $last page if ($pagenum < 1) { $pagenum = 1; } else if ($pagenum > $last) { $pagenum = $last; } // This sets the range of rows to query for the chosen $pagenum $limit = 'LIMIT ' .($pagenum - 1) * $page_rows .',' .$page_rows; // This is your query again, it is for grabbing just one page worth of rows by applying $limit $sql = "SELECT id, firstname, lastname, datemade FROM testimonials WHERE approved='1' ORDER BY id DESC $limit"; $query = mysqli_query($db_conx, $sql); // This shows the user what page they are on, and the total number of pages $textline1 = "Testimonials (<b>$rows</b>)"; $textline2 = "Page <b>$pagenum</b> of <b>$last</b>"; // Establish the $paginationCtrls variable $paginationCtrls = ''; // If there is more than 1 page worth of results if($last != 1){ /* First we check if we are on page one. If we are then we don't need a link to the previous page or the first page so we do nothing. If we aren't then we generate links to the first page, and to the previous page. */ if ($pagenum > 1) { $previous = $pagenum - 1; $paginationCtrls .= '<a href="'.$_SERVER['PHP_SELF'].'?pn='.$previous.'">Previous</a> &nbsp; &nbsp; '; // Render clickable number links that should appear on the left of the target page number for($i = $pagenum-4; $i < $pagenum; $i++){ if($i > 0){ $paginationCtrls .= '<a href="'.$_SERVER['PHP_SELF'].'?pn='.$i.'">'.$i.'</a> &nbsp; '; } } } // Render the target page number, but without it being a link $paginationCtrls .= ''.$pagenum.' &nbsp; '; // Render clickable number links that should appear on the right of the target page number for($i = $pagenum+1; $i <= $last; $i++){ $paginationCtrls .= '<a href="'.$_SERVER['PHP_SELF'].'?pn='.$i.'">'.$i.'</a> &nbsp; '; if($i >= $pagenum+4){ break; } } // This does the same as above, only checking if we are on the last page, and then generating the "Next" if ($pagenum != $last) { $next = $pagenum + 1; $paginationCtrls .= ' &nbsp; &nbsp; <a href="'.$_SERVER['PHP_SELF'].'?pn='.$next.'">Next</a> '; } } $list = ''; while($row = mysqli_fetch_array($query, MYSQLI_ASSOC)){ $id = $row["id"]; $firstname = $row["firstname"]; $lastname = $row["lastname"]; $datemade = $row["datemade"]; $datemade = strftime("%b %d, %Y", strtotime($datemade)); $list .= '<p><a href="testimonial.php?id='.$id.'">'.$firstname.' '.$lastname.' Testimonial</a> - Click the link to view this testimonial<br>Written '.$datemade.'</p>'; } // Close your database connection mysqli_close($db_conx); ?> <!DOCTYPE html> <html> <head> <style type="text/css"> body{ font-family:"Trebuchet MS", Arial, Helvetica, sans-serif;} div#pagination_controls{font-size:21px;} div#pagination_controls > a{ color:#06F; } div#pagination_controls > a:visited{ color:#06F; } </style> </head> <body> <div> <h2><?php echo $textline1; ?> Paged</h2> <p><?php echo $textline2; ?></p> <p><?php echo $list; ?></p> <div id="pagination_controls"><?php echo $paginationCtrls; ?></div> </div> </body> </html>

2) But my working option. a) For this, in my class https://github.com/joshcam/PHP-MySQLi-Database-Class/blob/master/MysqliDb.php#L2224 I had to change a couple of values:

  • paginate function, since there you need to pull out not one, but two values:

public function paginate ($table, $page, $fields = null){ $offset = $this->pageLimit * ($page - 1); $res = $this->withTotalCount()->get ($table, Array ($offset, $this->pageLimit), $fields); $dg = $this->totalPages = ceil($this->totalCount / $this->pageLimit); return [ "res" => $res, "last" => $dg ]; }

  • and the $ pageLimit variable at the top. You expose it as you need entries per page.

b) Since My table comes out in the second attached file, so I have to send a GET request to the main file, and from there load the necessary file, again with a GET request. If you have 1 file, then you should not change '. $ _ SERVER [' PHP_SELF '].'

c) to display beautiful buttons in pagination, do not forget to connect BUTSTRAP <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">

The main file "clients.php":

 <?php $pn = @$_GET['pn']; if($pn == ""){ $pn = 1; } ?> <div>$('#client_table').load('clients_table.php?pn=<?php echo $pn; ?>');</div>

Attached file "clients_table.php":

 <?php require_once('database.php'); // внутри подключение к MysqliDb.php $pagenum = 1; $res = $db->paginate('clients', $pagenum); $last = $res['last']; if($last < 1){ $last = 1; } if(isset($_GET['pn'])){ $pagenum = preg_replace('#[^0-9]#', '', $_GET['pn']); } if ($pagenum < 1) { $pagenum = 1; }else if($pagenum > $last){ $pagenum = $last; } $db->orderBy("fio","asc"); $res = $db->paginate('clients', $pagenum); $clients = $res['res']; $g=1; $textline = "Страница <b>$pagenum</b> из <b>$last</b>"; $paginationCtrls = ''; if($last != 1){ if ($pagenum > 3) { $paginationCtrls .= '<a href="clients.php?pn=1" class="btn btn-default" role="button"><b><< </b></a> &nbsp;'; } if ($pagenum > 1) { $previous = $pagenum - 1; $paginationCtrls .= '<a href="clients.php?pn='.$previous.'" class="btn btn-default" role="button"><b>< </b></a> &nbsp; &nbsp; '; for($i = $pagenum-2; $i < $pagenum; $i++){ if($i > 0){ $paginationCtrls .= '<a href="clients.php?pn='.$i.'" class="btn btn-default" role="button">'.$i.'</a> &nbsp; '; } } } $paginationCtrls .= '<p color:black; class="btn btn-default" role="button"><b>'.$pagenum.'</b></p>&nbsp; '; for($i = $pagenum+1; $i <= $last; $i++){ $paginationCtrls .= '<a href="clients.php?pn='.$i.'" class="btn btn-default" role="button">'.$i.'</a> &nbsp; '; if($i >= $pagenum+2){ break; } } if ($pagenum != $last) { $next = $pagenum + 1; $paginationCtrls .= ' &nbsp; &nbsp; <a href="clients.php?pn='.$next.'" class="btn btn-default" role="button"><b>>&nbsp;</b></a> '; } if ($pagenum < $last-2) { $paginationCtrls .= '<a href="clients.php?pn='.$last.'" class="btn btn-default" role="button"><b> >></b></a> &nbsp; &nbsp; '; } } ?> <center> <div class="container"> <table class="table table-bordered" id="catalog"> <thead class="thead-default"> <tr> <th>#</th> <th>Ф.И.О.</th> <th>Номер телефона</th> <th>Дата регистрации</th> <th>Действия</th> </tr> </thead> <tbody> <?php foreach ($clients as $visitor) { ?> <tr> <td><?php echo $g++;?></td> <td><a href="view_client.php?cid=<?php echo $visitor['id']; ?>"><?php echo $visitor['fio']; ?></a></td> <td><?php echo $visitor['tel_no']; ?></td> <td><?php echo date('G:id/m/Y',$visitor['date_reg']); ?></td> </td> <td align="center"> <a href="javascript:void(0)" class="edit_client" data-client_id="<?php echo $visitor['id'];?>" data-fio="<?php echo $visitor['fio'];?>" data-tel_no="<?php echo $visitor['tel_no'];?>" data-toggle="modal" data-target="#edit_client">Править / <a href="process.php?client_id=<?php echo $visitor['id'];?>&link_target=del_client"> Удалить</a> </td> </tr> <?php } ?> </tbody> </table> </div> </center> <div id="pagination_controls"><?php echo $paginationCtrls; ?>&nbsp;&nbsp;<?php echo $textline; ?></div> <style type="text/css"> th { text-align: center; } td { text-align: center; } div#pagination_controls{font-size:15px;} div#pagination_controls > a{ color:#06F; } div#pagination_controls > a{ text-decoration:none; } div#pagination_controls > a:visited{ color:#06F; } </style> <script> $(".edit_client").click(function() { var client_id=$(this).data('client_id'); var fio=$(this).data('fio'); var tel_no=$(this).data('tel_no'); $('#client_id').val(client_id); $('#fio').val(fio); $('#tel_no').val(tel_no); }); </script>

SUCCESS TO ALL !!!