[SOLVED] Noob DB mistake- Date Stamp

Grant Holmes · January 29, 2008

I am slowly, but surely learning PHP, but made a boo-boo in creating my table that NOW I know is wrong.

Basically, whenever I create a record, I have a time-stamp field so I know when a record was created.

However, today, in managing some records, I noticed that when I changed the status on a record from inactive to active that the time stamp field was also nice enough to update. I THOUGHT that field was only for creation of the record.

Do I need another field where "I" put the "creation" date in that can't/won't be updated, or is there a better way to handle this? In some ways it is nice to know when the record was updated, but for my application I HAVE to know/keep that original date.

Thoughts?

themistral · January 29, 2008

If you want to capture the dated added and the date last modified then yes you will need 2 fields.

If you just want the date a record was added, find all update queries that change the date field and remove the references to the date field.

HTH

dare87 · January 29, 2008

Field: timestamp (or whatever you want)

Type: datetime

That is how I have it setup and then when you have the initial insert use

<?php
$query = "INSERT INTO whatever SET timestamp=NOW()";

Grant Holmes · January 30, 2008

Hey gang, thanks for the great suggestions. I will need a new field.

I'm using a form processing page to insert the form results. While this may not be the best usage, I'd like to use this line:

if($FTGDateEntered == "")  $FTGDateEntered = "SET timestamp=NOW()";

Will that work? This assumes that DateEntered WILL always be blank as it's not set anywhere until I put it in the DB

Grant Holmes · January 30, 2008

Well I learned that will not work.

Here's what I've done.

I added a new TimeStamp field called "LastUpdated" and made it a "TimeStamp"..."ON UPDATE CURRENT_TIMESTAMP" as the attribute. This is now my "date created/updated" field- and it's working correctly.

I changed my DateEntered field then to be a "DATE" field. Now in my processing form, I'm doing this:

if($FTGDateEntered == "")  $FTGDateEntered = "date(YmdHis)";

But I'm getting "0000-00-00" entered/passed to the table.

Help?!

revraz · January 30, 2008

Compare

"date(YmdHis)";

to

0000-00-00

Not the same format, is the DB set to DATETIME or just DATE?

Grant Holmes · January 30, 2008

Good catch. It was just "Date".

I changed it to "DateTime", but still am now getting "0000-00-00 00:00:00"

revraz · January 30, 2008

Try

"date(Y-m-d H:i:s)";

But you should just use the MySQL NOW() if you want to insert the current date/time

Grant Holmes · January 30, 2008

I tried both your suggestions.

the "date(Y-m-d H:i:s)"; returned more zeros as above.

and using if($FTGDateEntered == "") $FTGDateEntered = "now()"; stops the form from working.

I don't think I understood what you said?

revraz · January 30, 2008

NOW() is only for MySQL

Lets see your INSERT query that you use to write the row.

Grant Holmes · January 30, 2008

Like this?

if (mysql_errno() == 0) {

@mysql_select_db("contacts", $mysql_link);


}

if (get_magic_quotes_gpc()) {
$FTG_Event = stripslashes($FTGEvent);
$FTG_Active = stripslashes($FTGActive);
$FTG_IP = stripslashes($FTGIP); 
$FTG_DateEntered = stripslashes($FTGDateEntered); 
$FTG_Contact_Info_FirstName = stripslashes($FTGContact_Info_FirstName);
$FTG_Contact_Info_LastName = stripslashes($FTGContact_Info_LastName);
$FTG_Contact_Info_StreetAddress = stripslashes($FTGContact_Info_StreetAddress);
$FTG_Contact_Info_Address2 = stripslashes($FTGContact_Info_Address2);
$FTG_Contact_Info_City = stripslashes($FTGContact_Info_City);
$FTG_Contact_Info_State = stripslashes($FTGContact_Info_State);
$FTG_Contact_Info_ZipCode = stripslashes($FTGContact_Info_ZipCode);
$FTG_Contact_Info_Country = stripslashes($FTGContact_Info_Country);
$FTG_Contact_Info_Email = stripslashes($FTGContact_Info_Email);
$FTG_Comments = stripslashes($FTG_Comments);  
$FTG_Birthday_Info_FirstName = stripslashes($FTGBirthday_Info_FirstName);
$FTG_Birthday_Info_LastName = stripslashes($FTGBirthday_Info_LastName);
$FTG_Birthday_Info_StreetAddress = stripslashes($FTGBirthday_Info_StreetAddress);
$FTG_Birthday_Info_Address2 = stripslashes($FTGBirthday_Info_Address2);
$FTG_Birthday_Info_City = stripslashes($FTGBirthday_Info_City);
$FTG_Birthday_Info_State = stripslashes($FTGBirthday_Info_State);
$FTG_Birthday_Info_ZipCode = stripslashes($FTGBirthday_Info_ZipCode);
$FTG_Birthday_Info_Country = stripslashes($FTGBirthday_Info_Country);
$FTG_Birthday_Info_Email = stripslashes($FTGBirthday_Info_Email);

} else {
$FTG_Event = $FTGEvent;
$FTG_Active = $FTGActive; 
$FTG_IP = $FTGIP; 
$FTG_DateEntered = $FTGDateEntered;  
$FTG_Contact_Info_FirstName = $FTGContact_Info_FirstName;
$FTG_Contact_Info_LastName = $FTGContact_Info_LastName;
$FTG_Contact_Info_StreetAddress = $FTGContact_Info_StreetAddress;
$FTG_Contact_Info_Address2 = $FTGContact_Info_Address2;
$FTG_Contact_Info_City = $FTGContact_Info_City;
$FTG_Contact_Info_State = $FTGContact_Info_State;
$FTG_Contact_Info_ZipCode = $FTGContact_Info_ZipCode;
$FTG_Contact_Info_Country = $FTGContact_Info_Country;
$FTG_Contact_Info_Email = $FTGContact_Info_Email;
$FTG_Comments = $FTG_Comments;
$FTG_Birthday_Info_FirstName = $FTGBirthday_Info_FirstName;
$FTG_Birthday_Info_LastName = $FTGBirthday_Info_LastName;
$FTG_Birthday_Info_StreetAddress = $FTGBirthday_Info_StreetAddress;
$FTG_Birthday_Info_Address2 = $FTGBirthday_Info_Address2;
$FTG_Birthday_Info_City = $FTGBirthday_Info_City;
$FTG_Birthday_Info_State = $FTGBirthday_Info_State;
$FTG_Birthday_Info_ZipCode = $FTGBirthday_Info_ZipCode;
$FTG_Birthday_Info_Country = $FTGBirthday_Info_Country;
$FTG_Birthday_Info_Email = $FTGBirthday_Info_Email;

}

if (mysql_errno() == 0) {

$sqlcmd = sprintf("INSERT INTO `birthdays`(`Event`, `Active`, `Contact_Info_FirstName`, `Contact_Info_LastName`, `Contact_Info_StreetAddress`, `Contact_Info_Address2`, `Contact_Info_City`, `Contact_Info_State`, `Contact_Info_ZipCode`, `Contact_Info_Country`, `Contact_Info_Email`, `Cbirthdate`, `Birthday_Info_FirstName`, `Birthday_Info_LastName`, `Birthday_Info_StreetAddress`, `Birthday_Info_Address2`, `Birthday_Info_City`, `Birthday_Info_State`, `Birthday_Info_ZipCode`, `Birthday_Info_Country`, `Birthday_Info_Email`, `Bbirthdate`, `Comments`, `DateEntered`, `IP`) VALUES('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s')",
                   mysql_real_escape_string($FTG_Event, $mysql_link),
                   mysql_real_escape_string($FTG_Active, $mysql_link),				
                   mysql_real_escape_string($FTG_Contact_Info_FirstName, $mysql_link),
                   mysql_real_escape_string($FTG_Contact_Info_LastName, $mysql_link),
                   mysql_real_escape_string($FTG_Contact_Info_StreetAddress, $mysql_link),
                   mysql_real_escape_string($FTG_Contact_Info_Address2, $mysql_link),
                   mysql_real_escape_string($FTG_Contact_Info_City, $mysql_link),
                   mysql_real_escape_string($FTG_Contact_Info_State, $mysql_link),
                   mysql_real_escape_string($FTG_Contact_Info_ZipCode, $mysql_link),
                   mysql_real_escape_string($FTG_Contact_Info_Country, $mysql_link),
                   mysql_real_escape_string($FTG_Contact_Info_Email, $mysql_link),
                   mysql_real_escape_string($FTGCbirthdate, $mysql_link),
                   mysql_real_escape_string($FTG_Birthday_Info_FirstName, $mysql_link),
                   mysql_real_escape_string($FTG_Birthday_Info_LastName, $mysql_link),
                   mysql_real_escape_string($FTG_Birthday_Info_StreetAddress, $mysql_link),
                   mysql_real_escape_string($FTG_Birthday_Info_Address2, $mysql_link),
                   mysql_real_escape_string($FTG_Birthday_Info_City, $mysql_link),
                   mysql_real_escape_string($FTG_Birthday_Info_State, $mysql_link),
                   mysql_real_escape_string($FTG_Birthday_Info_ZipCode, $mysql_link),
                   mysql_real_escape_string($FTG_Birthday_Info_Country, $mysql_link),
                   mysql_real_escape_string($FTG_Birthday_Info_Email, $mysql_link),
                   mysql_real_escape_string($FTGBbirthdate, $mysql_link),			
                   mysql_real_escape_string($FTGComments, $mysql_link),	
                   mysql_real_escape_string($FTGDateEntered, $mysql_link),									                 
			mysql_real_escape_string($FTGIP, $mysql_link));

@mysql_query($sqlcmd, $mysql_link);


}

Otherwise, it's a REAL long page!!

Grant Holmes · January 30, 2008

Above the pasted code just above, but I passed the string to the email the script also sends and I get this:

Date : date(Y-m-d H:i:s)

revraz · January 30, 2008

Try this

Remove

$FTG_DateEntered = $FTGDateEntered;

change

mysql_real_escape_string($FTGDateEntered, $mysql_link),

to

NOW(),

Grant Holmes · January 30, 2008

to verify, do you mean, change to:

mysql_real_escape_string($FTGDateEntered, NOW()),

??

Ohterwise, I don't know what you mean. (I do get the "remove" part)

revraz · January 30, 2008

No, NOW() replaces that entire line.

Grant Holmes · January 30, 2008

Okay, Above that line, I commented out ANY reference to Date Entered. Now the bottom of the code looks like this:

if (mysql_errno() == 0) {

$sqlcmd = sprintf("INSERT INTO `birthdays`(`Event`, `Active`, `Contact_Info_FirstName`, `Contact_Info_LastName`, `Contact_Info_StreetAddress`, `Contact_Info_Address2`, `Contact_Info_City`, `Contact_Info_State`, `Contact_Info_ZipCode`, `Contact_Info_Country`, `Contact_Info_Email`, `Cbirthdate`, `Birthday_Info_FirstName`, `Birthday_Info_LastName`, `Birthday_Info_StreetAddress`, `Birthday_Info_Address2`, `Birthday_Info_City`, `Birthday_Info_State`, `Birthday_Info_ZipCode`, `Birthday_Info_Country`, `Birthday_Info_Email`, `Bbirthdate`, `Comments`, `DateEntered`, `IP`) VALUES('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s')",
                   mysql_real_escape_string($FTG_Event, $mysql_link),
                   mysql_real_escape_string($FTG_Active, $mysql_link),				
                   mysql_real_escape_string($FTG_Contact_Info_FirstName, $mysql_link),
                   mysql_real_escape_string($FTG_Contact_Info_LastName, $mysql_link),
                   mysql_real_escape_string($FTG_Contact_Info_StreetAddress, $mysql_link),
                   mysql_real_escape_string($FTG_Contact_Info_Address2, $mysql_link),
                   mysql_real_escape_string($FTG_Contact_Info_City, $mysql_link),
                   mysql_real_escape_string($FTG_Contact_Info_State, $mysql_link),
                   mysql_real_escape_string($FTG_Contact_Info_ZipCode, $mysql_link),
                   mysql_real_escape_string($FTG_Contact_Info_Country, $mysql_link),
                   mysql_real_escape_string($FTG_Contact_Info_Email, $mysql_link),
                   mysql_real_escape_string($FTGCbirthdate, $mysql_link),
                   mysql_real_escape_string($FTG_Birthday_Info_FirstName, $mysql_link),
                   mysql_real_escape_string($FTG_Birthday_Info_LastName, $mysql_link),
                   mysql_real_escape_string($FTG_Birthday_Info_StreetAddress, $mysql_link),
                   mysql_real_escape_string($FTG_Birthday_Info_Address2, $mysql_link),
                   mysql_real_escape_string($FTG_Birthday_Info_City, $mysql_link),
                   mysql_real_escape_string($FTG_Birthday_Info_State, $mysql_link),
                   mysql_real_escape_string($FTG_Birthday_Info_ZipCode, $mysql_link),
                   mysql_real_escape_string($FTG_Birthday_Info_Country, $mysql_link),
                   mysql_real_escape_string($FTG_Birthday_Info_Email, $mysql_link),
                   mysql_real_escape_string($FTGBbirthdate, $mysql_link),			
                   mysql_real_escape_string($FTGComments, $mysql_link),	
                   NOW(),							                 
	   mysql_real_escape_string($FTGIP, $mysql_link));

Now the processing page fails- (will not process) or write to DB.

Grant Holmes · January 30, 2008

I also tried uncommenting this line:

if($FTGDateEntered == "")  $FTGDateEntered = "date(Y-m-d H:i:s)";

Cause I figured it needed formatted, but same issue, processing to the table fails.

However the email still works and the email contains:

Date : date(Y-m-d H:i:s)

but again, writing to the table fails.

If I leave that line commented, the date in the email is blank. Like:

Date :

Grant Holmes · January 30, 2008

bump

Grant Holmes · January 30, 2008

Got it fixed on my own gang. Thanks for your help.

I ended up setting $time=date("Y-m-d H:i:s");

Then later said that $DateEntered=$time. I know that's extra steps, but kept me from altering LOTs of other code.

Sign In

[SOLVED] Noob DB mistake- Date Stamp

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information