Ridgejp
asked on
Add a date range function to PDF Generation
Hi,
Look for a steer on the above.
Using TCPDF to produce a number of PDF's but would like to add a date range to the output of the report - wondering what is the best way to do this within the mysql statement.
All help welcome.
J
Look for a steer on the above.
Using TCPDF to produce a number of PDF's but would like to add a date range to the output of the report - wondering what is the best way to do this within the mysql statement.
All help welcome.
J
ASKER
Thanks Ray.
What's the best way to do it using a form or bootstrap calendar to select a date in that way - so it's interactive?
What's the best way to do it using a form or bootstrap calendar to select a date in that way - so it's interactive?
Not sure about Bootstrap. See Practical Example #17 in the articles for an example of http://jqueryui.com/datepicker/
ASKER
Will do, report back soon.
ASKER
Hi Ray,
Thanks had a good look at that.
What's the best way to pass the date across to for example to: "pdfgen_singles_first.php" which produces the PDF, for inclusion in the select query?
The PDF's are being accessed using bootstrap styling in the following way: -
Thanks had a good look at that.
What's the best way to pass the date across to for example to: "pdfgen_singles_first.php"
The PDF's are being accessed using bootstrap styling in the following way: -
<ul class="dropdown-menu">
<li role="separator" class="divider"></li>
<li><a href="inventory.php">Return to Inventory Main</a></li>
<li role="separator" class="divider"></li>
<li><a href="pdfgen_singles_first.php" target="_BLANK">PDF's: eBay Singles</a></li>
<li><a href="pdfgen_duplicates_first.php" target="_BLANK">PDF's: eBay Duplicates</a></li>
<li><a href="pdfgen_singles_second.php" target="_BLANK">PDF's: Amazon/TCH Singles</a></li>
<li><a href="pdfgen_duplicates_second.php" target="_BLANK">PDF's: Amazon/TCH Duplicates</a></li>
</ul>
I believe you can install this script and run it "as is" to see the process in action. The script submits the request to itself because there is no action= attribute in the form tag. To submit to a different URL, just put the URL into the action= attribute of the HTML form tag.
https://iconoun.com/demo/temp_ridgejp.php
https://iconoun.com/demo/temp_ridgejp.php
<?php
/**
* https://www.experts-exchange.com/questions/29018490/Add-a-date-range-function-to-PDF-Generation.html#a42118628
*
* Demonstrate the jQuery Date Picker
* https://www.experts-exchange.com/articles/20920/Handling-Time-and-Date-in-PHP-and-MySQL-OOP-Version.html
*/
error_reporting(E_ALL);
echo '<pre>';
// IF THE DATE WAS SUBMITTED, USE IT IN PHP
if (!empty($_POST['chosen_date']))
{
$dateobj = FALSE;
try
{
$dateobj = new DateTime($_POST['chosen_date']);
}
catch(Exception $e)
{
error_log($e);
}
if ($dateobj)
{
$chosen_date = $dateobj->format(DateTime::ATOM);
$chosen_date = $dateobj->format('Y-m-d');
echo PHP_EOL . "You chose date: <b>$chosen_date</b>";
}
}
// CREATE THE HTML USING HEREDOC NOTATION ADAPTED FROM http://jqueryui.com/datepicker/
$htm = <<<EOD
<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>jQuery UI Datepicker - Default functionality</title>
<link rel="stylesheet" href="//code.jquery.com/ui/1.12.1/themes/base/jquery-ui.css">
<link rel="stylesheet" href="http://jqueryui.com/datepicker/resources/demos/style.css">
<script src="https://code.jquery.com/jquery-1.12.4.js"></script>
<script src="https://code.jquery.com/ui/1.12.1/jquery-ui.js"></script>
<script>
$(function() {
$("#datepicker").datepicker();
});
</script>
</head>
<body>
<form method="post">
<p>Date: <input name="chosen_date" type="text" id="datepicker" /></p>
<input type="submit" />
</form>
</body>
</html>
EOD;
echo $htm;
ASKER
Hi Ray,
Think I'm making progress but hit a few snags ...
So I have the page (uk_orders_master.php) which currently calls the PDF from a dropdown menu ... in here I've added the following which includes the action= command for the page in question I've also removed the echo command to prevent the error from TCPDF that it can't open the page as data has already been output.
Then in "pdfgen_singles_first.php" I've added "require_once(uk_orders_ma ster.php); " to prevent an undefined variable error for $chosen_date which is in the SELECT statement as follows: -
I can see that the $chosen_date is being passed through the SELECT statement as I'm getting the following fatal error:
I presume this is a format issue on the date string?
My chosen format for the field is TIMESTAMP i.e. "2017-04-27 15:47:06" which I've entered in full.
Any thoughts?
J
Think I'm making progress but hit a few snags ...
So I have the page (uk_orders_master.php) which currently calls the PDF from a dropdown menu ... in here I've added the following which includes the action= command for the page in question I've also removed the echo command to prevent the error from TCPDF that it can't open the page as data has already been output.
<!-- Date Picker -->
<?php
// IF THE DATE WAS SUBMITTED, USE IT IN PHP
if (!empty($_POST['chosen_date']))
{
$dateobj = FALSE;
try
{
$dateobj = new DateTime($_POST['chosen_date']);
}
catch(Exception $e)
{
error_log($e);
}
if ($dateobj)
{
$chosen_date = $dateobj->format(DateTime::ATOM);
$chosen_date = $dateobj->format('Y-m-d');
}
}
?>
<link rel="stylesheet" href="//code.jquery.com/ui/1.12.1/themes/base/jquery-ui.css">
<link rel="stylesheet" href="http://jqueryui.com/datepicker/resources/demos/style.css">
<script src="https://code.jquery.com/jquery-1.12.4.js"></script>
<script src="https://code.jquery.com/ui/1.12.1/jquery-ui.js"></script>
<script>
$(function() {
$("#datepicker").datepicker();
});
</script>
<form action="pdfgen_singles_first.php" method="post">
<p>Date: <input name="chosen_date" type="text" id="datepicker" /></p>
<input type="submit" />
</form>
Then in "pdfgen_singles_first.php"
$query =" SELECT
salesRecordNumber,
dateImport,
buyerFullName,
buyerAddress1,
buyerAddress2,
buyerAddress3,
buyerTown,
buyerCounty,
buyerPCD,
SUM(totalPrice) AS orderTotalPrice
FROM master
WHERE dateImport>=$chosen_date customLabel =2 AND buyerPCD <>''
GROUP BY
salesRecordNumber,
dateImport,
buyerFullName,
buyerAddress1,
buyerAddress2,
buyerAddress3,
buyerTown,
buyerCounty,
buyerPCD,
catID
ORDER BY catID";
I can see that the $chosen_date is being passed through the SELECT statement as I'm getting the following fatal error:
Fatal error: QUERY FAIL: SELECT salesRecordNumber, dateImport, buyerFullName, buyerAddress1, buyerAddress2, buyerAddress3, buyerTown, buyerCounty, buyerPCD, SUM(totalPrice) AS orderTotalPrice FROM master WHERE dateImport>=2017-04-27 customLabel =2 AND buyerPCD <>'' GROUP BY salesRecordNumber, dateImport, buyerFullName, buyerAddress1, buyerAddress2, buyerAddress3, buyerTown, buyerCounty, buyerPCD, catID ORDER BY catID ERRNO: 1064 ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'customLabel =2 AND buyerPCD <>'' GROUP BY salesRecordNumber, dateImport, buyerFu' at line 1 in /var/www/html/pdfgen_singles_first.php on line 73
I presume this is a format issue on the date string?
My chosen format for the field is TIMESTAMP i.e. "2017-04-27 15:47:06" which I've entered in full.
Any thoughts?
J
ASKER
Sorry spotted the school boy error in the SELECT statement missing AND after the dateImport>=$chosen_date. However, latest error is as follows: -
Warning: Cannot modify header information - headers already sent by (output started at /var/www/html/uk_orders_master.php:153) in /usr/share/php/tcpdf/tcpdf.php on line 7672
TCPDF ERROR: Some data has already been output to browser, can't send PDF file
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SELECT ... WHERE my_date_column BETWEEN 2017-04-01 AND 2017-04-30
This will select all the records from April 2017.
See more on handling date and time in PHP and MYSQL in these articles:
Procedural
https://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL-Procedural-Version.html
Object-oriented
https://www.experts-exchange.com/articles/20920/Handling-Time-and-Date-in-PHP-and-MySQL-OOP-Version.html