Display Records Older Than 30 Days

[oceanwave]

I want to display all records older than 30 days. From what I have read, this is accomplished using:

$sql = "SELECT * FROM table WHERE TO_DAYS(NOW()) - TO_DAYS(Stamp) >= '30'";

The question is, how to create these values in my table?
Stamp datetime NOT NULL,
produces a 0000-00-00 00:00:00 value in my database
Should it even be a datetime value in the first place?
Secondly, why does it input 0000-00-00 00:00:00?

If Stamp datetime NOT NULL, is incorrect, what statement do I need for the $sql line to work?

What statement do I need when creating my table for TO_DAYS(NOW()) to work? I would think this would have to be an automatic value. If I am wrong, do I need to create a column? If so, using what create command?

Thanks so much!

[coopster]

Welcome to WebmasterWorld, oceanwave!

Yes, that is one way to get all records older than 30 days, there are a few other ways [webmasterworld.com] to get those rows as well.

>>The question is, how to create these values in my table?

As you INSERT new rows, you could specify NOW() as the value for your DATETIME column:

INSERT INTO mytable (Stamp) VALUES (NOW()); 

>>Should it even be a datetime value in the first place?

Sure, why not. Another option would be to use a column type of TIMESTAMP [mysql.com]. If you insert NULL into the first TIMESTAMP column of a table, the current date and time is inserted. More about TIMESTAMP [mysql.com].

>>Secondly, why does it input 0000-00-00 00:00:00?

Because you haven't assigned a value to it. And since you defined it as NOT NULL, you cannot insert a NULL value.

>>If Stamp datetime NOT NULL, is incorrect, what statement do I need for the $sql line to work?

You simply need to assign a value when you INSERT or UPDATE the value for that particular column.

>>What statement do I need when creating my table for TO_DAYS(NOW()) to work?

First, it has to be a date or date-and-time [mysql.com] column type. Then, as stated earlier, you need to INSERT a date or date-and-time value into the column, otherwise it will default to all zeros.

CREATE TABLE mytable (myStamp DATETIME); 
INSERT INTO mytable VALUES(NOW()); 
INSERT INTO mytable VALUES('2002-12-25'); 
SELECT * FROM mytable; 
+---------------------+ 
¦ myStamp             ¦ 
+---------------------+ 
¦ 2004-02-16 21:15:35 ¦ 
¦ 2002-12-25 00:00:00 ¦ 
+---------------------+ 
2 rows in set (0.01 sec) 

It's as easy as that!

[oceanwave]

Thank you so much Coopster!

I tried to insert: INSERT INTO mytable (Stamp) VALUES (NOW()); as you suggested, but I wasn't sure where to include this line. This is the page that adds to the database. I shortened the number of lines below to show you the script in a reasonable amount of space.

My first attempt produced an error message that told me to go look at the manual (not a good sign). My second attempt did change the datetime value, except no other entered values were added to the database.
Where is this line added?
<?php
include("connect.php");

if(!empty($fname)) {
$tname = addslashes($tname);
$fname = addslashes($fname);
$lname = addslashes($lname);

$sql = "INSERT INTO table SET TitleName='$tname', FirstName='$fname', LastName='$lname'";

$query = mysql_query($sql) or die("Cannot query the database.<br>". mysql_error());

echo "Your record has been added to the database.";
} else {
?>

Thank you for taking the time to explain things to me!

[coopster]

You're welcome.
If the

dateAdded

column is part of your

table

table, then you have to include it in your INSERT operation. If a column is not explicity named in an INSERT operation, it will be assigned its default value. Simply add the field to your INSERT operation:

$sql = " 
INSERT INTO table 
 SET TitleName='$tname', 
 FirstName='$fname', 
 LastName='$lname', 
 dateAdded=NOW() 
"; 

[oceanwave]

Coopster, you are brilliant! Thank you so much for your help!

A simple line like Stamp=NOW() fixed everything!

It is interesting because when I used the statement:
$sql = "SELECT * FROM table WHERE TO_DAYS(NOW()) - TO_DAYS(Stamp) >= '30'";
the rows where Stamp= 0000-00-00 00:00:00 did not show up. That won't be a problem in the future (now that Stamp is fixed), but I thought those rows would have been included.

I guess if I change the word SELECT to DELETE in the sql statement, I can automatically delete all entries over 30 days, without having to select and add checkboxes. WOW!

Thanks again Coopster!

[coopster]

You're welcome.

It is interesting because [...] the rows where Stamp= 0000-00-00 00:00:00 did not show up [...] I thought those rows would have been included.

They are still in your table but they won't show up because of the logic in the

WHERE

clause. But do you know why? If you are thinking, ...well, any date minus 0 should be greater than 30, therefore it should return that date, right? Logically, yes. However, executing the TO_DAYS function against a malformed date will return a NULL result. All zeros is a malformed date. And NULL is not greater than 30 -- therefore no row returned.

You could do two things to resolve this issue, add more logic to the WHERE clause to get rid of them right now...

$sql = "SELECT * FROM table WHERE TO_DAYS(NOW()) - TO_DAYS(Stamp) >= '30' OR Stamp = 0"; 

...or wait 30 days and delete all the rows where the Stamp is = 0 :)

I guess if I change the word SELECT to DELETE in the sql statement, I can automatically delete all entries over 30 days...

True.