SitePoint Sponsor

User Tag List

Results 1 to 5 of 5

Thread: Out of time

Hybrid View

  1. #1
    SitePoint Evangelist
    Join Date
    Mar 2011
    Location
    Bellingham, WA
    Posts
    450
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Out of time

    Hello,

    I'm trying to get my MySql database to automatically enter in a timestamp when I insert data. Based on the structure/code below, I just get 0's in my dbTime column. Any thoughts would be appreciated...

    -Eric

    Fields: log_id, tax_id, email, dbTime
    Structure
    Type: timestamp
    Default: Current_Timestamp
    Null: No

    PHP Code:
        $log_query=mysql_query("INSERT INTO log VALUES ('','".$txn_id."','".$payer_email."','')"); 

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    i just tested it and your query gets an error --
    SQL Error (1292): Incorrect datetime value: ''

    always test your queries outside of php first
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Evangelist
    Join Date
    Mar 2011
    Location
    Bellingham, WA
    Posts
    450
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Hello!

    First, here's the SHOW CREATE TABLE:

    SQL result

    Host: localhost
    Database: myaglebr__kreut2
    Generation Time: Jun 25, 2011 at 10:40 AM
    Generated by: phpMyAdmin 3.3.10.1 / MySQL 5.1.56-log
    SQL query: SHOW CREATE TABLE log;
    Rows: 1

    Table Create Table
    log CREATE TABLE `log` (
    `log_id` int(11) NOT NULL AUTO_INCREMENT,
    `txn_id` varchar(30) NOT NULL,
    `payer_email` varchar(255) NOT NULL,
    `dbTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`log_id`)
    ) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8

    Second, regarding the PHP --- unless I'm being batty, it actually does enter info into my database --- just not the date. And regarding the error, since I tell it to put in the timestamp (at least I think I do) automatically, shouldn't I not have to put anything in the column?

    Thanks for looking into this.

    -Eric

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    your CREATE TABLE looks okay (i just wanted to reassure myself that the timestamp column was declared properly)

    when i tested it, the column accepted a NULL and converted it to the default value, CURRENT_TIMESTAMP

    however, when i attempted to insert row with a zero-length string as the value for the timestamp column, i got that error message and it definitely did not insert a row

    you have several options: provide a list of column names for the INSERT and omit both the auto_increment column and the timestamp column (thus allowing their default values to be used), or use either NULL or CURRENT_TIMESTAMP as the value to be inserted into the timestamp column instead of the zero-length string

    a string, whether zero-length or whatever, is, you gotta admit, a pretty questionable value for a column that isn't a TEXT or VARCHAR

    maybe it is version dependent, and in your case it converted the zero-length string into a 0 value, but in my case it produced the error message

    best to tidy up your syntax and provide only valid values
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Evangelist
    Join Date
    Mar 2011
    Location
    Bellingham, WA
    Posts
    450
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Thanks for your solutions! I thought that it was a bit weird that I input a 0 length string for the id and it entered something (this was my index), but didn't work for the timestamp.

    Enjoy the rest of your weekend.

    Eric


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
  •