Separating SQL Queries amongst several pages

Question

Been trying to work out how to do this for hours, i have managed to get queries using OFFSET and FETCH which will set the amount of results to display and at which number to start at but cant seem to work out how to actually use my pagination in my footer so it display on a new page.

very new to html, css, php and mysql so its a learn as you go but hard to find tutorials that make sense.

<?php


     $sql = "SELECT STEAM_ID, Hunters, Smoker, Boomers, Spitters, Jockeys, Charger,
    (Hunters + Smoker + Boomers + Spitters + Jockeys + Charger) as Total_Kills
     FROM Special_Infected_Kills ORDER BY Total_Kills DESC";

    $result = $conn->query($sql);

    if ($result = $conn->query($sql)) {
        echo "<table><tr>
        <th>Player</th>
        <th>Total Kills</th>
        <th>Hunter</th>
        <th>Jockey</th>
        <th>Charger</th>
        <th>Smoker</th>
        <th>Boomer</th>
        <th>Spitter</th>
        </tr>";

      // output data of each row
      while($row = $result->fetch_assoc()) {
      echo "<tr>
      <td>".$row["STEAM_ID"]."</td>
      <td>".$row["Total_Kills"]."</td>
      <td>".$row["Hunters"]."</td>
      <td>".$row["Boomers"]."</td>
      <td>".$row["Spitters"]."</td>
      <td>".$row["Jockeys"]."</td>
      <td>".$row["Charger"]."</td>
      <td>".$row["Smoker"]."</td>
      </tr>";
       }
  echo "</table>";
  } else {
  echo "0 results";
  }
 $conn->close();
 ?>

MY FOOTER

<footer>

<div class="center">
<div class="pagination">
  <a href="#">&laquo;</a>
  <a href="#" class="active">1</a>
  <a href="#">2</a>
  <a href="#">3</a>
  <a href="#">4</a>
  <a href="#">5</a>
  <a href="#">6</a>
  <a href="#">&raquo;</a>
</div>
</div>

<div class="createdby">
<p> Website built & designed by Blade </p>
</div>

</footer>

So pretty much just want the first 20 results on the first page then every 20 after that on another page by clicking the pagination in the footer

Answer 1

You mention FETCH in the question but it is unclear whether you refer to the javascript fetch api or some other thing not disclosed but the following might help solve the issue of paged results. Without the relevant database tables and data the following was tested with another table and recordset to produce paged results -seems to work OK. I'm sure that this can be adapted to work with the fetch api quite easily if required.

<?php

    /* count all records to begin pagination calculations */
    $sql='select count(*) as `rows` from `special_infected_kills`';
    $res=$db->query( $sql );
    
    $rs=$res->fetch_object();
    $rows=$rs->rows;                    // total number of records in table
    
    $pagesize=20;                       // results per page
    $pages=ceil( $rows / $pagesize );   // calculated total number of pages
    
    
    /*
        deduce or generate the current page. As we want to display ?page=1 rather than ?page=0
        in the querystring the hyperlinks for paging start at 1 but the recordset pages start 
        at zero. Hence subtracting 1 immediately from specified page.
        
        Use the Elvis operator to set alternative value if the filter fails.
    */
    $page=filter_input( INPUT_GET, 'page', FILTER_SANITIZE_NUMBER_INT ) ?: 1;
    $page--;
    
    // ensure the $page is within correct bounds
    if( $page < 0 )$page=1;
    if( $page > $pages )$page=$pages;
    
    
    // construct sql for paged results, supply limit clauses as placeholders.
    $sql = 'SELECT 
            STEAM_ID, Hunters, Smoker, Boomers, Spitters, Jockeys, Charger,
            ( Hunters + Smoker + Boomers + Spitters + Jockeys + Charger ) as `Total_Kills`
        FROM `Special_Infected_Kills` 
        ORDER BY `Total_Kills` DESC
        LIMIT ?, ?';
    
    
    // create the prepared statement, bind and run. Assign results to named variables.
    $stmt=$db->prepare( $sql );
    $stmt->bind_param('ss', $page, $pagesize );
    $res=$stmt->execute();
    $stmt->bind_result( $sid, $hunters, $smoker, $boomers, $splitters, $jockeys, $charger, $kills );
    
    
    
    // display the data for the current page
    $output='
    <table>
        <tr>
            <th>Player</th>
            <th>Total Kills</th>
            <th>Hunter</th>
            <th>Jockey</th>
            <th>Charger</th>
            <th>Smoker</th>
            <th>Boomer</th>
            <th>Spitter</th>
        </tr>';
    
    while( $rs=$stmt->fetch() ){
        $output .= sprintf('
        <tr>
            <td>%s</td>
            <td>%s</td>
            <td>%s</td>
            <td>%s</td>
            <td>%s</td>
            <td>%s</td>
            <td>%s</td>
            <td>%s</td>
        </tr>', $sid, $kills, $hunters, $jockeys, $charger, $smoker, $boomers, $splitters );
    }
    $output .= '
    </table>';
    
    

    /************************************************
        Links for First page, previous, next & last 
        are calculated differently.
    */
    $first= $page > 0 ? '<a class="paging" href="?page=1">First</a>' : '<span>First</span>';
    $prev = $page > 0 ? sprintf('<a class="paging" href="?page=%s">Previous</a>',( max( 1, $page ) ) ) : '<span>Previous</span>';
    $next = $page < $pages ? sprintf('<a class="paging" href="?page=%s">Next</a>',( min( $pages + 1, $page + 2 ) ) ) : '<span>Next</span>';
    $last = $page < $pages ? sprintf('<a class="paging" href="?page=%s">Last</a>', $pages + 1 ) : '<span>Last</span>';
    
    
    $footer=sprintf('
        <footer>
            <div class="center">
                <div class="pagination">
                
                    <!-- text links for prev/next etc -->
                    <div>%s | %s | %s | %s</div>
                    
                    <a class="paging" href="?page=%s">&laquo;</a>', $first, $prev, $next, $last, ( max( 1, $page ) ) );
    
    
    for( $i=1; $i <= $pages + 1; $i++ ){
        $active=$page == $i ? ' active' : '';
        $footer .= sprintf(' <a class="paging%2$s" href="?page=%1$d">%1$d</a> ', $i, $active );
    }
    
    
    
    $footer .= sprintf('
                <a class="paging" href="?page=%s">&raquo;</a>
            </div>
        </div>
        
        <div class="createdby">
            <p>Website built & designed by Blade</p>
        </div>
    </footer>', min( $pages + 1, $page + 2 ));
    
    
    
    // render the html
    echo $output, $footer;
    
?>