ianhaney Posted February 5, 2016 Share Posted 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 Quote Link to comment Share on other sites More sharing options...
ianhaney Posted February 5, 2016 Author Share Posted February 5, 2016 Ahh ok, how would the correct way, sorry, I'll try and google it in the mean time Quote Link to comment Share on other sites More sharing options...
Psycho Posted February 5, 2016 Share Posted 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 Quote Link to comment Share on other sites More sharing options...
mac_gyver Posted February 5, 2016 Share Posted February 5, 2016 (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 February 5, 2016 by mac_gyver Quote Link to comment Share on other sites More sharing options...
ianhaney Posted February 6, 2016 Author Share Posted February 6, 2016 Oh right ok, I'll look into cleaning it up Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.