CSV Export query with date range

83Lee83 · May 13, 2013

I have a database CSV export working fine, but I want the user to be able to select a date range (date created) for the export process.

Here is my HTML part for the date select (format of date on the form is mm/dd/yyyy):

<form action="csv_export_arp.php">
<p>Select a date range from </p><label style="color:#FFF;" for="from">From</label>
<input type="text" id="from" name="from" value="from" />
<label style="color:#FFF;" for="to" >to</label>
<input type="text" id="to" name="to" value="to"/>
<input name="export" type="submit" value="Export Pending ARP CSV" />
</form>

My CSV export which works apart from the date range (two different files for script:

Part 1 (connect ot db)

<?php

$host = 'xxxxx'; // MYSQL database host adress
$db = 'wxxxxx'; // MYSQL database name
$user = 'xxxxx'; // Mysql Datbase user
$pass = 'xxxxxx'; // Mysql Datbase password
$from_data = $_POST['from']; //Should be sanitized before use in the query, or use PDO.
$to_data   = $_POST['to']; //Should be sanitized before use in the query, or use PDO. 
// Connect to the database
$link = mysql_connect($host, $user, $pass);
mysql_select_db($db);

require 'scripts/exportcsv_arp.inc.php';

$table="support_users"; // this is the tablename that you want to export to csv from mysql.

exportMysqlToCsv($table);

?>

Part two(exportcsv_arp.inc.php):

<?php
function exportMysqlToCsv($table,$filename = 'export_arp.csv')
{
$csv_terminated = "\n";
$csv_separator = ",";
$csv_enclosed = '"';
$csv_escaped = "\\";
$sql_query = "SELECT ticket_number AS 'Ticket number', first_name AS 'First name', surname AS 'Last name', 
email AS 'Email address', product AS 'Product', retailer AS 'Retailer', dop AS 'Date of purchase', message AS
 'Message', address AS 'Postal address', DATE_FORMAT(created, '%m/%d/%Y') AS 'Date created', status AS 'Status', 
action AS 'Action', comment AS 'Comment', resolution AS 'Resolution' FROM $table WHERE status='Pending ARP' 
created >= DATE_FORMAT('" . $from_data . "', '%d/%m/%Y') AND created <  DATE_FORMAT('" . $to_data . "', '%d/%m/%Y')" ;
// Gets the data from the database
$result = mysql_query($sql_query);
$fields_cnt = mysql_num_fields($result);
$schema_insert = '';
for ($i = 0; $i < $fields_cnt; $i++)
{
    $l = $csv_enclosed . str_replace($csv_enclosed, $csv_escaped . $csv_enclosed,
        stripslashes(mysql_field_name($result, $i))) . $csv_enclosed;
    $schema_insert .= $l;
    $schema_insert .= $csv_separator;
} // end for
$out = trim(substr($schema_insert, 0, -1));
$out .= $csv_terminated;
// Format the data
while ($row = mysql_fetch_array($result))
{
    $schema_insert = '';
    for ($j = 0; $j < $fields_cnt; $j++)
    {
        if ($row[$j] == '0' || $row[$j] != '')
        {
            if ($csv_enclosed == '')
            {
                $schema_insert .= $row[$j];
            } else
            {
                $schema_insert .= $csv_enclosed .
                str_replace($csv_enclosed, $csv_escaped . $csv_enclosed, $row[$j]) . $csv_enclosed;
            }
        } else
        {
            $schema_insert .= '';
        }
        if ($j < $fields_cnt - 1)
        {
            $schema_insert .= $csv_separator;
        }
    } // end for
    $out .= $schema_insert;
    $out .= $csv_terminated;
} // end while
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Content-Length: " . strlen($out));
// Output to browser with appropriate mime type, you choose 
//header("Content-type: text/x-csv");
//header("Content-type: text/csv");
header("Content-type: application/csv");
header("Content-Disposition: attachment; filename=$filename");
echo $out;
exit;
 }
 ?>

I can't figure out how to get the query to link in with the selected date range from my form (at the top), I feel this is the important line of my csv export script and this is what I tried, probably quite a way off with it (if the date range query part is removed, the CSV export works fine with the right staus only (pending arp) exported):

 FROM $table WHERE status='Pending ARP' created >= DATE_FORMAT('" . $from_data . "', '%d/%m/%Y') AND created
 <  DATE_FORMAT('" . $to_data . "', '%d/%m/%Y')" ;

Any guidance on getting this working would be much appreciated.

Barand · May 13, 2013

You haven't specified a form method and the default is GET. You are using POST in your processing.

Also for date comparisons you need dates in format yyyy-mm-dd

83Lee83 · May 16, 2013

I'm still not managing to get this to work.

This is how I have changed things. My form date format outputs like this e.g.) 2013-03-01, I'm using date picker jquery plugin for my form.

<script>
$(function() {
$( "#from" ).datepicker({
defaultDate: "+1w",
changeMonth: true,
numberOfMonths: 3,
dateFormat: "yy-mm-dd",
onClose: function( selectedDate ) {
$( "#to" ).datepicker( "option", "minDate", selectedDate );
}
});
$( "#to" ).datepicker({
defaultDate: "+1w",
changeMonth: true,
numberOfMonths: 3,
dateFormat: "yy-mm-dd",
onClose: function( selectedDate ) {
$( "#from" ).datepicker( "option", "maxDate", selectedDate );
}
});
});
</script>

And my HTML for the form

<form action="http://www.xxxx.com/csv_export_arp.php">
    <p>Select a date range from </p><label style="color:#FFF;" for="from">From</label>
<input type="text" id="from" name="from" value="from" />
<label style="color:#FFF;" for="to" >to</label>
<input type="text" id="to" name="to" value="to"/>
    <input name="export" type="submit" value="Export Pending ARP CSV" />
</form>

I changed the script file ( csv_export_arp.php) to this (GET rather than post)

<?php

$host = 'localhost'; // MYSQL database host adress
$db = 'xxxxx'; // MYSQL database name
$user = 'xxxxx'; // Mysql Datbase user
$pass = 'xxxxxx'; // Mysql Datbase password
$from_data = $_GET['from']; //Should be sanitized before use in the query, or use PDO.
$to_data = $_GET['to']; //Should be sanitized before use in the query, or use PDO.
// Connect to the database
$link = mysql_connect($host, $user, $pass);
mysql_select_db($db);

require 'scripts/exportcsv_arp.inc.php';

$table="support_users"; // this is the tablename that you want to export to csv from mysql.

exportMysqlToCsv($table, $from_data, $to_data);

?>

So i then changed my script query part to the following, to the same date ormat. this is part of the file : exportcsv_arp.inc.php

$sql_query = "SELECT ticket_number AS 'Ticket number', first_name AS 'First name', surname AS 'Last name', email AS 'Email address', product AS 'Product',
 retailer AS 'Retailer', dop AS 'Date of purchase', message AS 'Message', address AS 'Postal address', DATE_FORMAT(created, '%Y-%m-%d') AS 'Date created',
 status AS 'Status', action AS 'Action', comment AS 'Comment', resolution AS 'Resolution' FROM $table WHERE status='Pending ARP' created >= DATE_FORMAT
('" . $from_data . "', '%Y-%m-%d') AND created <  DATE_FORMAT('" . $to_data . "', '%Y-%m-%d')" ;
// Gets the data from the database

Can any of you good people see why this isn't working, if I take the date queries out I get the right status(pending ARP) exported to the CSV, so somewhere my date selctions aren't been transfered corractly to the CSV file as well as the status selction.

Barand · May 16, 2013

What is the format of the dates in your db table?

83Lee83 · May 16, 2013

What is the format of the dates in your db table?

Hi, I've just logged in to PHPmyAdmin and it says datetime and the entries are dispalyed like this in the table within phpmyadmin 2013-05-15 13:22:03

Do you know how I might be going wrong?

Barand · May 16, 2013

try

SELECT ticket_number AS 'Ticket number', 
    first_name AS 'First name', 
    surname AS 'Last name', 
    email AS 'Email address', 
    product AS 'Product',
    retailer AS 'Retailer', 
    dop AS 'Date of purchase', 
    message AS 'Message', 
    address AS 'Postal address', 
    DATE(created) AS 'Date created',
    status AS 'Status', 
    action AS 'Action', 
    comment AS 'Comment', 
    resolution AS 'Resolution' 
FROM $table 
WHERE status='Pending ARP' 
    AND DATE(created) BETWEEN '$from_data' AND '$to_data'

83Lee83 · May 16, 2013

try

SELECT ticket_number AS 'Ticket number', 
    first_name AS 'First name', 
    surname AS 'Last name', 
    email AS 'Email address', 
    product AS 'Product',
    retailer AS 'Retailer', 
    dop AS 'Date of purchase', 
    message AS 'Message', 
    address AS 'Postal address', 
    DATE(created) AS 'Date created',
    status AS 'Status', 
    action AS 'Action', 
    comment AS 'Comment', 
    resolution AS 'Resolution' 
FROM $table 
WHERE status='Pending ARP' 
    AND DATE(created) BETWEEN '$from_data' AND '$to_data'

Hi Many thanks for your effort, but doing this just returned a blank CSV with just headings, if I remove the line

AND DATE(created) BETWEEN '$from_data' AND '$to_data'

The CSV lists the entries but for all dates. I notice on the CSV table once exported date formate is as follows : 05/03/2011 = dd/mm/yyyy

not sure if this is where the issue is.

Barand · May 16, 2013

Can you

echo "<pre>$sql_query</pre>";

so we can see the query as it is submitted

83Lee83 · May 16, 2013

Can you

echo "<pre>$sql_query</pre>";

so we can see the query as it is submitted

Hi , sorry I'm a total novice with all this, where do I put that line?

Barand · May 16, 2013

After you create the $sql_query string that you are passing to mysql_query().

After echoing the string you could put

exit;

to halt the script.

Edited May 16, 2013 by Barand

83Lee83 · May 16, 2013

After you create the $sql_query string that you are passing to mysql_query().

After echoing the string you could put

exit;

to halt the script.

Okay thanks this is what I did:

<?php
function exportMysqlToCsv($table, $from_data, $to_data, $filename = 'export_arp.csv')
{
    $csv_terminated = "\n";
    $csv_separator = ",";
    $csv_enclosed = '"';
    $csv_escaped = "\\";    
    $sql_query = "SELECT ticket_number AS 'Ticket number', first_name AS 'First name', surname AS 'Last name', email AS 
'Email address', product AS 'Product', retailer AS 'Retailer', dop AS 'Date of purchase', message AS 
'Message', address AS 'Postal address', DATE_FORMAT(created, '%Y-%m-%d') AS 'Date created', status AS 
'Status', action AS 'Action', comment AS 'Comment', resolution AS 'Resolution' FROM $table 
WHERE status='Pending ARP' AND created >= DATE_FORMAT('" . $from_data . "', '%Y-%m-%d') AND created
 <  DATE_FORMAT('" . $to_data . "', '%Y-%m-%d')" ;
    
    // Gets the data from the database
    $result = mysql_query($sql_query);
    exit;
    $fields_cnt = mysql_num_fields($result);
    $schema_insert = '';
    for ($i = 0; $i < $fields_cnt; $i++)
    {
        $l = $csv_enclosed . str_replace($csv_enclosed, $csv_escaped . $csv_enclosed,
            stripslashes(mysql_field_name($result, $i))) . $csv_enclosed;
        $schema_insert .= $l;
        $schema_insert .= $csv_separator;
    } // end for
    $out = trim(substr($schema_insert, 0, -1));
    $out .= $csv_terminated;
    // Format the data
    while ($row = mysql_fetch_array($result))
    {
        $schema_insert = '';
        for ($j = 0; $j < $fields_cnt; $j++)
        {
            if ($row[$j] == '0' || $row[$j] != '')
            {
                if ($csv_enclosed == '')
                {
                    $schema_insert .= $row[$j];
                } else
                {
                    $schema_insert .= $csv_enclosed .
                    str_replace($csv_enclosed, $csv_escaped . $csv_enclosed, $row[$j]) . $csv_enclosed;
                }
            } else
            {
                $schema_insert .= '';
            }
            if ($j < $fields_cnt - 1)
            {
                $schema_insert .= $csv_separator;
            }
        } // end for
        $out .= $schema_insert;
        $out .= $csv_terminated;
    } // end while
    header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
    header("Content-Length: " . strlen($out));
    // Output to browser with appropriate mime type, you choose 
    //header("Content-type: text/x-csv");
    //header("Content-type: text/csv");
    header("Content-type: application/csv");
    header("Content-Disposition: attachment; filename=$filename");
    echo $out;
    exit;
}
?>

I added end after my $result = mysql_query($sql_query); ad I got a blank page with the URL myurl.com/csv_export_arp.php?from=2012-06-04&to=2013-05-01&export=Export+Pending+ARP+CSV

So from that string I see my dates form the for are been put in the query.

The full script is above if this helps.

Edited May 16, 2013 by 83Lee83

Barand · May 16, 2013

Where's the echo?

You need to echo the query string just before the exit;

83Lee83 · May 16, 2013

Where's the echo?

You need to echo the query string just before the exit;

Doing that this is what the page delivers.

SELECT ticket_number AS 'Ticket number', first_name AS 'First name', surname AS 'Last name', email AS 'Email address', product AS 'Product', 
retailer AS 'Retailer', dop AS 'Date of purchase', message AS 'Message', address AS 'Postal address', DATE_FORMAT(created, '%Y-%m-%d') 
AS 'Date created', status AS 'Status', action AS 'Action', comment AS 'Comment', resolution AS 'Resolution'
 FROM support_users WHERE status='Pending ARP' AND created >= DATE_FORMAT('', '%Y-%m-%d') AND created <  DATE_FORMAT('', '%Y-%m-%d')

Barand · May 16, 2013

I see you ignored my query changes.

Anyway, you can now see that both $from_data and $to_data are empty.

AND created >= DATE_FORMAT('', '%Y-%m-%d') AND created < DATE_FORMAT('', '%Y-%m-%d')

83Lee83 · May 16, 2013

I see you ignored my query changes.

Anyway, you can now see that both $from_data and $to_data are empty.

AND created >= DATE_FORMAT('', '%Y-%m-%d') AND created < DATE_FORMAT('', '%Y-%m-%d')

I have just used your string and it delivers this result.

SELECT ticket_number AS 'Ticket number', 
    first_name AS 'First name', 
    surname AS 'Last name', 
    email AS 'Email address', 
    product AS 'Product',
    retailer AS 'Retailer', 
    dop AS 'Date of purchase', 
    message AS 'Message', 
    address AS 'Postal address', 
    DATE(created) AS 'Date created',
    status AS 'Status', 
    action AS 'Action', 
    comment AS 'Comment', 
    resolution AS 'Resolution' 
FROM support_users 
WHERE status='Pending ARP' 
    AND DATE(created) BETWEEN '' AND ''

Still empty, any idea where i go from here. Many thanks for your continued help by the way.

Barand · May 16, 2013

Check the values before this line

exportMysqlToCsv($table, $from_data, $to_data);

to see if it getting the form values correctly

Edited May 16, 2013 by Barand

83Lee83 · May 16, 2013

Check the values before this line
exportMysqlToCsv($table, $from_data, $to_data);
to see if it getting the form values correctly

That just returns a blank page.

But my selected dates do show up in the URL string again.

Edited May 16, 2013 by 83Lee83

Barand · May 16, 2013

So, did $from_data and $to_data contain the expected values just prior to calling the export function?

83Lee83 · May 16, 2013

So, did $from_data and $to_data contain the expected values just prior to calling the export function?

No it didn't, I did this and just gt a blank page.

<?php
echo "<pre>$sql_query</pre>";
	exit;
function exportMysqlToCsv($table, $from_data, $to_data, $filename = 'export_arp.csv')

Barand · May 16, 2013

As you are echoing the wrong thing in the wrong place then no surprise there.

Check the two variables by echoing them in the place I suggested.

83Lee83 · May 16, 2013

As you are echoing the wrong thing in the wrong place then no surprise there.

Check the two variables by echoing them in the place I suggested.

Sorry for the misunderstnading, so where do I put

echo "<pre>$sql_query</pre>";
    exit;

To check the two variables?

Barand · May 16, 2013

Check the values before this line
exportMysqlToCsv($table, $from_data, $to_data);
to see if it getting the form values correctly

So, did $from_data and $to_data contain the expected values just prior to calling the export function?

Check the two variables by echoing them in the place I suggested.

83Lee83 · May 17, 2013

Many thanks, I realise what you menat now, doh!.

Yes by echoing these values I get the coorect figures e.g)

2012-11-04

2013-05-05

So for some reason my query is not using the figures correctly within the database query for CSV export. Any ideas?

mac_gyver · May 17, 2013

this thread reads like the page is being requested twice, once with and once without the get query string on the url or you have multiple different included/required files floating around.

posting your entire form page would help (javascript/jquery you have on there could be doing this) and post the current php code.

how exactly are you viewing the csv output that is showing the dates in the 05/03/2011 format?

setting php's error_reporting to E_ALL and display_errors to ON may also help with the debugging.

83Lee83 · May 17, 2013

Managed to get this working, I figured this was an issue with the query. Tried a few things and this is what I came up with. I changed my date format to YYYY-MM-DD on my date form.

$sql_query = "SELECT ticket_number AS 'Ticket number', first_name AS 'First name', surname AS 'Last name', 
email AS 'Email address', product AS 'Product', retailer AS 'Retailer', dop AS 'Date of purchase', message AS
 'Message', address AS 'Postal address', DATE_FORMAT(created, '%d/%m/%Y') AS 'Date created', status AS 'Status', 
action AS 'Action', comment AS 'Comment', resolution AS 'Resolution' FROM $table WHERE status='Pending ARP' AND created BETWEEN 
'$from_data 00:00:00' AND '$to_data 00:00:00'";

Sign In

CSV Export query with date range

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information