ianhaney
02-05-2016, 03:11 PM
Hi
I am trying to paginate search results but have the following error
Warning: mysqli_num_rows() expects parameter 1 to be mysqli_result, array given in /home/sites/it-doneright.co.uk/public_html/admin/software-sales/search-data.php on line 148
It is dividing up the results as 5 per page so that bit is working but unsure of what I am missing
below is my script
<?php
ini_set('display_startup_errors',1);
ini_set('display_errors',1);
error_reporting(-1);
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>
<?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
// Count the total records
$total_records = mysqli_num_rows($data);
//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
I am trying to paginate search results but have the following error
Warning: mysqli_num_rows() expects parameter 1 to be mysqli_result, array given in /home/sites/it-doneright.co.uk/public_html/admin/software-sales/search-data.php on line 148
It is dividing up the results as 5 per page so that bit is working but unsure of what I am missing
below is my script
<?php
ini_set('display_startup_errors',1);
ini_set('display_errors',1);
error_reporting(-1);
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>
<?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
// Count the total records
$total_records = mysqli_num_rows($data);
//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