Jump to content

paginate search results

ianhaney

By ianhaney
in PHP Coding Help

 Share

Recommended Posts

ianhaney Advanced Member

ianhaney

Posted
Posted

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

Link to comment
https://forums.phpfreaks.com/topic/300742-paginate-search-results/
Share on other sites
Psycho Prolific Member

Psycho

Posted
Posted

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 Prolific Member

mac_gyver

Posted
Posted (edited)

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 by mac_gyver
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.