paginate search results

**styxlawyer** · 02-07-2016, 07:50 PM

OK, so you are getting data back from the the query. It's only the start point that seems to be the problem.

You seem to have two separate tabulated queries on that page. Which is the one that isn't working correctly?

**ianhaney** · 02-07-2016, 10:49 PM

Originally Posted by styxlawyer

OK, so you are getting data back from the the query. It's only the start point that seems to be the problem.

You seem to have two separate tabulated queries on that page. Which is the one that isn't working correctly?

Hi

Sorry for my late reply, yeah am getting data back from the query

I think it is this one that is not working correctly

PHP Code:


$result = $db->prepare("SELECT * FROM purchased_software WHERE sales_month BETWEEN :a AND :b ORDER BY id LIMIT $start_from, 1000000");

the data is not being paginated, I have the following

PHP Code:


$per_page=5;

if (isset($_GET["page"])) {



$page = $_GET["page"];



}



else {



$page=1;



}

but the $per_page is not being in the query, so do I need to do LIMIT $per_page or something in the query above?

**styxlawyer** · 02-07-2016, 10:54 PM

Putting the $per_page variable back into the query should limit it to 5 entries being returned for each query:

Code:

$result = $db->prepare("SELECT * FROM purchased_software WHERE sales_month BETWEEN :a AND :b ORDER BY id LIMIT $start_from, $per_page");

... but I thought you said earlier that wasn't working. I have a dummy database I created for your SMS problem, so I'll add a few more lines and try a few things.

**ianhaney** · 02-07-2016, 10:56 PM

I can't remember now, sorry, I'll try it now and see what happens, oh right ok cool, the SMS system is working now

**ianhaney** · 02-07-2016, 11:02 PM

right I tried it with the $per_page in the query and you was right, it did not work, it did make the records limited to 5 per page but it did not display page two etc link

below is the code for the query I now have the paginated links

Code:

$result = $db->prepare("SELECT * FROM purchased_software WHERE sales_month BETWEEN :a AND :b ORDER BY id LIMIT $start_from, $per_page");

Code:

//Using ceil function to divide the total records on per page
$total_pages = ceil($total_records / $per_page);

//Going to first page
echo "<center><a href='search-data.php?page=1'>".'First Page'."</a>";

for ($i=1; $i<=$total_pages; $i++) {

echo "<a href='search-data.php?page=".$i."'>".$i."</a>";
};
// Going to last page
echo "<a href='search-data.php?page=$total_pages'>".'Last Page'."</a></center>";

**ianhaney** · 02-08-2016, 03:53 PM

Hi

UPDATE

Just seeing if anyone has a update or script change to help me, with the $per_page in the query, the records are paginated to 5 on the first page, but all I get down the bottom is First Page1LastPage, I have around 25 records so should have 1 2 3 4 5 down the bottom for the pagination links, the code looks like the following

Hope someone can help me

Code:

<?php

ini_set('display_startup_errors',1);
ini_set('display_errors',1);
error_reporting(-1);

session_start();
if ($_SESSION['user']=='') {
    header("Location:../index.php");
} else {
    include("../config.php");
    $sql = $dbh->prepare("SELECT * FROM users WHERE id=?");
    $exec = $sql->execute(array($_SESSION['user']));
    $user = $exec ? $sql->fetch() : null;
}

require_once('connect.php');

$per_page=5;
if (isset($_GET["page"])) {

$page = $_GET["page"];

}

else {

$page=1;

}

// Page will start from 0 and Multiply by Per Page
$start_from = ($page-1) * $per_page;

$data = [];
if (isset($_GET['d1']) && isset($_GET['d2'])) {
    $d1 = strtotime($_GET['d1']);
    $d2 = strtotime($_GET['d2']);
    if ($d1 && $d2) {
        $start_date = date('Y-m-d', $d1);
        $end_date = date('Y-m-d', $d2);		
		$result = $db->prepare("SELECT * FROM purchased_software WHERE sales_month BETWEEN :a AND :b ORDER BY id LIMIT $start_from, $per_page");
        $result->bindParam(':a', $start_date);
        $result->bindParam(':b', $end_date);
        $exec = $result->execute();
        if ($exec) {
             $data = $result->fetchAll();
        }
    }
}

?>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html>
    <head> 
        <title>Search Data Between Two Dates</title>
        <link rel="stylesheet" type="text/css" media="screen" href="css/styles.css" />
        <link rel="stylesheet" type="text/css" href="css/tcal.css" />
        <script type="text/javascript" src="js/tcal.js"></script>
    </head>  

    <body>
        <div id="logo">
            <img src="images/logo/it-done-right.jpg" alt="" title="">
        </div>

        <script>
            var t;
            window.onload=resetTimer;
            document.onkeypress=resetTimer;

            function logout()
            {
                alert("You are now logged out.");
                location.href='../logout.php';
            }
            function resetTimer()
            {
                clearTimeout(t);
                t=setTimeout(logout,1800000) //logs out in 30 minutes
            }
        </script>

        <?php if ($user): ?>
            <div class='home-content'>
                <center>
                    <h2>Hello, <?= $user['username']; ?></h2>
                    <a href='../logout.php'>Log Out</a>
                    <br><br>
                    <a href='../index.php'>Home</a>
                </center>
            </div>
            <br>
        <?php endif; ?>

        <?php include("nav-menu.php"); ?>

        <br>

        <form action="search-data.php" method="get">
            From : <input type="text" name="d1" class="tcal" value="" />
            To: <input type="text" name="d2" class="tcal" value="" />
            <input type="submit" value="Search">
        </form>						

        <table class="view-repairs">
            <thead>
                <tr>
                    <th>Software ID</th>
                    <th>Customer PayPal Email</th>
                    <th>Sales Date</th>
                    <th>Software Title</th>
                    <th>Quantity</th>
                    <th>Total Sale</th>
                    <th>Ebay Fees</th>
                    <th>PayPal Fees</th>
                    <th>Cost Price</th>
                    <th>Profit</th>
                    <th>Notes</th>
                    <th>Status</th>
                    <th>Sold By</th>
                    <th>Actions</th>
                </tr>
            </thead>
            <tbody>
            <?php foreach ($data as $row): ?>
                <tr>
                   <td><a href="view-specific-software-sale.php?id=<?= $row['id']; ?>"><?php echo $row['id']; ?></a></td>
                   <td><?php echo $row['customer_pp_email']; ?></td>
                   <td><?php echo date("d/m/Y", strtotime($row['sales_month'])); ?></td>
                   <td><?php echo $row['software_title']; ?></td>
                   <td><?php echo $row['quantity']; ?></td>
                   <td><?php echo $row['total_sale']; ?></td>
                   <td><?php echo $row['ebay_fees']; ?></td>
                   <td><?php echo $row['paypal_fees']; ?></td>
                   <td><?php echo '£' . $row['software_cost']; ?></td>
                   <td><?php echo '£' . $row['profit']; ?></td>
                   <td><?php echo substr($row['notes'], 0, 25); ?></td>
                   <td><?php echo $row['status']; ?></td>
                   <td><?php echo $row['sold_by']; ?></td>
                   <td><a href="add-update-software-sales.php?id=<?= $row['id']; ?>">Edit</a></td>
                </tr>
            <?php endforeach; ?>

            </tbody>
        </table>
        
        <?php
        // Count the total records
$total_records = sizeof($data, 0);

//Using ceil function to divide the total records on per page
$total_pages = ceil($total_records / $per_page);

//Going to first page
echo "<center><a href='search-data.php?page=1'>".'First Page'."</a>";

for ($i=1; $i<=$total_pages; $i++) {

echo "<a href='search-data.php?page=".$i."'>".$i."</a>";
};
// Going to last page
echo "<a href='search-data.php?page=$total_pages'>".'Last Page'."</a></center>";
        
        ?>
        
    <div class="box-right" style="height: 90px !important">
	<div class="box-middle-content">

<?php

$data = [];
if (isset($_GET['d1']) && isset($_GET['d2'])) {
    $d1 = strtotime($_GET['d1']);
    $d2 = strtotime($_GET['d2']);
    if ($d1 && $d2) {
        $start_date = date('Y-m-d', $d1);
        $end_date = date('Y-m-d', $d2);

$result = $db->prepare("SELECT SUM(profit) FROM purchased_software WHERE sales_month BETWEEN :a AND :b");

$result->bindParam(':a', $start_date);
        $result->bindParam(':b', $end_date);
        $exec = $result->execute();
        if ($exec) {
             $data = $result->fetchAll();
        }
    }
}

foreach ($data as $row):

 echo 'Total Software Profit for ' . $start_date = date('d-m-Y', $d1) . ' AND ' . $end_date = date('d-m-Y', $d2) . '<br>' . '£' . $row['SUM(profit)'];

endforeach;

?>

</div>
</div>
        
        <a href="view-software-sales.php">BACK TO VIEW ALL SOFTWARE SALES</a>
        
    </body>
</html>

**styxlawyer** · 02-09-2016, 12:00 AM

I did say I would look at this for you and I have done so. Unfortunately, I do also have other things to do in my life, so your problem isn't top priority. However, I now know what your problem is. Here's a breakdown of why it doesn't work:

In line 47 you fetch some rows of data to a maximum of the value stored in '$per_page'.
In line 151 you evaluate how many rows of data you received and put it in '$total_records'. So the value stored in '$total_records' will not be any larger than the value stored in '$per_page'.
In line 154 you divide the value in '$total_records' by the value in '$per_page' and store the result in '$total_pages'. Thus '$total_pages' will either be 0 or 1 and never higher.

That's why you only see one link under your table and cannot retrieve any more records. At no point do you evaluate the total number of records in the database.

Hope that helps.

**ianhaney** · 02-09-2016, 11:13 AM

Hi

Thank you for replying, appreciate it, ahh yeah I know I understand mine isn't top priority

I guess then them lines 47, 151 and 154 need amending, is that right? or do I need to evaluate the total number of the records in the database as you say, if so how do I do that?

**ianhaney** · 02-09-2016, 11:16 AM

Would the total number of records in the db table look something like the following

Code:

$result = mysqli_query("SELECT count(*) FROM purchased_software");

I ma not sure though how to do it in PDO rather than mysqli?

**styxlawyer** · 02-09-2016, 11:16 AM

You are effectively using recursion in this page and, as a C/C++ programmer of some 30+ years experience, I can tell you that is one of the most difficult techniques to master. For recursion to work correctly each call to the function (or PHP page in your case) needs to know where it has come from as well as where it is going. The recursive calls to your page (clicked links) only contain one parameter which is where you wish to go. There is no information carried in the URL to tell the receiving page where the call has originated. You need to add a second parameter to your links to tell the PHP page at which set of results the browser is currently looking.

There are a number of other things which are going to catch you out. Firstly, you are extracting only some of the results from the database according to an arbitrary date range. Each set of results will probably not be starting at the first entry in the database, so you are going to have difficulty using '$start_from' in your query on line 42. The OFFSET attached to the LIMIT in the query relates to the first record in the database and not to the first record you have recovered. You need to evaluate '$start_from' such that it points to the database offset of the first recovered record and not the first record. Also, this query will probably fail if the recovered records aren't consecutive in the database as the value calculated in '$start_from' may point to a record which isn't one you require to display.

I suggest you rethink your page and do not use recursion. Make one query to the database, return all the results to the browser and then use JavaScript on the browser to paginate the results. This will be fine unless you are going to return tens of thousands of results in which case the resulting page will be huge but, since you are running this page on an internal server, that may not be a problem.