0

I am trying to use this script that I have put together, forgive me I am a bit of a newbie with sql.

When I run the script it is outputting an empty csv file, only showing column titles, no data.

What I want it to do is export entries from today's (current date) date only.

i think the issue is with this line:

$sql = "SELECT * FROM exp_freeform_form_entries_3 where FROM_UNIXTIME(entry_date) = CURDATE() ORDER BY entry_id";

If i remove from "where" onwards it seems to work, but obviously outputs all entries, not todays only.

<?php

//delete existing files
$files = glob('/home/sitename/csv/*'); // get all file names
foreach($files as $file){ // iterate files
  if(is_file($file))
    unlink($file); // delete file
}


// Connect and query the database for the users
$conn = new PDO("mysql:host=localhost;dbname=dbname", 'username', 'password');
$sql = "SELECT * FROM exp_freeform_form_entries_3 where FROM_UNIXTIME(entry_date) = CURDATE() ORDER BY entry_id";
$results = $conn->query($sql);

// Pick a filename and destination directory for the file
// Remember that the folder where you want to write the file has to be writable
//$filename = "/tmp/db_user_export_".time().".csv";
$filename = "/home/sitename/csv/db_user_export_".time().".csv";

// Actually create the file
// The w+ parameter will wipe out and overwrite any existing file with the same name
$handle = fopen($filename, 'w+');

// Write the spreadsheet column titles / labels
fputcsv($handle, array('Entry ID','First Name'));

// Write all the user records to the spreadsheet
foreach($results as $row)
{
    fputcsv($handle, array($row['entry_id'],$row['form_field_1']));
}

// Finish writing the file
fclose($handle);


?>
0

FROM_UNIX_TIME returns a date/time from a Unix timestamp (seconds from 1/1/1970). So:

select from_unixtime(12345);
+----------------------+
| from_unixtime(12345) |
+----------------------+
| 1970-01-01 04:25:45  |
+----------------------+
1 row in set (0.00 sec)

However CURDATE() returns only the date portion:

select curdate();

+------------+
| curdate()  |
+------------+
| 2013-06-26 |
+------------+
1 row in set (0.00 sec)

As you can see, they're not the same, so you have no matches.

If you wrap your FROM_UNIXTIME in a DATE() function, you'll extract just the date portion, so try:

SELECT * FROM exp_freeform_form_entries_3 where DATE(FROM_UNIXTIME(entry_date))
= CURDATE() ORDER BY entry_id";
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.