-
paginate this data
Hi
This works perfect in terms of retrieving the data between two dates but I am unsure on how to paginate this data when I select two dates to display the data between the dates I select
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;
}
require_once('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 COUNT(*) FROM purchased_software");
$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();
}
}
}
?>
<!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>Sold By</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><?php echo $row['sold_by']; ?></td>
<td><a href="add-update-software-sales.php?id=<?= $row['id']; ?>">Edit</a></td>
</tr>
<?php endforeach; ?>
</tbody>
</table>
<div class="box-right" style="height: 90px !important">
<div class="box-middle-content">
<?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>
-
I'm not sure what you're asking.
So you can select data between 2 dates, right? - Are you therefore just asking how to paginate that selected data? If so, maybe this will help (there's a pagination script half way down) http://www.dynamicdrive.com/forums/e...ith-Pagination
-
Your best tool to get this working is a pencil and paper. Stay away from the keyboard until you understand the required data flow. Here's some tips:
You have two pages 'view-software-sales.php' and 'search_data.php'. The second page ('search_data.php') is initially called from 'view-software-sales.php' and subsequently recursively from itself.
The first call to 'search_data.php' is from 'view-software-sales.php' and carries two parameters 'd1' & 'd2'. If these two parameters exist, the database is searched using them and an array of data returned. As part of the first call page 'search_data.php' needs to establish how large the array of data returned will be. In order prevent repeatedly counting the size of the returned data, there needs to be an additional parameter returned when the call is recursive. The ideal value here is the number of pages calculated in the first call. That will indicate to 'search_data.php' that this is not the first call and it already knows how many pages of results it should be paginating.
So, recursive calls to 'search_data.php' must carry four parameters. The first two of these should be the number of pages established in the first call ('size') and the desired page to display next ('page'). OK so far, but what else is needed. Well, your database query requires two dates to be recovered from the URI before it will execute any query and if those are missing the query isn't executed!
Now we can establish what blocks are needed in 'search_data.php' as follows:
1. Connect users and database (already present).
2. Detect if 'size' is present in the URI and if so use it as well as 'page' from the URI. Otherwise calculate the value of 'size' and set 'page' to 1.
3. Using 'd1' and 'd2', recover the required set of data and display it.
4. Create the links to the other pages remembering to add all four parameters 'size', 'page', 'd1' and 'd2'.
5. Any other stuff you need on the page.
Hope that helps.
-
Here's a quick and dirty example I knocked up this morning. It uses the same dummy database I built for your SMS page a couple of weeks ago. You should only need to change the database connection line at the top of the page. If the page is called with no parameters it will just display the "New Search" section.
Note that I had to use mysqli_num_rows() to evaluate the number of rows in the retrieved data set as 'COUNT' was producing an error in XAMPP mySql. I don't know what the problem is yet, but I'm working on it. If 'COUNT' works for you then it's probably better to use that.
Edited to add: The problem with COUNT is fixed. The version of MariaDB bundled with XAMPP produces an error if there is a space between 'COUNT' and '('. It works if the space is deleted.
Copy the code and save it as 'pages.php'.
PHP Code:
<?php
$dbConn = mysqli_connect("localhost" , "root", "", "test") or die("Check connection parameters!");
?>
<!doctype html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="Author" content="">
<meta name="Keywords" content="">
<meta name="Description" content="">
<title>Paginate data</title>
<script type="text/javascript">
function stopRKey(evt) {
var evt = (evt) ? evt : ((event) ? event : null);
var node = (evt.target) ? evt.target : ((evt.srcElement) ? evt.srcElement : null);
if ((evt.keyCode == 13) && (node.type=="text")) {return false;}
}
document.onkeypress = stopRKey;
</script>
<style>
table, th, td {
border: 2px solid black;
border-collapse:collapse;
}
</style>
</head>
<body>
<?php
$data = [];
if (isset($_GET["rows"])) { // If variable 'rows' is in the URI, then use it.
$per_page = $_GET["rows"];
} else {
$per_page = 10;
}
if (isset($_GET["pfx"])): // If variable 'pfx' is in the URI, then use it.
$pfx = $_GET["pfx"]; // Otherwise go to bottom of page and just display
// 'New Search'.
echo '<h2>Paginate records from database</h2>';
if (isset($_GET["page"])) { // If variable 'page' is in the URI, then use it,
$page = $_GET["page"]; // as this is a recursive call.
} else {
$page = 1; // Otherwise set to page zero.
}
if (isset($_GET["size"])) { // if variable 'size' is in the URI, then use it.
$size = $_GET["size"];
} else {
$query = "SELECT id FROM repairs WHERE customer_phone LIKE '%".$pfx."%'";
$result = mysqli_query($dbConn, $query);
if ($result) {
$size = ceil((mysqli_num_rows($result))/$per_page);
}
}
$start_from = ($page - 1) * $per_page;
$result = mysqli_query($dbConn, "SELECT id, customer_name, customer_phone, repair_details FROM repairs WHERE customer_phone LIKE '%".$pfx."%' LIMIT $start_from, $per_page");
if ($result) {
$data = mysqli_fetch_all($result, MYSQLI_ASSOC);
}
?>
<table>
<thead>
<tr>
<th style="width:3em;">ID</th>
<th style="width:20em;">Customer Name</th>
<th style="width:10em;">Customer Phone</th>
<th style="width:30em;">Repair Details</th>
</tr>
</thead>
<tbody>
<?php
echo '<p><strong>Page '. $page .' of '.$size.'.</strong></p>';
foreach ($data as $row): ?>
<tr>
<td><?php echo $row['id']; ?></a></td>
<td><?php echo $row['customer_name']; ?></td>
<td><?php echo $row['customer_phone']; ?></td>
<td><?php echo $row['repair_details']; ?></td>
</tr>
<?php endforeach; ?>
</tbody>
</table>
<?php
$urlpfx = urlencode($pfx);
if($size > 1) {
echo '<p><br /><br />';
for($i=1; $i<=$size; $i++) {
echo '<button style="margin-right:0.5em;"><a href="pages.php?page='.$i.'&size='.$size.'&rows='.$per_page.'&pfx='.$urlpfx.'">'.$i.'</a></button>';
}
}
echo '<h4>New Search</h4>';
else: // End of 'if (isset($_GET["pfx"])):' from line 43.
echo '<h2>New Search</h2>';
endif; // End of 'if (isset($_GET["pfx"])): else:' on line 43.
?>
<form action="pages.php" method="get">
<p>Search for: <input type="text" name="pfx" value="+44" maxlength="8" /></p>
<p>Rows per page: <input type="text" name="rows" value="10" maxlength="4" /></p>
<p><input type="submit" /></p>
</form>
</body>
</html>
-
So sorry for my late reply, thank you for the script styxlawyer
Is there a way it can include the from and to dates in that script?
-
Simply change the database connection and substitute your two dates for the phone prefix I used in the example. As I said before, I can't do it for you as I don't have access to your database and know nothing of the structure of the table.
-
I changed the db info and added the two date php variables in but I am just getting a blank white page with no errors?
Code:
<?php
ini_set('display_startup_errors',1);
ini_set('display_errors',1);
error_reporting(-1);
$dbConn = mysqli_connect("localhost" , "dbname", "password", "dbname") or die("Check connection parameters!");
?>
<!doctype html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Paginate data</title>
<script type="text/javascript">
function stopRKey(evt) {
var evt = (evt) ? evt : ((event) ? event : null);
var node = (evt.target) ? evt.target : ((evt.srcElement) ? evt.srcElement : null);
if ((evt.keyCode == 13) && (node.type=="text")) {return false;}
}
document.onkeypress = stopRKey;
</script>
<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>
<?php include("nav-menu.php"); ?>
<div id="logo">
<img src="images/logo/it-done-right.jpg" alt="" title="">
</div>
<?php
$data = [];
if (isset($_GET["rows"])) { // If variable 'rows' is in the URI, then use it.
$per_page = $_GET["rows"];
} else {
$per_page = 5;
}
if (isset($_GET["d1"])): // If variable 'pfx' is in the URI, then use it.
$d1 = $_GET["d1"]; // Otherwise go to bottom of page and just display
// 'New Search'.
if (isset($_GET["d2"])): // If variable 'pfx' is in the URI, then use it.
$d2 = $_GET["d2"]; // Otherwise go to bottom of page and just display
// 'New Search'.
echo '<h2>Paginate records from database</h2>';
if (isset($_GET["page"])) { // If variable 'page' is in the URI, then use it,
$page = $_GET["page"]; // as this is a recursive call.
} else {
$page = 1; // Otherwise set to page zero.
}
if (isset($_GET["size"])) { // if variable 'size' is in the URI, then use it.
$size = $_GET["size"];
} else {
$query = "SELECT id FROM purchased_software WHERE sales_month BETWEEN LIKE '%".$d1."%' AND '%".$d2."%' ORDER BY id";
$result = mysqli_query($dbConn, $query);
if ($result) {
$size = ceil((mysqli_num_rows($result))/$per_page);
}
}
$start_from = ($page - 1) * $per_page;
$result = mysqli_query($dbConn, "SELECT * FROM purchased_software WHERE sales_month BETWEEN LIKE '%".$d1."%' AND '%".$d2."%' LIMIT $start_from, $per_page");
if ($result) {
$data = mysqli_fetch_all($result, MYSQLI_ASSOC);
}
?>
<table>
<thead>
<tr>
<th>Software ID</th>
<th>Customer PayPal Email</th>
<th>Ebay Username</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>Sold By</th>
<th>Actions</th>
</tr>
</thead>
<tbody>
<?php
echo '<p><strong>Page '. $page .' of '.$size.'.</strong></p>';
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 $row['ebay_username']; ?></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><?php echo $row['sold_by']; ?></td>
<td><a href="add-update-software-sales.php?id=<?= $row['id']; ?>">Edit</a></td>
</tr>
<?php endforeach; ?>
</tbody>
</table>
<?php
$urld1 = urlencode($d1);
$urld2 = urlencode($d2);
if($size > 1) {
echo '<p><br /><br />';
for($i=1; $i<=$size; $i++) {
echo '<button style="margin-right:0.5em;"><a href="search-info.php?page='.$i.'&size='.$size.'&rows='.$per_page.'&d1='.$urld1.'&d2='.$urld2.'">'.$i.'</a></button>';
}
}
echo '<h4>New Search</h4>';
else: // End of 'if (isset($_GET["pfx"])):' from line 43.
echo '<h2>New Search</h2>';
endif; // End of 'if (isset($_GET["pfx"])): else:' on line 43.
?>
<form action="search-info.php" method="get">
<p>From : <input type="text" name="d1" class="tcal" value="" /></p>
<p>To: <input type="text" name="d2" class="tcal" value="" /></p>
<p>Rows per page: <input type="text" name="rows" value="10" maxlength="4" /></p>
<p><input type="submit" /></p>
</form>
</body>
</html>
-
1 Attachment(s)
Sorry my fault, was a little error in one of the lines, I got it working but it is not retrieveing any results?
the amended code is below
Code:
<?php
ini_set('display_startup_errors',1);
ini_set('display_errors',1);
error_reporting(-1);
$dbConn = mysqli_connect("localhost" , "dbname", "password", "dbname") or die("Check connection parameters!");
?>
<!doctype html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Paginate data</title>
<script type="text/javascript">
function stopRKey(evt) {
var evt = (evt) ? evt : ((event) ? event : null);
var node = (evt.target) ? evt.target : ((evt.srcElement) ? evt.srcElement : null);
if ((evt.keyCode == 13) && (node.type=="text")) {return false;}
}
document.onkeypress = stopRKey;
</script>
<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>
<?php include("nav-menu.php"); ?>
<div id="logo">
<img src="images/logo/it-done-right.jpg" alt="" title="">
</div>
<?php
$data = [];
if (isset($_GET["rows"])) { // If variable 'rows' is in the URI, then use it.
$per_page = $_GET["rows"];
} else {
$per_page = 5;
}
if (isset($_GET["d1"])): // If variable 'pfx' is in the URI, then use it.
$d1 = $_GET["d1"]; // Otherwise go to bottom of page and just display
echo '<h2>Paginate records from database</h2>';
if (isset($_GET["page"])) { // If variable 'page' is in the URI, then use it,
$page = $_GET["page"]; // as this is a recursive call.
} else {
$page = 1; // Otherwise set to page zero.
}
if (isset($_GET["size"])) { // if variable 'size' is in the URI, then use it.
$size = $_GET["size"];
} else {
$query = "SELECT id FROM purchased_software WHERE sales_month LIKE '%".$d1."%' ORDER BY id";
$result = mysqli_query($dbConn, $query);
if ($result) {
$size = ceil((mysqli_num_rows($result))/$per_page);
}
}
$start_from = ($page - 1) * $per_page;
$result = mysqli_query($dbConn, "SELECT * FROM purchased_software WHERE sales_month LIKE '%".$d1."%' LIMIT $start_from, $per_page");
if ($result) {
$data = mysqli_fetch_all($result, MYSQLI_ASSOC);
}
?>
<table>
<thead>
<tr>
<th>Software ID</th>
<th>Customer PayPal Email</th>
<th>Ebay Username</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>Sold By</th>
<th>Actions</th>
</tr>
</thead>
<tbody>
<?php
echo '<p><strong>Page '. $page .' of '.$size.'.</strong></p>';
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 $row['ebay_username']; ?></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><?php echo $row['sold_by']; ?></td>
<td><a href="add-update-software-sales.php?id=<?= $row['id']; ?>">Edit</a></td>
</tr>
<?php endforeach; ?>
</tbody>
</table>
<?php
$urld1 = urlencode($d1);
if($size > 1) {
echo '<p><br /><br />';
for($i=1; $i<=$size; $i++) {
echo '<button style="margin-right:0.5em;"><a href="search-info.php?page='.$i.'&size='.$size.'&rows='.$per_page.'&d1='.$urld1.'">'.$i.'</a></button>';
}
}
echo '<h4>New Search</h4>';
else: // End of 'if (isset($_GET["pfx"])):' from line 43.
echo '<h2>New Search</h2>';
endif; // End of 'if (isset($_GET["pfx"])): else:' on line 43.
?>
<form action="search-info.php" method="get">
<p>From : <input type="text" name="d1" class="tcal" value="" /></p>
<p>To: <input type="text" name="d2" class="tcal" value="" /></p>
<p>Rows per page: <input type="text" name="rows" value="10" maxlength="4" /></p>
<p><input type="submit" /></p>
</form>
</body>
</html>
I have attached a screenshot of how the page looks
Attachment 5821
-
I noticed it works if I don't put any date in using the datepicker in the search form and press submit, it returns the data and displays the pagination links but when click any of the pagination links like 2 or 3 for example, they don't work and does not take me to page 2 or 3
if can't get it working, I might just use a back to top button
would be nice to get it working but I can't work out the coding on how to get the results returned if select two dates using the from and why the pagination links are not working?
-
If it helps, below is the purchased_software db table structure
the column called sales_month stores the date in the format of YYYY/MM/DD
-- phpMyAdmin SQL Dump
-- version 4.0.7
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Feb 14, 2016 at 12:24 PM
-- Server version: 5.5.47
-- PHP Version: 5.3.28
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
--
-- Database: `dbname`
--
-- --------------------------------------------------------
--
-- Table structure for table `purchased_software`
--
CREATE TABLE IF NOT EXISTS `purchased_software` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`customer_pp_email` varchar(500) CHARACTER SET utf8 NOT NULL,
`ebay_username` varchar(500) CHARACTER SET utf8 NOT NULL,
`sales_month` date NOT NULL,
`software_title` varchar(500) CHARACTER SET utf8 NOT NULL,
`quantity` int(5) NOT NULL,
`total_sale` decimal(11,2) NOT NULL,
`ebay_fees` decimal(11,2) NOT NULL,
`paypal_fees` decimal(11,2) NOT NULL,
`software_cost` decimal(11,2) NOT NULL,
`profit` decimal(11,2) NOT NULL,
`notes` varchar(550) CHARACTER SET utf8 NOT NULL,
`status` varchar(850) CHARACTER SET utf8 NOT NULL,
`sold_by` varchar(400) CHARACTER SET utf8 NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=104 ;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
