Trying to download data matching current date but unable to

[lovephp]

OK friends im back yet again with new issue. from my table i am trying to download data matching current day date but somehow its not working, i save date as

`date_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

the outcome is 2014-04-04 22:13:19

 

here is my code, what i want is download data which is posted only today like 2014-04-04 so i did this but it downloads all blank

 

require_once('config.php');
$todaysdata = date("Y-m-d");
$query = sprintf('SELECT * FROM data WHERE date_time = "'.$todaysdata.'"');
$result = mysql_query($query) or die('Failed to connect to server: ' . mysql_error());
$today = date("d-m-Y");
header("Content-Type: text/csv");
header("Content-Disposition: attachment;filename=Data_Backup.$today.csv");
$row = mysql_fetch_assoc($result);
if ($row) {
    echocsv(array_keys($row));
}
while ($row) {
    echocsv($row);
    $row = mysql_fetch_assoc($result);
}
function echocsv($fields)
{
    $separator = '';
    foreach ($fields as $field) {
        if (preg_match('/\\r|\\n|,|"/', $field)) {
            $field = '"' . str_replace('"', '""', $field) . '"';
        }
        echo $separator . $field;
        $separator = ',';
    }
    echo "\r\n";
}

Edited April 4, 2014 by lovephp

[ginerjm]

When you say "isn't working", whatever do you mean?  I'm guessing that you get no results from your query.  Yes?

[lovephp]

yes i meant the file does gets downloaded but the csv file remains blank no data on it

[ginerjm]

That's because you have no data.  Agreed? 

 

Think about what you wrote in your first post.  You know what the date_time (btw - bad field name) field holds in the table.  You know what the value you wish to compare to the records looks like.

 

Do they look alike?

[lovephp]

but if i remove

 

$query = sprintf('SELECT * FROM data'');

 

all data gets downloaded

[lovephp]

there is data in the table and i want to compare is say today is 2014-04-04 i want to get only those rows with 2014-04-04 from date_time but the date_time stores records as 2014-04-04 22:13:19

Edited April 4, 2014 by lovephp

[ginerjm]

1 - why are you using sprint???????

 

2 - you're thinking backwards.

 

You want to get the table's date field in a format that you can compare your $todaysdata field to, no?

If you applied your curiosity and work effort to a little research on MySQL you would find the list of MySQL functions that are available.  Especially the ones that pertain to dates.

 

 

$q = "select  * FROM data WHERE DATE_FORMAT(date_time, "%Y-%c-%d") = '$todaysdata' ";

[lovephp]

lol you are right but slight issue i get error now as Parse error: parse error on that line

[ginerjm]

Change my double quotes around the format string to singles.

Edited April 4, 2014 by ginerjm

[lovephp]

what is wrong here?

 

 

$query = 'SELECT * FROM data WHERE DATE_FORMAT(date_time, '%Y-%c-%d') = "'.$todaysdata.'"';

still getting error

[ginerjm]

my string works and is much simpler:

 

$q = "select  * FROM data WHERE DATE_FORMAT(date_time, '%Y-%m-%d') = '$todaysdata' ";

 

Note: I changed the quotes as well as the month spec - %m will give you 01,02... as it should be.  Note that by using dbl quotes outside you can avoid having to concatenate the variable at the end.

[lovephp]

ok this works but again all blank not data getting downloaded

 

 

$query = "select  * FROM data WHERE DATE_FORMAT(date_time, '%Y-%m-%d') = '".$todaysdara."'";
 

[ginerjm]

Why won't you use the line I gave you?  Yours is flawed.  Please? 

[lovephp]

used the one you gave it works but no data in the csv file

[ginerjm]

Can I see you new code now?

[lovephp]

using this and it does download the csv but data does not appear on the file

 

$query = "select  * FROM data WHERE DATE_FORMAT(date_time, '%Y-%m-%d') = '$todaysdata' ";

[ginerjm]

No - all your code.  Plus add a check of the number of rows you get from the query before continuing.

[lovephp]

hahaha got it now its working made a mistake

 

$todaysdata = date("T-m-d"); instead of $todaysdata = date("Y-m-d");

 

cheers bro

Edited April 4, 2014 by lovephp

[ginerjm]

Which is why I wanted to see your code.

 

good luck  - hth

[lovephp]

thanks you been really helpful really appreciate it.