paginate search results

I did update the query line to the following

Code:

---

$result = $db->prepare("SELECT COUNT(*) FROM purchased_software WHERE sales_month BETWEEN :a AND :b ORDER BY id LIMIT 5 offset 0");

---

but when I ran the search, I get undefined index errors like the following

Notice: Undefined index: id in /home/sites/it-doneright.co.uk/public_html/admin/software-sales/search-data.php on line 129
Notice: Undefined index: customer_pp_email in /home/sites/it-doneright.co.uk/public_html/admin/software-sales/search-data.php on line 130
Notice: Undefined index: sales_month in /home/sites/it-doneright.co.uk/public_html/admin/software-sales/search-data.php on line 131 01/01/1970
Notice: Undefined index: software_title in /home/sites/it-doneright.co.uk/public_html/admin/software-sales/search-data.php on line 132
Notice: Undefined index: quantity in /home/sites/it-doneright.co.uk/public_html/admin/software-sales/search-data.php on line 133
Notice: Undefined index: total_sale in /home/sites/it-doneright.co.uk/public_html/admin/software-sales/search-data.php on line 134
Notice: Undefined index: ebay_fees in /home/sites/it-doneright.co.uk/public_html/admin/software-sales/search-data.php on line 135
Notice: Undefined index: paypal_fees in /home/sites/it-doneright.co.uk/public_html/admin/software-sales/search-data.php on line 136
Notice: Undefined index: software_cost in /home/sites/it-doneright.co.uk/public_html/admin/software-sales/search-data.php on line 137 £
Notice: Undefined index: profit in /home/sites/it-doneright.co.uk/public_html/admin/software-sales/search-data.php on line 138 £
Notice: Undefined index: notes in /home/sites/it-doneright.co.uk/public_html/admin/software-sales/search-data.php on line 139
Notice: Undefined index: status in /home/sites/it-doneright.co.uk/public_html/admin/software-sales/search-data.php on line 140
Notice: Undefined index: sold_by in /home/sites/it-doneright.co.uk/public_html/admin/software-sales/search-data.php on line 141

Yes, that's exactly I would expect from a query which is returning a COUNT. There will be no fields in the returned data just a number of records.

ok, is there a way I can then do the COUNT and the fields to return the data in the same query or would it need to be multiple queries?

You will have to use two discrete queries, one to determine how many data rows will be returned and the second to actually get that data.

Quote:

---

Originally Posted by styxlawyer

You will have to use two discrete queries, one to determine how many data rows will be returned and the second to actually get that data.

---

I did think that, how would the script look for that in PDO?

I gave it a go as thought it might be like the following but didn't work

Code:

---

$result = $db->prepare("SELECT COUNT(*) FROM purchased_software");
$result = $db->prepare("SELECT * FROM purchased_software WHERE sales_month BETWEEN :a AND :b ORDER BY id LIMIT 5 offset 0");

---

The query will be identical. PDO is just an object-oriented interface to mysqli. I think you need to read this page before you try to do any more.

I have looked at the link you provided and got the script from it and just got one little issue, it comes up with the error message below

Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given in /home/sites/it-doneright.co.uk/public_html/admin/software-sales/search-data.php on line 104

on 104 is the following line

Code:

---

while($row = mysqli_fetch_array($query, MYSQLI_ASSOC)){

---

This is my whole code

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;
}

include_once("mysqli_connection.php");

// This first query is just to get the total count of rows
$sql = "SELECT COUNT(id) FROM purchased_software";
$query = mysqli_query($con, $sql);
$row = mysqli_fetch_row($query);
// Here we have the total row count
$rows = $row[0];
// This is the number of results we want displayed per page
$page_rows = 5;
// This tells us the page number of our last page
$last = ceil($rows/$page_rows);
// This makes sure $last cannot be less than 1
if($last < 1){
        $last = 1;
}
// Establish the $pagenum variable
$pagenum = 1;
// Get pagenum from URL vars if it is present, else it is = 1
if(isset($_GET['pn'])){
        $pagenum = preg_replace('#[^0-9]#', '', $_GET['pn']);
}
// This makes sure the page number isn't below 1, or more than our $last page
if ($pagenum < 1) {
    $pagenum = 1;
} else if ($pagenum > $last) {
    $pagenum = $last;
}
// This sets the range of rows to query for the chosen $pagenum
$limit = 'LIMIT ' .($pagenum - 1) * $page_rows .',' .$page_rows;

$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");
        $result->bindParam(':a', $start_date);
        $result->bindParam(':b', $end_date);
        $exec = $result->execute();
        if ($exec) {
            $data = $result->fetchAll();
        }
    }
}

// This is your query again, it is for grabbing just one page worth of rows by applying $limit
$sql = "SELECT id, customer_pp_email, sales_month, software_title, quantity, total_sale, ebay_fees, paypal_fees, cost_price, profit, notes, status, sold_by FROM purchased_software sales_month BETWEEN :a AND :b ORDER BY id ORDER BY id DESC $limit";
$query = mysqli_query($con, $sql);

// This shows the user what page they are on, and the total number of pages
$textline1 = "Sales (<b>$rows</b>)";
$textline2 = "Page <b>$pagenum</b> of <b>$last</b>";
// Establish the $paginationCtrls variable
$paginationCtrls = '';
// If there is more than 1 page worth of results
if($last != 1){
        /* First we check if we are on page one. If we are then we don't need a link to
          the previous page or the first page so we do nothing. If we aren't then we
          generate links to the first page, and to the previous page. */
        if ($pagenum > 1) {
        $previous = $pagenum - 1;
                $paginationCtrls .= '<a href="'.$_SERVER['PHP_SELF'].'?pn='.$previous.'">Previous</a> &nbsp; &nbsp; ';
                // Render clickable number links that should appear on the left of the target page number
                for($i = $pagenum-4; $i < $pagenum; $i++){
                        if($i > 0){
                        $paginationCtrls .= '<a href="'.$_SERVER['PHP_SELF'].'?pn='.$i.'">'.$i.'</a> &nbsp; ';
                        }
            }
    }
        // Render the target page number, but without it being a link
        $paginationCtrls .= ''.$pagenum.' &nbsp; ';
        // Render clickable number links that should appear on the right of the target page number
        for($i = $pagenum+1; $i <= $last; $i++){
                $paginationCtrls .= '<a href="'.$_SERVER['PHP_SELF'].'?pn='.$i.'">'.$i.'</a> &nbsp; ';
                if($i >= $pagenum+4){
                        break;
                }
        }
        // This does the same as above, only checking if we are on the last page, and then generating the "Next"
    if ($pagenum != $last) {
        $next = $pagenum + 1;
        $paginationCtrls .= ' &nbsp; &nbsp; <a href="'.$_SERVER['PHP_SELF'].'?pn='.$next.'">Next</a> ';
    }
}
$list = '';
while($row = mysqli_fetch_array($query, MYSQLI_ASSOC)){
        $id = $row["id"];
        $firstname = $row["customer_pp_email"];
        $lastname = $row["sales_month"];
        $datemade = $row["software_title"];
        $datemade = strftime("%b %d, %Y", strtotime($datemade));
        $list .= '<p><a href="testimonial.php?id='.$id.'">'.$firstname.' '.$lastname.' Testimonial</a> - Click the link to view this testimonial<br>Written '.$datemade.'</p>';
}
// Close your database connection
mysqli_close($con);


?>

<!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>
       
        <div>
  <h2><?php echo $textline1; ?> Paged</h2>
  <p><?php echo $textline2; ?></p>
  <p><?php echo $list; ?></p>
  <div id="pagination_controls"><?php echo $paginationCtrls; ?></div>
</div>

</body>
</html>

---

There's far too much extraneous stuff in that page to make any sense of it at all.

I suggest you start off with only the code from the page I linked to and modify the queries to match your database. Once you have that working then you can go on and add the additional code required for the other queries. You are trying to jump too far ahead before you have the basic page working.

KISS.