[SOLVED] Timestamps for subscription newsletter

[Irresistable]

I have a newsletter where users subscribe to. Or sign up to the mailing list.

It inputs the email and activation code to the database.

 

   function addNewUser($email, $activ_code){
  $email = mysql_real_escape_string($email);
  $activ_code = mysql_real_escape_string($activ_code);
  
      $time = date("F j, Y, g:i");

      $q = "INSERT INTO ".TBL_SUB." VALUES ('$email', '$activ_code', '$time', '1')";
      return mysql_query($q, $this->connection);
   }

 

This is saying it should send the time to the database. I dont understand the F j, Y, g:i

Although I want the time in the format of eg: 21:25 02/11/2009

I don't know if what is set there is correct or not. However.. I don't know what the collum should be in the database. I use phpmyadmin.

 

Please can you help with this.

Thank you

[gizmola]

Please don't do this -- it's one of my pet peeves.  You are using mysql, which has a built in timestamp column type. 

 

I wrote a fairly extensive article about the timestamp:

 

http://www.gizmola.com/blog/archives/93-Too-much-information-about-the-MySQL-TIMESTAMP.html

 

 

[taquitosensei]

see http://www.php.net/date

F j, Y, g:i  = November 2, 2009, 3:30

Which isn't the best way to do it because then strtotime time can't work with it very well because you don't know am/pm.  Your field in your database should be datetime.    Y-m-d H:i:s  = 2009-11-02 15:30:00

 

then you could use date and strtotime to format it in php or format it in mysql

php

$formatted_date=date("H:i d/m/Y", strtotime($timestampe)); 

mysql

select date_format(date_field,'%H:%i %d/%m/%Y') as formatted_date from table 

[Irresistable]

Thanks for the help.

What would be the most efficient way to do this?

What would my code look like.

Also for mysql.. How would I create the timestamp for the table emails.

Something like..

INSERT TO TABLE emails

I'm not sure though.. or about the rest. Can you help with these bits? Thanks.

 

 

The timestamp collum comes up displayed as .. 0000-00-00 00:00:00

Though I'm not sure what the php would be.. to import the date. In that format, but I'd like to be a standard UK format - 21:52 02/11/2009 (or with the date first time after) (or with the time as 9:52:55AM - or something to tell for AM or PM)

[gizmola]

If you read my blog posting it has examples of how to issue a create table statement.  If the table already exists you need to issue an alter table.  Since you stated you were using phpmyadmin, it gives you a facility to easily create a table or change one without knowing the specifics of the create table syntax, which isn't difficult in any case.

 

The beauty of the timestamp is that you don't specify the value for the timestamp column -- it gets filled in automatically when the row is inserted.

 

There are caveats to using a timestamp, as I explain in the article.

 

You can also use a DATETIME column alternatively. In that case the sql would be as simple as:

 

INSERT into your table (col1, col2, createdOn) VALUES ('some name', 'somename@somedomain.com', NOW())

 

There is no need to involve the PHP date or convert it, whatesoever.  MySQL will take care of this all for you.

 

 

[Irresistable]

I have a timestamp collum, the format comes up as 0000-00-00 00:00:00 when the row is inserted. Theres nothing else.. would it be wise deleting the time part from the .PHP, incase its trying to overwrite it setting it to 0's ?

[gizmola]

I have a timestamp collum, the format comes up as 0000-00-00 00:00:00 when the row is inserted. Theres nothing else.. would it be wise deleting the time part from the .PHP, incase its trying to overwrite it setting it to 0's ?

 

I think I've stated this several times already in this thread --- do not use an php for the timestamp!  Insert the row, omitting the timestamp column from the column list, and do not provide any value for it.  The server will set the time of the timestamp column for you, on insert.

[taquitosensei]

unless you need to do a date/time that's not  right now.  In which case you would use datetime for your field then format the php accordingly. 

$date="1976-10-30"; 
$mysql_formatted_date=date("Y-m-d H:i:s", strtotime($date));