[RESOLVED] Date Query...

[obrienkev]

Hi all,

Why won't the below work?

This displays correctly...

$date = date('Y m d');
echo $date.'<br />';

My database table date field is set to date value.

$result = mysql_query("INSERT into files(date) values('".$date."')");

however, "0000-00-00" is always inserted.

Why is this?

thanks,

Kevin.

[mrhappiness]

your code produces 2005 08 09 but i'ld bet the database expects 2005-08-09

$result = mysql_query("INSERT into files(date) values(NOW())");

should work as well, why bothering php with calculating the date?

[obrienkev]

Thank You.

I plan to do this step by step so I appreciate all your help.

How do I check if the current date is now a day after the date registered in database table?

this is what I've been trying...

$result2 = mysql_query("SELECT DATE_FORMAT(date, %Y %m %d) FROM files" 
												."WHERE date < NOW()"); 

[obrienkev]

Got this working...

$query = "SELECT DATE_FORMAT(date, '%Y %m %d') FROM files WHERE date <= DATE_ADD(NOW(), INTERVAL 1 DAY)";
$result2 = mysql_query($query) or die(mysql_error());

Now why doesn't this display the date?

while($row = mysql_fetch_array($result2))
{
        echo 'file Date: '.$row['date'];
        echo '<br />';
}

And it also prints 'file Date' for all database entries when it should only print one.
One of the entries is dated from days ago, the rest are from today.

Thanks,

Kevin.

[toplay]

Since you're using fetch by array, you can use $row[0] to get the value.

Another option is to use the 'AS' keyword in MySQL. I like to use it with fetch by associative array, but you can use it with fetch by array too. Example:

$query = "SELECT DATE_FORMAT(date, '%Y %m %d') AS formatted_date FROM files WHERE date <= DATE_ADD(NOW(), INTERVAL 1 DAY)";
$result2 = mysql_query($query) or die(mysql_error());

while ($row = mysql_fetch_assoc($result2)) {
    echo 'file Date: ', $row['formatted_date'];
}

[obrienkev]

I used $row[0] and it returns all database table entries.

How can I only return entries with dates at least a day old??

Here's my query again...

$query = "SELECT DATE_FORMAT(file_date, '%Y %m %d') FROM files WHERE file_date <= DATE_ADD(NOW(), INTERVAL 1 DAY)";
$result2 = mysql_query($query) or die(mysql_error());

Cheers.

Kevin.

[toplay]

Try one of these ways:

... WHERE DATE_ADD(file_date, INTERVAL 1 DAY) = CURDATE()

or maybe you want

... WHERE CURDATE() >= DATE_ADD(file_date, INTERVAL 1 DAY)

depending on what you want/mean.

[obrienkev]

Using this and seems to work...

$query = "SELECT DATE_FORMAT(file_date, '%Y %m %d') FROM files WHERE CURDATE() >= DATE_ADD(file_date, INTERVAL 1 DAY)";
$result2 = mysql_query($query) or die(mysql_error());

Is it possible to transfer files from one table to another based on the date?

thanks,

Kevin.

[mrhappiness]

yes (vague question -> vague answer 😉)

[obrienkev]

OK. I'm using the above query to select which files to move based on the date.

Then what query should I use to transfer these files to another table?

thanks for your help,

Kevin.

[mrhappiness]

http://dev.mysql.com/doc/mysql/en/insert.html
INSERT INTO ... SELECT is the one you look for

after having done this do a DELETE to remove the data from the source table

[obrienkev]

Thanks!! Is it possible to move the files to a different database? Can I use two databases for below?

'files' table would be from database_A and 'archive' table from database_Z.

Is this possible?

$query = "INSERT INTO archive(name, type, size, content, file_date) SELECT name, type, size, content, file_date FROM files WHERE CURDATE() >= DATE_ADD(file_date, INTERVAL 1 DAY)";

[mrhappiness]

yes

syntax is databasename.tablename instead of only tablename

[obrienkev]

So do I include the select_db query for each database I use or is there any need to use it since I'm using database.tablename???

thanks,

Kevin.