Pagination with While loop

[Tony_20]

Hello ladies and gents, i hope you are all well! Just recently joined this site looks great and easy to use.

I am just getting into php and msql i have managed to create a table that spits out the data but i am looking for a really simple way to paginate this while loop so it only shows a certain number of rows, then some buttons to load the next page.

Could any please point me in the right direction here? i have came acros some crazy examples.. Just looking for a clean simple way.

Many thanks.

<h2 align="center">List of Companies</h2> <table id="ver-minimalist" align="center" border="0" cellspacing="0" cellpadding="5" width="100%"> <thead> <th>No</th> <th>Name</th> <th>Company</th> <th>Revenue</th> <th>Location</th> <th>Action</th> </thead> <?php
                    $i=1;
                        while ($row=mysql_fetch_assoc($query)) {
                            echo "<tr> <td>".$i."</td> <td>".$row['name']."</td> <td>".$row['company']."</td> <td>".$row['revenue']."</td> <td>".$row['location']."</td> <td align='center'> <a href='index.php?epr=delete&id=".$row['id']."'>DELETE</a> |
                                   <a href='index.php?epr=update&id=".$row['id']."'>UPDATE</a> |  
                               </td> </tr>";
                            $i++;
                    }

                    ?> </table> </table>

[Isaac_4]

The general approach is to do two queries.

Since you didn't include the code that generated your result set, I will use a simple example.

The idea is to do a SELECT COUNT(*) query to figure out how many rows there are in the table, then do a separate SELECT * query to get the current page of data.

<?php

$where = '1=1'; // If you have any conditions in your query, include them here
$perPage = 25;
if(isset($_GET['p'])) {
    $page = (int)$_GET['p']-1; // Get the page selected
} else {
    $page = 0;
}

$countSQL = 'SELECT COUNT(*) As total_rows FROM table_name WHERE '.$where;
// Now get the current page of text
$pageSQL = 'SELECT * FROM table_name WHERE '.$where.' LIMIT '.($page*$perPage).','.$perPage;
$query = mysql_query($countSQL);
$pageCount = $query['total_rows'] / $perPage;
$query = mysql_query($pageSQL);

Now you'll have a populated query object and a pageCount variable telling you how many pages there are.

Add something like this to the end of the page, under your table to render a list of pages:

<p>
Page:
<?php

for($i = 1; $i <= $pageCount; $i++) {
    echo '<a href="?p='.$i.'">'.$i.'</a> ';
}
?>
</p>

This code isn't tested but should be very close to what you need to do.

See this page for info about MySQL LIMIT http://www.mysqltutorial.org/mysql-limit.aspx

[diafol]

https://www.daniweb.com/programming/web-development/tutorials/499320/common-issues-with-mysql-and-php item #6