#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2000
    Posts
    31
    Rep Power
    14
    Hello, my problem is I cannot insert the date time in my database with my php! What I'm doing wrong?

    My table
    --------
    CREATE TABLE register (
    id int(11) DEFAULT '0' NOT NULL auto_increment,
    name VARCHAR(50),
    email VARCHAR(50),
    title VARCHAR(50),
    url VARCHAR(50),
    description VARCHAR(200),
    date DATETIME,
    PRIMARY KEY (id));

    my PHP
    ------

    $query = "INSERT INTO register VALUES('$name','$title','$url','$description', '$email', 'SYSDATE()')";
    $result = MYSQL_QUERY($db);
  2. #2
  3. No Profile Picture
    Apprentice Deity
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 1999
    Location
    Niagara Falls (On the wrong side of the gorge)
    Posts
    3,237
    Rep Power
    18
    You are not entering a number of values equal to the number of columns. You either need to specify a value for each column (AND IN THE SAME ORDER AS THE COLUMNS WERE DEFINED):

    $query = "INSERT INTO register VALUES(null,'$name','$email','$title','$url','$description', 'SYSDATE()')";


    or specify the fields the values will go into:

    $query = "INSERT INTO register (name,title,url,description,email,date) VALUES('$name','$title','$url','$description', '$email', 'SYSDATE()')";
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2000
    Posts
    31
    Rep Power
    14
    <BLOCKQUOTE><font size="1" face="Verdana,Arial,Helvetica">quote:</font><HR>Originally posted by rod k:
    You are not entering a number of values equal to the number of columns. You either need to specify a value for each column (AND IN THE SAME ORDER AS THE COLUMNS WERE DEFINED):

    $query = "INSERT INTO register VALUES(null,'$name','$email','$title','$url','$description', 'SYSDATE()')";


    or specify the fields the values will go into:

    $query = "INSERT INTO register (name,title,url,description,email,date) VALUES('$name','$title','$url','$description', '$email', 'SYSDATE()')";
    [/quote]

    Thank You Rod, I tried but I still have the problem. Maybe I need to put some code in my form? At this time I have for result 0000-00-00 I change my table "datetime" for "date DATE,"

    [This message has been edited by dalida (edited September 08, 2000).]
  6. #4
  7. Banned (not really)
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 1999
    Location
    Brussels, Belgium
    Posts
    14,640
    Rep Power
    4476
    I don't know if sysdate() returns something that is valid as a mysql datetime entry. I also believe that mysql updates you date/datetime/timestamp columns automatically when you do an insert, to the current time.

    try this:

    insert into register (name,email,title,url,description) values ('$name','$email','$title,'$url','$description');

    the id and date should be set automatically.


    HTH,
    ---John Holmes...
  8. #5
  9. No Profile Picture
    Apprentice Deity
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 1999
    Location
    Niagara Falls (On the wrong side of the gorge)
    Posts
    3,237
    Rep Power
    18
    Actually, no, mysql does not automatically update all those datatypes. Only TIMESTAMP is automatically updated on insert/update/replace.

    dalida, use now() instead of sysdate() for setting the datetime field.
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2000
    Posts
    31
    Rep Power
    14
    <BLOCKQUOTE><font size="1" face="Verdana,Arial,Helvetica">quote:</font><HR>Originally posted by rod k:
    Actually, no, mysql does not automatically update all those datatypes. Only TIMESTAMP is automatically updated on insert/update/replace.

    dalida, use now() instead of sysdate() for setting the datetime field.
    [/quote]

    I tried SYSDATE(), CURDATE(), NOW() and ( NOW(),'...'), but I still have 0000-00-00
    Maybe I have a error in my table?
    This is my last attempt;
    $date=date("Y M D");
    $query = "INSERT INTO register values (NULL,'$name','$email','$title','$url','$description','$date')";
    $result = MYSQL_QUERY($query);

    I read the manual at MySQL and PHP but I cannot find anything on this...
  12. #7
  13. No Profile Picture
    Apprentice Deity
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 1999
    Location
    Niagara Falls (On the wrong side of the gorge)
    Posts
    3,237
    Rep Power
    18
    Don't put quotes around now() or sysdate(). They're functions and shouldn't be quoted. If you quote them it won't run the function but try to insert the text.
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2000
    Posts
    31
    Rep Power
    14
    <BLOCKQUOTE><font size="1" face="Verdana,Arial,Helvetica">quote:</font><HR>Originally posted by rod k:
    Don't put quotes around now() or sysdate(). They're functions and shouldn't be quoted. If you quote them it won't run the function but try to insert the text.[/quote]

    Thank You Rod, I found the problem!

    I changed my table for: date DATE not null,
    and put in my php:
    $dat = date("Y m d");
    $query = "INSERT INTO register VALUES ('0','$name','$email','$title','$url','$description','$dat')";
    $result = mysql_query($query);

    Everything is working find, the problem is when someone enter a ' in the field of my html form.
    Example; It's a cool site for hockey fans! If I don't put the backslash in the html
    form It's a ... it wont print in the MySQL. I need to find how to fix this now
    But everything is working for the date thank you for help!
  16. #9
  17. No Profile Picture
    Apprentice Deity
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 1999
    Location
    Niagara Falls (On the wrong side of the gorge)
    Posts
    3,237
    Rep Power
    18
    addslashes()
  18. #10
  19. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2000
    Posts
    31
    Rep Power
    14
    <BLOCKQUOTE><font size="1" face="Verdana,Arial,Helvetica">quote:</font><HR>Originally posted by rod k:
    addslashes()[/quote]

    Thanks

Similar Threads

  1. mysql date time stamp and UTF8
    By doomish in forum MySQL Help
    Replies: 0
    Last Post: February 8th, 2004, 08:37 AM
  2. Replies: 1
    Last Post: January 30th, 2004, 06:04 PM
  3. mysql insert query
    By jubitzu in forum PHP Development
    Replies: 1
    Last Post: January 29th, 2004, 11:12 AM
  4. How to insert NULL in MySQL using PHP?
    By arisman in forum PHP Development
    Replies: 8
    Last Post: January 13th, 2004, 02:28 AM
  5. Zip code validation
    By krwyth in forum MySQL Help
    Replies: 2
    Last Post: January 5th, 2004, 01:29 PM

IMN logo majestic logo threadwatch logo seochat tools logo