SQL query getting entries from current day

Question

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


?>

Answer 1

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