how do i achieve pagination when retrieving and displaying rows from a database table

Question

I am retrieving a lot of items from mysql database and posting them to a html page how do i move some items automatically to a new page in php so that someone does not have to over scroll till the bottom of the html page

<?php
$query = "SELECT * FROM venueimage "; 
$result = mysqli_query($connect, $query);
while($row = mysqli_fetch_array($result)){

echo '
<div  class="container">
<div style="border : 2px solid #FDF3E7; background:#ECECEA;"           class="row">';
echo' <div  class="col-sm-4 border-right">';
echo "<u >"; echo "Venue ID :"; echo  $row['id']   ; echo "</u>";            echo "</br>";

echo "Venue name :"; echo  $row['venuename']   ; echo "</br>";

echo "Venue address :"; echo  $row['address']   ; echo "</br>";

echo "Venue phone :"; echo  $row['phone']   ; echo "</br>";

echo "Venue capacity :"; echo  $row['capacity']   ; echo "</br>";

echo "preferredfor :"; echo  $row['preferredfor']   ; echo "</br>";

echo "Venue price :"; echo  $row['price']   ; echo "</br>";

echo "brief description :"; echo  $row['briefdescription']   ; echo "       </br>";

echo '</div>';
echo '<div  class="col-sm-8">';
echo'
  
<img style="width: 790; height: 250"    src="data:image/jpeg;base64,'.base64_encode($row['venueimage']).'    "    />  ';
  echo '</div>';
  echo '</div>';
  echo "<hr>";
  echo '</div>';
  echo "</br>";

  }

  mysqli_close($connect);
  ?>

Answer 1

Sounds like you want to implement a solution to add paging (pagination). This is a common requirement and many solutions exist here on StackOverflow and other pages.

Check this out, it should get you started: Simple PHP Pagination script

try {

// Find out how many items are in the table
$total = $dbh->query('
    SELECT
        COUNT(*)
    FROM
        table
')->fetchColumn();

// How many items to list per page
$limit = 20;

// How many pages will there be
$pages = ceil($total / $limit);

// What page are we currently on?
$page = min($pages, filter_input(INPUT_GET, 'page', FILTER_VALIDATE_INT, array(
    'options' => array(
        'default'   => 1,
        'min_range' => 1,
    ),
)));

// Calculate the offset for the query
$offset = ($page - 1)  * $limit;

// Some information to display to the user
$start = $offset + 1;
$end = min(($offset + $limit), $total);

// The "back" link
$prevlink = ($page > 1) ? '<a href="?page=1" title="First page">&laquo;</a> <a href="?page=' . ($page - 1) . '" title="Previous page">&lsaquo;</a>' : '<span class="disabled">&laquo;</span> <span class="disabled">&lsaquo;</span>';

// The "forward" link
$nextlink = ($page < $pages) ? '<a href="?page=' . ($page + 1) . '" title="Next page">&rsaquo;</a> <a href="?page=' . $pages . '" title="Last page">&raquo;</a>' : '<span class="disabled">&rsaquo;</span> <span class="disabled">&raquo;</span>';

// Display the paging information
echo '<div id="paging"><p>', $prevlink, ' Page ', $page, ' of ', $pages, ' pages, displaying ', $start, '-', $end, ' of ', $total, ' results ', $nextlink, ' </p></div>';

// Prepare the paged query
$stmt = $dbh->prepare('
    SELECT
        *
    FROM
        table
    ORDER BY
        name
    LIMIT
        :limit
    OFFSET
        :offset
');

// Bind the query params
$stmt->bindParam(':limit', $limit, PDO::PARAM_INT);
$stmt->bindParam(':offset', $offset, PDO::PARAM_INT);
$stmt->execute();

// Do we have any results?
if ($stmt->rowCount() > 0) {
    // Define how we want to fetch the results
    $stmt->setFetchMode(PDO::FETCH_ASSOC);
    $iterator = new IteratorIterator($stmt);

    // Display the results
    foreach ($iterator as $row) {
        echo '<p>', $row['name'], '</p>';
    }

} else {
    echo '<p>No results could be displayed.</p>';
}

} catch (Exception $e) {
echo '<p>', $e->getMessage(), '</p>';
}

All credits to @Nev Stokes