Scroll up down large data

Beginners - Learning PHP
#1

having an issue with viewing numeric data, mainly deposit, withdraw and balance, with balance calculated from deposit & withdraw filed.
Data is shown correctly along with balances but with large amount of data the display needs to be scrolled.

What is required is that it is shown page wise (say 20 lines or so), along with NEXT & PREVIOUS buttons. This is also working, but the problem is with the balances, when you press the NEXT button, the last balance is not taken into consideration but the fresh one is, needless to say the balances are all wrong.

All the current data should be on top and not at the bottom.!

<?php 
session_start();
setlocale(LC_MONETARY, 'en_IN');
date_default_timezone_set('Asia/Kolkata');
include "config.php";
?>
<!DOCTYPE html>
<html lang="en">

<head>
    <meta charset="UTF-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1.0" />
    <link rel="stylesheet" href="style.css" type="text/css" />
</head>

<body>
<div class="container">
    <div class="parent">
        <?php include "head.php"; ?>
            
             <aside class="section leftSidebar">
             <?php include "left.php"; ?>
            </aside>

            <main class="section main">

                <table class="tg">
                    <colgroup>
                        
                        <col style="width: 90px"  />
                        <col style="width: 200px" />
                        <col style="width: 100px" />
                        <col style="width: 100px" />
                        <col style="width: 120px" />
                        <col style="width: 90px" />
                    </colgroup>
                    <thead>
                       
                    </thead>
                    <tbody>
                        <tr>

                            <td class="tg-h2xt">Date</td>
                            <td class="tg-h2xt">Particulars</td>
                            <td class="tg-h2xt">Deposit</td>
                            <td class="tg-h2xt">Withdraw</td>
                            <td class="tg-h2xt">Balance</td>
                            <td class="tg-h2xt">Action</td>
                        </tr>
                        <tr>

<?php 

if (!isset($_GET['startrow']) or !is_numeric($_GET['startrow'])) {
    $startrow = 0;
  } else {
    $startrow = (int)$_GET['startrow'];
  }
    $sql = "SELECT * FROM sundry ORDER BY sdate ASC LIMIT $startrow,22";
    $result_data=mysqli_query($conn, $sql);
       while ($row_data = mysqli_fetch_assoc($result_data)) {

        $start_line = $start_line+1;

            
            $date=$row_data['sdate'];
            $date=strtotime($date);
            $sdate=date('d-m-yy',$date);
            $spart=$row_data['spart'];
            $deposit=$row_data["sdeposit"];
            $withdraw=$row_data["swithdraw"];
            $dep_sum +=$deposit;
            $wit_sum +=$withdraw;
            $total=$balance+$dep_sum-$wit_sum;


        echo "<tr>";
        echo "<td class='tg-0pky'>" ."$sdate". "</td>";
        echo "<td class='tg-0pky'>" .$row_data["spart"]. "</td>";
        echo "<td class='tg-dvpl'>" .number_format($deposit,2). "</td>";
        echo "<td class='tg-dvpl'>" .number_format($withdraw,2). "</td>";
        echo "<td class='tg-dvpl'>" .money_format('%!i',$total). "</td>";
                        
        echo "<td class='tg-dvpl'><form class='new' action= ''  method='POST'><input type='hidden'  name='id'  value=" .$row_data['sid']. "><input type='submit'  class='cancelbtn1' name='delete' value='DELETE''></form></td>";
            if(isset($_POST['id'])) {
            $id=$_REQUEST['id'];
            $query = "DELETE FROM sundry WHERE sid=$id ";
            $result = mysqli_query($conn,$query);
            header("Location: sundry.php");
   
    }
}            
        echo "<div>";
echo "SUNDRY EXPENSES";
echo '<a href="'.$_SERVER['PHP_SELF'].'?startrow='.($startrow+22).'"><button style=margin-left:34.0em;width:10%; >Next</button></a>';
$prev = $startrow - 22;
echo "</table>";

if ($prev >= 0) {
echo "<div style=width:10%; >";
    echo '<a href="'.$_SERVER['PHP_SELF'].'?startrow='.$prev.'"><button style=margin-left:46.0em; >Previous</button</a>';
    $balance=$total;
}
?>
        </div>
</main>
</div>
<aside class="section rightSidebar">
<table class="tg">
</tbody>
</table>
</aside>

<footer class="section footer ">
<form name="sundry" action="sinsert.php" method="POST"> 
Transaction Date:
<input type="date" name="s_date"  required=" " />  

Particulars:
<input type="text"  name="s_part"  required=" " /> 
Transaction Amount:
<input type="number" placeholder="00.00" name="s_amount" required=" " />

<input class= 'blue' type=submit name="deposit" value="Deposit" />
<input class = 'red' type=submit name="withdraw" value="Withdraw" />
<input class = 'green'type=reset  name="Reset" value="Reset"/>
</form> 
</footer>
</div>
</div>
</body>
<?php
?>
</html>
#2

You need to read up on pagination where you can control how much data is to be displayed.

Here’s an example of my website (this is a method in a Class):

    $query = 'SELECT id, user_id, author, page, thumb_path, path, post, page, Model, ExposureTime, Aperture, ISO, FocalLength, heading, content, DATE_FORMAT(date_added, "%M %e, %Y") as date_added, date_added as myDate FROM cms ORDER BY myDate DESC LIMIT :perPage OFFSET :blogOffset';
    $stmt = static::pdo()->prepare($query); // Prepare the query:
    $stmt->execute([':perPage' => $perPage, ':blogOffset' => $offset()]); // Execute the query with the supplied data:
    $result = $stmt->fetchAll(PDO::FETCH_OBJ);
    return $result;

Here’s something that makes it easier to understand:

/*
 * Pagination Code
 */
$current_page = htmlspecialchars($_GET['page'] ?? 1); // Current Page Location:
$per_page = 3; // Total articles per page
$total_count = $journal::countAll(); // Total articles in database table:
$pagination = new Pagination($current_page, $per_page, $total_count);

Like I said find an easy to follow tutorial on Pagination as it should help you.

#3

Thanks for your reply, pagination is not the problem, the problem is when we go to page 2 the balance (deposit-withdraw) of page 1 is not taken into account into page 2, balance=deposit-withdraw starts of page 2 without taking into account the earlier balance!.

$per_page_record = 20;
if (isset($_GET["page"])) {    
$page  = $_GET["page"];    
}    else {    
$page=1;    
}    
$start_from = ($page-1) * $per_page_record;     
$query = "SELECT * FROM sundry ORDER BY sdate ASC LIMIT $start_from, $per_page_record";     
$rs_result = mysqli_query ($conn, $query);    
?>    
<div class="container">   
<table class="table table-striped table-condensed table-bordered">   
<tbody>   
<?php     
while ($row = mysqli_fetch_array($rs_result)) {

$date=$row['sdate'];
$date=strtotime($date);
$sdate=date('d-m-yy',$date);



$spart=$row['spart'];
$deposit=$row["sdeposit"];
$withdraw=$row["swithdraw"];
$dep_sum +=$deposit;
$wit_sum +=$withdraw;

if($page=1){
$balance=$dep_sum-$wit_sum;
$total=$balance;

if($page>1){
  $balance=$total+$dep_sum-$wit_sum;
}


}
echo "<tr>";
echo "<td>" .$row['sdate']. "</td>";
echo "<td>" .$row["spart"]. "</td>";     
echo "<td>" .$row["sdeposit"]. "</td>";   
echo "<td>" .$row["swithdraw"]. "</td>";   
echo "<td>" .$balance. "</td>";
echo  "</tr>";     


}

if($page=1){
echo   $balance;

if ($page>1){
  echo $total;
}
}


?>     
</tbody>   
</table>   

<div class="pagination">    
<?php  
$query = "SELECT COUNT(*) FROM sundry";     
$rs_result = mysqli_query($conn, $query);     
$row = mysqli_fetch_row($rs_result);     
$total_records = $row[0];     
$total_pages = ceil($total_records / $per_page_record);     
$pagLink = "";       
if($page>=2){   
echo "<a href='test.php?page=".($page-1)."'>  Prev </a>";   
            }       
for ($i=1; $i<=$total_pages; $i++) {   
if ($i == $page) {   
$pagLink .= "<a class = 'active' href='test.php?page=" .$i."'>".$i." </a>";   
 }  else  {   
$pagLink .= "<a href='test.php?page=".$i."'> ".$i." </a>";     
}   
            };     
            echo $pagLink;   
            if($page<$total_pages){   
                echo "<a href='test.php?page=".($page+1)."'>  Next </a>";   
            }   
          ?>    
</div>
#4

Web servers are stateless. The only input variable your code has to tell it what to do is the $_GET[‘page’] value.

If the following query gets the data for the requested page -

SELECT * FROM sundry ORDER BY sdate ASC LIMIT $start_from, $per_page_record

How would you query to get the SUM() of the values up to the start of that data? How about a query with a LIMIT one less than $start_from?

The following logic/query should work -

// if greater than the 1st page, get the total of all the previous rows
if($page > 1)
{
	$offset = $start_from - 1;
	$query = "SELECT SUM(sdeposit - swithdraw) as total
	FROM sundry
	ORDER BY sdate
	LIMIT $offset";
	// execute and fetch the $total from the above query

}
else
{
	// if on the 1st page, the starting total is zero
	$total = 0;
}

At this point $total is the starting value to use in the while() {} loop logic.

#5

OP, Your code is vulnerable to a Directory Traversal Attack.
NEVER TRUST USER INPUT!

#6

Thanks got it working, now how to have it in such a way that the page that opens is the last (current) page?
I tried this
header(“Location:{$_SERVER[‘PHP_SELF’]}?page=$total_pages”);
where $total_pages = ceil($total_records / $per_page_record);
but it has no effect!

Sponsor our Newsletter | Privacy Policy | Terms of Service