Link to home
Start Free TrialLog in
Avatar of Ridgejp
RidgejpFlag for United Kingdom of Great Britain and Northern Ireland

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
Avatar of Ray Paseur
Ray Paseur
Flag of United States of America image

If you have columns of data type DATE you can use something like this in your WHERE clause

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
Avatar of Ridgejp

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?
Not sure about Bootstrap.  See Practical Example #17 in the articles for an example of http://jqueryui.com/datepicker/
Avatar of Ridgejp

ASKER

Will do, report back soon.
Avatar of Ridgejp

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

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

Open in new window

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

Open in new window

Avatar of Ridgejp

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.

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

Open in new window


Then in "pdfgen_singles_first.php" I've added "require_once(uk_orders_master.php);" to prevent an undefined variable error for $chosen_date which is in the SELECT statement as follows: -

$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";

Open in new window


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

Open in new window


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
Avatar of Ridgejp

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

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Ray Paseur
Ray Paseur
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial