Log in

View Full Version : paginate this data


ianhaney
02-10-2016, 02:08 PM
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



<?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 '&#163;' . $row['software_cost']; ?></td>
<td><?php echo '&#163;' . $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>' . '&#163;' . $row['SUM(profit)'];

endforeach;

?>

</div>
</div>

<a href="view-software-sales.php">BACK TO VIEW ALL SOFTWARE SALES</a>

</body>
</html>
Beverleyh
02-10-2016, 04:33 PM
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/entry.php?326-Display-Excerpts-From-an-RSS-Feed-with-Pagination
styxlawyer
02-10-2016, 06:15 PM
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.
styxlawyer
02-11-2016, 01:07 PM
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

$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>
ianhaney
02-13-2016, 08:37 PM
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?
styxlawyer
02-13-2016, 11:54 PM
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.
ianhaney
02-14-2016, 09:05 AM
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?



<?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>
ianhaney
02-14-2016, 09:43 AM
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



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

5821
ianhaney
02-14-2016, 10:59 AM
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?
ianhaney
02-14-2016, 12:28 PM
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 */;
ianhaney
02-14-2016, 03:47 PM
I got the pagination working if I just click submit to return all the rows but can't seem to get the date from and to working still, it returns no data and got a blank white page with no errors

Below is what I have currently



<?php

$dbConn = mysqli_connect("localhost" , "", "", "") 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>

<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

$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["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 LIKE '%".$d1."%' OR sales_month LIKE '%".$d2."%'";

$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."%' OR '%".$d2."%'
LIMIT $start_from, $per_page");
if ($result) {
$data = mysqli_fetch_all($result, MYSQLI_ASSOC);
}

?>

<table class="view-repairs">
<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 '<a href="search-info.php?page='.$i.'&size='.$size.'&rows='.$per_page.'&d1='.$urld1.'&d2='.$urld2.'">' . ' - '
.$i.'</a>';
}
}
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" value="" class="tcal" /></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>
ianhaney
02-14-2016, 04:49 PM
I have the error log off my server now and think the reason for the blank white page is the error below that is displayed in the error log

PHP Parse error: syntax error, unexpected end of file in /home/sites/it-doneright.co.uk/public_html/admin/software-sales/search-info.php on line 155: /home/sites/it-doneright.co.uk/public_html/admin/software-sales/search-info.php

On line 155 is </html>

I am going to google the issue and see if I can fix it
ianhaney
02-14-2016, 07:58 PM
I have a update, I got the page displaying now but just getting no results displayed

I tried to use the same d1 for both from and to date so wonder if that is the reason why no data is being returned so took the to field out and just left the from field but just having the from field left does not return any data when I search, I don't get what else to try, hope someone can help please



<?php

ini_set('display_startup_errors',1);
ini_set('display_errors',1);
error_reporting(-1);

$dbConn = mysqli_connect("localhost" , "", "", "") 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>

<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

$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["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'.

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."%'";

$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 class="view-repairs">
<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 '<a href="search-info.php?page='.$i.'&size='.$size.'&rows='.$per_page.'&d1='.$urld1.'">' . ' - '
.$i.'</a>';
}
}
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="d1" 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 am not getting any errors now in the error log

I get all the data returned if I don't enter any date
styxlawyer
02-14-2016, 10:18 PM
I think your problem might be something to do with this:


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


In PHP your best friend for debugging is the "echo" statement which will send something to the browser that you can see. Just report all the values the page is using and you'll find the answer very quickly.

Firstly look at the URI in your browser after the first pass and see what is returned for "&size=". If you're seeing no data, my bet is you'll see "&size=0" which means that line 66 in your source is evaluating to zero. Add a few echo statements through the code to report what PHP is finding at various steps and you'll soon have it sorted. For example change this:



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'.

echo '<h2>Paginate records from database</h2>';


to this:



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'.

echo '<h2>Paginate records from database</h2>';

echo '<p style="color:red">Using month: '.$d1.'</p>';


and change this:



$size = ceil((mysqli_num_rows($result))/$per_page);


to this:



$xyzzy = mysqli_num_rows($result);
echo '<p style="color:red">'.$xyzzy.' records found.</p>';
$size = ceil($xyzzy/$per_page);
ianhaney
02-14-2016, 10:34 PM
OK

I have altered the lines of script you mentioned and I choose in the from field 1-2-2016 and the to field 29-2-2016

and it returns the following

Paginate records from database

Using month: 29-02-2016

0 records found.

the url looks like the following

http://www.it-doneright.co.uk/admin/software-sales/search-info.php?d1=01-02-2016&d1=29-02-2016&rows=10
styxlawyer
02-15-2016, 10:35 AM
... and do you see what's wrong with that URI?



http://www.it-doneright.co.uk/admin/software-sales/search-info.php?d1=01-02-2016&d1=29-02-2016&rows=10


At a minimum you also need to have 'page' in there so that you know where you are.
ianhaney
02-15-2016, 10:40 AM
... and do you see what's wrong with that URI?



http://www.it-doneright.co.uk/admin/software-sales/search-info.php?d1=01-02-2016&d1=29-02-2016&rows=10


Is it because in the url is d1=01-02-2016&d1=29-02-2016

I am guessing it can't be the same, so it should be like the following

d1=01-02-2016&d2=29-02-2016

That right?
ianhaney
02-15-2016, 10:48 AM
I know I have done it wrong as got a blank white page again

I added the following lines in



if (isset($_GET["d1"])):
$d1 = $_GET["d1"];

//I ADDED THIS LINE IN
if (isset($_GET["d2"])):
$d2 = $_GET["d2"];


I amended this line below to include the $d2


echo '<p style="color:red">Using month: '.$d1.$d2.'</p>';


I amended the line below to include $d2



$query = "SELECT id FROM purchased_software WHERE sales_month LIKE '%".$d1."%' OR '%".$d2."%'";


I amended the line below to include the $d2



$result = mysqli_query($dbConn, "SELECT * FROM purchased_software WHERE sales_month LIKE '%".$d1."%' OR '%".$d2."%' LIMIT $start_from, $per_page");


I added the second line in to include the $d2


$urld1 = urlencode($d1);
$urld2 = urlencode($d2); //I ADDED THIS LINE IN


I amended the line below to include $urld2



echo '<a href="search-info.php?page='.$i.'&size='.$size.'&rows='.$per_page.'&d1='.$urld1.'&d2='.$urld2.'">' . ' - '
.$i.'</a>';


My form now looks like the following



<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>
ianhaney
02-15-2016, 11:03 AM
I found the issue, it was where I had the following



if (isset($_GET["d1"])):
$d1 = $_GET["d1"];

if (isset($_GET["d2"])):
$d2 = $_GET["d2"];


I changed it to the following



if (isset($_GET['d1'], $_GET['d2'])):


I now have data returned and is being paginated but now I got the following issues where I took out the $d1 and $d2 variables

Notice: Undefined variable: d1 in /home/sites/it-doneright.co.uk/public_html/admin/software-sales/search-info.php on line 51 Notice: Undefined variable: d2 in /home/sites/it-doneright.co.uk/public_html/admin/software-sales/search-info.php on line 51

Using month:
Notice: Undefined variable: d1 in /home/sites/it-doneright.co.uk/public_html/admin/software-sales/search-info.php on line 62 Notice: Undefined variable: d2 in /home/sites/it-doneright.co.uk/public_html/admin/software-sales/search-info.php on line 62

102 records found.
Notice: Undefined variable: d1 in /home/sites/it-doneright.co.uk/public_html/admin/software-sales/search-info.php on line 74 Notice: Undefined variable: d2 in /home/sites/it-doneright.co.uk/public_html/admin/software-sales/search-info.php on line 74

I am going to investigate and see if I can fix it myself
ianhaney
02-15-2016, 11:42 AM
Sorry am stuck

I tried the following



if (isset($_GET['d1'], $_GET['d2'])):
$d1 = $_GET['d1'];
$d2 = $_GET['d2'];


instead of below



if (isset($_GET['d1'], $_GET['d2'])):


all I got with that is the following

Using month: 01-02-2016-29-02-2016

0 records found.

Page 1 of 0.

The URL looks like the following

http://it-doneright.co.uk/admin/software-sales/search-info.php?d1=01-02-2016&d2=29-02-2016&rows=10
styxlawyer
02-15-2016, 12:01 PM
Sorry, I don't have time to look at this now and probably not again before Thursday at the earliest. However, it looks as though the PHP code is now right and it's just your SQL queries which aren't working. I think you need to go back and re-read this post (http://www.dynamicdrive.com/forums/showthread.php?79689-paginate-this-data&p=316573#post316573).
ianhaney
02-15-2016, 12:44 PM
That's ok, I will have a look in the mean time and see if I can get it working and post a update if I manage to get it working
ianhaney
02-15-2016, 01:04 PM
I think I have have a temp fix for now, it was what you said in a earlier reply about the date format in the db table, it was yyyy-mm-dd and the datepicker was in format dd-mm-yyyy so for now I have just altered the date format to yyyy-mm-dd and is working perfect now

for now will leave it like that until can do it in dd-mm-yyyy or until Thursday but main thing it is working, I'll do some googling in the mean time and see if can get it working under date format dd-mm-yyyy
styxlawyer
02-15-2016, 06:00 PM
The whole point of the ISO 8601 format is that dates are always sequential. Use your existing date picker and manipulate the string from it so it matches that required by the database before you run the query. You'll need to use the PHP "explode()" function (http://php.net/manual/en/function.explode.php).

I might be back on Wednesday evening.
ianhaney
02-16-2016, 11:29 AM
Ahh ok, that went bit over my head regarding the explode function, think I got the bit about manipulate the string so it matches before running the query but as not 100%

if can't solve it, if ok to wait until your back Wednesday/Thursday
styxlawyer
02-17-2016, 06:44 PM
There are several options for making the two date strings match. Firstly, we have to know which of the following is correct and then we can progress.



.
the column called sales_month stores the date in the format of YYYY/MM/DD
.




.
it was yyyy-mm-dd and the datepicker was in format dd-mm-yyyy
.


I'll assume that the date format required for the database query is YYYY-MM-DD (the standard ISO 8601 format) but, if that's wrong, you'll need to change the code below.

The simplest way to fix this is to get the date-picker to produce a date string in the YYYY-MM-DD format. However, if that's not possible (or appropriate for your users), then the date-picker string needs to be broken down into it's constituent parts and reassembled in the correct format. It's important to remember that the format of the dates in the URI must always be the same whether the call to search-data.php comes from the date-picker page or is a recursive call from the search-data page itself. This narrows the choice down to two possible places where the translation can occur.

The first is in the date-picker page and, as this page is running on the browser, this will require some JavaScript. A simple function can translate the date codes from the date-picker into ISO 8601 format before they are inserted into the URI passed to search-data.php. That way no further translation is needed.

The second method is to translate the date-picker output immediately before the SQL query is assembled and, as this is server-side code, PHP will be required. Here's how to do that.

Firstly copy the function below and paste near the top of your page:



function translateDate($date) {
$dateArray = explode("-", $date); /* This creates an array of the three parts of the string in $date. */
return $dateArray[2].'-'.$dateArray[1].'-'.$dateArray[0]; /* Now stitch the parts back together in the reverse order. */
}


Now add a couple of calls to the function before assembling the query string (you'll have to do this in two places, once for query which establishes the size of the data and the second one for the retrieval of the data):



$start_date = translateDate($d1);
$end_date = translateDate($d2);
$query = "SELECT * FROM purchased_software WHERE sales_month BETWEEN $start_date AND $end_date ORDER BY id LIMIT $start_from, $per_page";


If that doesn't fix it, you'll have to post the source of the page which is currently working.