SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Addict
    Join Date
    Mar 2011
    Location
    Manchester, UK
    Posts
    226
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Time Stamp - Left Blank - Help

    Hi Guys,

    I need some help please, for some reason when a new record is added the field called TimeStamp is left blank. Here is how the field is setup:


    Code:
    Column	Type	                 Attributes                           	Null	Default	                Extra
    
    TimeStamp	timestamp		on update CURRENT_TIMESTAMP	No	CURRENT_TIMESTAMP	ON UPDATE CURRENT_TIMESTAMP
    Can anyone see anything as to why on a new record its left as 0000-00-00 00:00:00 ?

    Below is the PHP code i am using to insert a new record:

    PHP Code:
    $query6 sprintf("INSERT INTO logs VALUES ('',%s,%s,'')",
                
    quote_smart($FullName),
                
    quote_smart($log6)); 
    The last part thats blank '' is for the TimeStamp as iv left it blank thinking when a new record is added MySQL will add the current time/date automatically, any help would be great!

    Thank you..!
    You're Help Does Not Go Unnoticed, I have So Far Donated 25 GBP
    To Cancer Research UK As A Thank You To All The SitePoint
    Members That Have Helped Me In The PHP Forum Thank You!

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,274
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    could you do a SHOW CREATE TABLE for that table please
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict
    Join Date
    Mar 2011
    Location
    Manchester, UK
    Posts
    226
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    Thanks for the reply, I've never used that function before so not sure what im suppose to do with it, when i enter it in the SQL for PHPMYADMIN its coming up with an error. Can you give me some guidance please.

    Thank you.
    You're Help Does Not Go Unnoticed, I have So Far Donated 25 GBP
    To Cancer Research UK As A Thank You To All The SitePoint
    Members That Have Helped Me In The PHP Forum Thank You!

  4. #4
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,182
    Mentioned
    66 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by AdWarm View Post
    The last part thats blank '' is for the TimeStamp as iv left it blank thinking when a new record is added MySQL will add the current time/date automatically, any help would be great!
    Nope.

    Do not include the timestamp in your INSERT statement. Your telling it to add a null time stamp rather than letting it do its thing.

  5. #5
    SitePoint Addict
    Join Date
    Mar 2011
    Location
    Manchester, UK
    Posts
    226
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    I've change it to this but its still not adding the time stamp:

    PHP Code:
    $log "added a new contact - $postfullname from $CompanyName";
    $query4 sprintf("INSERT INTO logs VALUES ('',%s,%s)",
                
    quote_smart($FullName),
                
    quote_smart($log)); 
    You're Help Does Not Go Unnoticed, I have So Far Donated 25 GBP
    To Cancer Research UK As A Thank You To All The SitePoint
    Members That Have Helped Me In The PHP Forum Thank You!

  6. #6
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,163
    Mentioned
    153 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by AdWarm View Post
    Hi,

    Thanks for the reply, I've never used that function before so not sure what im suppose to do with it, when i enter it in the SQL for PHPMYADMIN its coming up with an error. Can you give me some guidance please.

    Thank you.
    Code:
    SHOW CREATE TABLE logs
    Via http://dev.mysql.com/doc/refman/5.0/...ate-table.html

  7. #7
    SitePoint Addict
    Join Date
    Mar 2011
    Location
    Manchester, UK
    Posts
    226
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks, below is the information:

    CREATE TABLE `logs` (
    `LogID` int(11) NOT NULL AUTO_INCREMENT,
    `EditedBy` text NOT NULL,
    `Log` text NOT NULL,
    `TimeStamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`LogID`)
    ) ENGINE=MyISAM AUTO_INCREMENT=174 DEFAULT CHARSET=latin1
    You're Help Does Not Go Unnoticed, I have So Far Donated 25 GBP
    To Cancer Research UK As A Thank You To All The SitePoint
    Members That Have Helped Me In The PHP Forum Thank You!

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,274
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Code:
    INSERT
      INTO logs
         ( EditedBy , Log )
    VALUES
         ( %s , %s )
    do not mention the columns that have a default behaviour

    two of yours do, two don't

    supply values only for the columns that don't
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Enthusiast
    Join Date
    Jul 2007
    Location
    San Sebastian, Spain
    Posts
    93
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    The issue here is that you are looking for the default behavior for timestamp but you are attempting to register an empty string as a timestamp value. This is not permitted. As r937 described above you should really name the columns you do not want default behavior for. Another way of doing this is passing a NULL value when you want the default behavior to be initiated.

    For example:

    Code:
    $query6 = sprintf("INSERT INTO logs VALUES (null,%s,%s,null)",
                quote_smart($FullName),
                quote_smart($log6));
    Would have worked.

  10. #10
    Always A Novice bronze trophy
    K. Wolfe's Avatar
    Join Date
    Nov 2003
    Location
    Columbus, OH
    Posts
    2,182
    Mentioned
    66 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by rcashell View Post
    The issue here is that you are looking for the default behavior for timestamp but you are attempting to register an empty string as a timestamp value. This is not permitted. As r937 described above you should really name the columns you do not want default behavior for. Another way of doing this is passing a NULL value when you want the default behavior to be initiated.

    For example:

    Code:
    $query6 = sprintf("INSERT INTO logs VALUES (null,%s,%s,null)",
                quote_smart($FullName),
                quote_smart($log6));
    Would have worked.
    You should not be passing the value at all, not even using NULL, as r937 and I stated.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •