Splitting table data into pages-CodePudding

I have the code to fetch database into html table. It's quite big, so I want to split it into pages - let's say 10 rows per page. Is there a good method to do it? Please help, I'm not very experienced in coding. Thank you!

<html>
<head>
<title></title>
<style>
    
<?php include 'style.css'; ?>

</style>
</head>
<body>

<?php
    $conn = mysqli_connect("localhost","root","","table_data");
    if (mysqli_connect_errno()){
        echo "Failed to connect to MySQL: " . mysqli_connect_error();
    }
    
    
    $result = mysqli_query($conn, "SELECT id, user, userurl FROM table ORDER BY id ASC LIMIT 100");
    $i = 0;
    $table_grid = '<table border="0" cellpadding="0">';
    while($row = mysqli_fetch_array($result)){
        $id = $row['id'];
        $owner = $row['user'];
        $userurl = $row['userurl'];
        
        if($i % 10==0){
            $table_grid .= '<tr><td>
            <h3>'.$id.'</h3>
            <h3>'.$user.'</h3>
            <h3>'.$userurl.'</h3>
            </td>';
        }else{
            $table_grid .='<td>
            <h3>'.$id.'</h3>
            <h3>'.$user.'</h3>
            <h3>'.$userurl.'</h3>
            </td>';
        }
        $i  ;
    }
    $table_grid .= '</tr></table>';
?>
    <?php echo $table_grid; ?>

</body>
</html>

CodePudding user response：

you should set a pagination for your code. you may use it at your front end code via datatable.js or use server side pagination using OFFSET AND LIMIT.

1- datatable(Front-End pagination):

add these to your codes (datatables and jquery):

<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.6.0/jquery.js" integrity="sha512-n/4gHW3atM3QqRcbCn6ewmpxcLAHGaDjpEBu4xZd47N0W2oQ 6q7oc3PXstrJYXcbNU1OHdQ1T7pAP gi5Yu8g==" crossorigin="anonymous" referrerpolicy="no-referrer"></script>
    <link href="//cdn.datatables.net/1.11.5/css/jquery.dataTables.min.css" rel="stylesheet" media="all">
    <script src="//cdn.datatables.net/1.11.5/js/jquery.dataTables.min.js"></script>
<script>
$(document).ready( function () {
    $('table').DataTable();
} );
</script>

2- Backend:

change your query like:

<?php
/* start pagination codes */
    $perpage = 5;

    if(isset($_GET["page"])){
    $page = intval($_GET["page"]);
    }
    else {
    $page = 1;
    }
    $calc = $perpage * $page;
    $start = $calc - $perpage;
    $conn = mysqli_connect("localhost","root","","table_data");
    if (mysqli_connect_errno()){
        echo "Failed to connect to MySQL: " . mysqli_connect_error();
    }
    
    
    $result = mysqli_query($conn, "SELECT id, user, userurl FROM table ORDER BY id ASC Limit $start, $perpage");
    ?>

and then use:

<?php

if(isset($page))

{

$result = mysqli_query($conn,"select Count(*) As Total from table");

$rows = mysqli_num_rows($result);

if($rows)

{

$rs = mysqli_fetch_assoc($result);

$total = $rs["Total"];

}

$totalPages = ceil($total / $perpage);

if($page <=1 ){

echo "<span id='page_links' style='font-weight: bold;'>Prev</span>";

}

else

{

$j = $page - 1;

echo "<span><a id='page_a_link' href='?page=$j'>< Prev</a></span>";

}

for($i=1; $i <= $totalPages; $i  )

{

if($i<>$page)

{

echo "<span><a id='page_a_link' href='?page=$i'>$i</a></span>";

}

else

{

echo "<span id='page_links' style='font-weight: bold;'>$i</span>";

}

}

if($page == $totalPages )

{

echo "<span id='page_links' style='font-weight: bold;'>Next ></span>";

}

else

{

$j = $page   1;

echo "<span><a id='page_a_link' href='?page=$j'>Next</a></span>";

}

}

?>