Запрос MYSQL с разбивкой по страницам PHP

Да, сначала вам нужно узнать общее количество записей. Затем запустите запрос еще раз, на этот раз с LIMIT в конце вашего запроса. Например, LIMIT 0, 10. В этом примере возвращается 10 результатов, начиная с записи 0 (первая запись). Если вы динамически устанавливаете первую запись результатов лимита с использованием переменной, вы можете обновить ее с каждой страницы. Для каждой страницы вы можете рассчитать, какова должна быть начальная запись. Например, номер страницы $ offset = $ recordsperpage *.

Я точно не знаю, что такое ваши переменные, поэтому я не буду играть со своим скриптом. Вот пример, который я использовал для проекта, который, как я знаю, работает. Пример скрипта:

 //Use mysqli object instead of mysql_xxx, better security $db3= new mysqli($hostname, $username, $password, $dbname); //how many records I want per page $perPage= 9; //I'm passing the startrecording as a Get variable in the page links if($_GET['startingrecord']){ $startingRecord=trim($_GET['startingrecord']); } //but if there is no get variable, start at record zero else { $startingRecord=0; } //prepare your query $stmt3 = $db3->prepare("SELECT DISTINCT file, category, P.productID, price, title, price * (1- sale) FROM Products AS P INNER JOIN OrderProducts AS OP ON P.productID NOT IN (Select productID from OrderProducts) Left JOIN Sale AS S on P.productID= S.ProductID ORDER BY productID DESC"); // You can bind parameters if necessary, but it's not here //execute the query and store the result $stmt3 -> execute(); $stmt3->store_result(); //determine the number of total rows $numberRows= $stmt3->num_rows; //close the db $db3 -> close; //Reopen with limits this time. $db3= new mysqli($hostname, $username, $password, $dbname); $stmt3 = $db3->prepare("SELECT DISTINCT file, category, P.productID, price, title, price * (1- sale) FROM Products AS P INNER JOIN OrderProducts AS OP ON P.productID NOT IN (Select productID from OrderProducts) Left JOIN Sale AS S on P.productID= S.ProductID ORDER BY productID DESC LIMIT ?, ?"); // bind the startingrecord from the get variable and the perpage variable. stmt3->bind_param("ii", $startingRecord, $perPage); //you use ceiling to make sure if that if there's a remainder, you have an extra page for the stray results $pages= ceil($numberRows/ $perPage); //This is me generating the page links for ($i=1; $i<= $pages; $i++) { if ($i==1){ $start=0; } else{ $start= ($i-1) * ($perPage); } echo "<a href='#' onclick='gallery(\"startingrecord=".$start."\")'>".$i." </a>"; } $stmt3 -> execute(); $stmt3->store_result(); $stmt3->bind_result($file, $category2, $productID, $price, $title2, $sale); while($stmt3->fetch()){ // do whatever you want to your result }) в //Use mysqli object instead of mysql_xxx, better security $db3= new mysqli($hostname, $username, $password, $dbname); //how many records I want per page $perPage= 9; //I'm passing the startrecording as a Get variable in the page links if($_GET['startingrecord']){ $startingRecord=trim($_GET['startingrecord']); } //but if there is no get variable, start at record zero else { $startingRecord=0; } //prepare your query $stmt3 = $db3->prepare("SELECT DISTINCT file, category, P.productID, price, title, price * (1- sale) FROM Products AS P INNER JOIN OrderProducts AS OP ON P.productID NOT IN (Select productID from OrderProducts) Left JOIN Sale AS S on P.productID= S.ProductID ORDER BY productID DESC"); // You can bind parameters if necessary, but it's not here //execute the query and store the result $stmt3 -> execute(); $stmt3->store_result(); //determine the number of total rows $numberRows= $stmt3->num_rows; //close the db $db3 -> close; //Reopen with limits this time. $db3= new mysqli($hostname, $username, $password, $dbname); $stmt3 = $db3->prepare("SELECT DISTINCT file, category, P.productID, price, title, price * (1- sale) FROM Products AS P INNER JOIN OrderProducts AS OP ON P.productID NOT IN (Select productID from OrderProducts) Left JOIN Sale AS S on P.productID= S.ProductID ORDER BY productID DESC LIMIT ?, ?"); // bind the startingrecord from the get variable and the perpage variable. stmt3->bind_param("ii", $startingRecord, $perPage); //you use ceiling to make sure if that if there's a remainder, you have an extra page for the stray results $pages= ceil($numberRows/ $perPage); //This is me generating the page links for ($i=1; $i<= $pages; $i++) { if ($i==1){ $start=0; } else{ $start= ($i-1) * ($perPage); } echo "<a href='#' onclick='gallery(\"startingrecord=".$start."\")'>".$i." </a>"; } $stmt3 -> execute(); $stmt3->store_result(); $stmt3->bind_result($file, $category2, $productID, $price, $title2, $sale); while($stmt3->fetch()){ // do whatever you want to your result }) 

 <style> ul.pagination { font-family: "Arial", "Helvetica", sans-serif; font-size: 13px; height: 100%; list-style-type: none; margin: 20px 0; overflow: hidden; padding: 0; } ul.pagination li.details { background-color: white; border-color: #C8D5E0; border-image: none; border-style: solid; border-width: 1px 1px 2px; color: #1E598E; font-weight: bold; padding: 8px 10px; text-decoration: none; } ul.pagination li.dot { padding: 3px 0; } ul.pagination li { float: left; list-style-type: none; margin: 0 3px 0 0; } ul.pagination li:first-child { margin-left: 0; } ul.pagination li a { color: black; display: block; padding: 7px 10px; text-decoration: none; } ul.pagination li a img { border: medium none; } ul.pagination li a.current { background-color: white; border-radius: 0 0 0 0; color: #333333; } ul.pagination li a.current:hover { background-color: white; } ul.pagination li a:hover { background-color: #C8D5E0; } ul.pagination li a { background-color: #F6F6F6; border-color: #C8D5E0; border-image: none; border-style: solid; border-width: 1px 1px 2px; color: #1E598E; display: block; font-weight: bold; padding: 8px 10px; text-decoration: none; } </style> <?php /******************************************pagination*****function*****************************************/ function pagination($per_page = 10, $page = 1, $url = '', $total){ $adjacents = "2"; $page = ($page == 0 ? 1 : $page); $start = ($page - 1) * $per_page; $prev = $page - 1; $next = $page + 1; $lastpage = ceil($total/$per_page); $lpm1 = $lastpage - 1; $pagination = ""; if($lastpage > 1) { $pagination .= "<ul class='pagination'>"; $pagination .= "<li class='details'>Page $page of $lastpage</li>"; if ($lastpage < 7 + ($adjacents * 2)) { for ($counter = 1; $counter <= $lastpage; $counter++) { if ($counter == $page) $pagination.= "<li><a class='current'>$counter</a></li>"; else $pagination.= "<li><a href='{$url}$counter'>$counter</a></li>"; } } elseif($lastpage > 5 + ($adjacents * 2)) { if($page < 1 + ($adjacents * 2)) { for ($counter = 1; $counter < 4 + ($adjacents * 2); $counter++) { if ($counter == $page) $pagination.= "<li><a class='current'>$counter</a></li>"; else $pagination.= "<li><a href='{$url}$counter'>$counter</a></li>"; } $pagination.= "<li class='dot'>...</li>"; $pagination.= "<li><a href='{$url}$lpm1'>$lpm1</a></li>"; $pagination.= "<li><a href='{$url}$lastpage'>$lastpage</a></li>"; } elseif($lastpage - ($adjacents * 2) > $page && $page > ($adjacents * 2)) { $pagination.= "<li><a href='{$url}1'>1</a></li>"; $pagination.= "<li><a href='{$url}2'>2</a></li>"; $pagination.= "<li class='dot'>...</li>"; for ($counter = $page - $adjacents; $counter <= $page + $adjacents; $counter++) { if ($counter == $page) $pagination.= "<li><a class='current'>$counter</a></li>"; else $pagination.= "<li><a href='{$url}$counter'>$counter</a></li>"; } $pagination.= "<li class='dot'>..</li>"; $pagination.= "<li><a href='{$url}$lpm1'>$lpm1</a></li>"; $pagination.= "<li><a href='{$url}$lastpage'>$lastpage</a></li>"; } else { $pagination.= "<li><a href='{$url}1'>1</a></li>"; $pagination.= "<li><a href='{$url}2'>2</a></li>"; $pagination.= "<li class='dot'>..</li>"; for ($counter = $lastpage - (2 + ($adjacents * 2)); $counter <= $lastpage; $counter++) { if ($counter == $page) $pagination.= "<li><a class='current'>$counter</a></li>"; else $pagination.= "<li><a href='{$url}$counter'>$counter</a></li>"; } } } if ($page < $counter - 1){ $pagination.= "<li><a href='{$url}$next'>Next</a></li>"; // $pagination.= "<li><a href='{$url}$lastpage'>Last</a></li>"; }else{ //$pagination.= "<li><a class='current'>Next</a></li>"; // $pagination.= "<li><a class='current'>Last</a></li>"; } $pagination.= "</ul>\n"; } return $pagination; } /****************************************////**pagination*****function*****************************************/ ?> <!---------------------------pagination-------------------------------------------------> <?php //error_reporting(0); $con = mysqli_connect("localhost","root","","accounts"); $page=1;//Default page $limit=2;//Records per page $start=0;//starts displaying records from 0 if(isset($_GET['page']) && $_GET['page']!=''){ $page=$_GET['page']; } $start=($page-1)*$limit; ?> <!---------------------------pagination-------end------------------------------------------> <table width="100%" > <tr> <td> <?php $query = mysqli_query($con,"select * from receipts"); $rows=mysqli_num_rows($query); //newly registerd on top $query=mysqli_query($con,"select * from receipts order by r_id ASC LIMIT $start, $limit"); if(mysqli_num_rows($query)>0){ while($row = mysqli_fetch_array($query,1)){ ?> <table cellpadding="3" cellspacing="3" border="1px #f8f8f8" width="100%" style="background-color:#FFFFFF;border:1px solid #e8e8e8"> <tr> <td id="class1">Name</td> <td id="class2"><?php echo $row['party_name'] ;?></td> </tr> <tr><td id="class1">Roll No</td> <td width="700px" id="class2"><?php echo $row['receipt_no']; ?></td> </tr> <tr> <td id="class1" width="170px">Date</td> <td id="class2"><?php echo $row['date1']; ?></td> </tr> </table> <?php }} ?> </td> </tr> </table> <?php if(empty($_GET['id'])) { $id = 1; } if(isset($_GET['id'])){ $id = $_GET['id']; } echo '</table><table>'; echo "<tr><td>"; echo pagination($limit,$page,'try.php?id='.$id.'&page=',$rows); //call function to show pagination echo "</td></tr>"; echo "</table>"; ?> </div>