paginate search results

ianhaney · February 5, 2016

Hi

I am trying to paginate my search results but keep getting the followung error

Warning: mysqli_num_rows() 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 155

below is the line on 155

$total_records = mysqli_num_rows($exec);

below is the whole script

<?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('connect.php');

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

$page = $_GET["page"];

}

else {

$page=1;

}

// Page will start from 0 and Multiple 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>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><a href="add-update-software-sales.php?id=<?= $row['id']; ?>">Edit</a></td>
                </tr>
            <?php endforeach; ?>
            </tbody>
        </table>
        
        <?php
$result = $db->prepare("select * from purchased_software");

$exec = $result->execute();
        if ($exec) {
             $data = $result->fetchAll();
        }
		
        // Count the total records
$total_records = mysqli_num_rows($exec);

//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

include('connect.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>

Thank you in advance

Ian

ianhaney · February 5, 2016

Ahh ok, how would the correct way, sorry, I'll try and google it in the mean time

Psycho · February 5, 2016

Looks like you are using PDO, not MySQLi_. PDO has the rowcount function. But, the manual states it may not work for SELECT queries on all databases. So, try it beforehand. You can always run a COUNT() query or loop through the results to get the count.

http://php.net/manual/en/pdostatement.rowcount.php

mac_gyver · February 5, 2016

your code is all over the place. in addition to mixing mysqli and PDO statements, you are making (apparently) two different PDO database connections AND the WHERE ... clause in your data retrieval query must be used in the total row count query so that the two queries match the same set of rows.

you should form the the WHERE ... clause in a php variable, then use that variable in both queries.

you should also get the total row count first, so that you can use it to limit the maximum page number so that a programming error or someone feeding your code invalid/large page numbers doesn't waste resources running the data retrieval query that will never match any data.

edit: you also have a problem with the pagination links and the date filtering, you need to propagate any selected date filtering in the pagination links so that the the code will properly select data on each page.

Edited February 5, 2016 by mac_gyver

ianhaney · February 6, 2016

Oh right ok, I'll look into cleaning it up

Sign In

paginate search results

Recommended Posts

ianhaney

Link to comment

Share on other sites

ianhaney

Link to comment

Share on other sites

Psycho

Link to comment

Share on other sites

mac_gyver

Link to comment

Share on other sites

ianhaney

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information