Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    May 2007
    Posts
    139

    Unanswered: to insert DATE TO DB/TABLE, how must inserted the date , what format ?

    When in phpMyAdmin I have

    Field Type
    dateplaced timestamp

    then in a command like
    Code:
    	$datenow = time();	 
    	//  $datenow = date("H:i j/m/y", $tsnow);  // p.516-7
    	$status="PENDING";
    	$email6=$_SESSION['email'];
    	$query = "INSERT INTO $BookingsTable VALUES(NULL,'$datenow',0,'$email6','$status','$datep','$dated','$Locationp','$Locationd','$days','$mostdaysrate','$vat','$off','$finalprice','$cat')";
    to insert DATE TO DB/TABLE, how must inserted the date , what format ?
    I insert timestamp but appears(in BROWSE DATAELECT * FROM `bookings2` LIMIT 0 , 30)
    0000-00-00 00:00:00 as field dateplaced ??? well, why appear zeros ?

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Im confused here
    ..do you men PHPMyAdmin or PHP
    timestamp is a reserved word
    you only need to encapsulate text / string columns
    usually dates should be in ISO format (yyyy/mm/dd @ hh:mm:ss)
    the @ hh:mm:ss is optional

    http://www.google.co.uk/search?hl=en...e+Search&meta=
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    May 2007
    Posts
    139
    I mean PHPMyAdmin.

    Field = dateplaced // [1] '$datenow'
    Type = timestamp

    I try insert number $datenow = time(); to second column, that i guest is a big number, but appears when i browse data as 0000-00-00 00:00:00 and NOT today's date , well ?

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    this
    Code:
    $datenow = time();	 
    	//  $datenow = date("H:i j/m/y", $tsnow);  // p.516-7
    	$status="PENDING";
    	$email6=$_SESSION['email'];
    	$query = "INSERT INTO $BookingsTable VALUES(NULL,'$datenow',0,'$email6','$status','$datep','$dated','$Locationp','$Locationd','$days','$mostdaysrate','$vat','$off','$finalprice','$cat')";
    looks like PHP to me. Im not aware you can run scripts like that in PHPMyAdmin
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the format for mysql is 'yyyy-mm-dd hh:mm:ss' -- no @
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    May 2007
    Posts
    139
    Please note: I want to keep track date/time , placed the booking, pickup date(car rent) and, dropoff date...I set these all fields to timestamp ... to save a date in db/table how I must insert date in what format(a big number) ? or what ?
    If I insert a big number to timestamp field this will show a date like
    'yyyy-mm-dd hh:mm:ss'
    in the table: "browse command" in phpmyadmin(mysql) ?

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    please do a SHOW CREATE TABLE for your table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    timestamp is a variant of datetime.. its set whenever the row is added or changed. so if you try to assign a value to a time stamp column you may have problems. if you have multiple timestamp columns they should all be set to the same value

    use datetime datatype in place
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    May 2007
    Posts
    139
    I try insert number $datenow = time(); to second column, that i guest is a big number, but appears when i browse data as 0000-00-00 00:00:00 and NOT today's date , well ?
    0000-00-00 00:00:00 == A timestamp field to appear this what must be inserted ?

    in my case I want to use time/date and compare it, appear only not expired bookings, sort by date etc ,... what to use ? what field type to use ?

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    use DATETIME datatype

    make sure you insert an actual datetime value, not a unix integer
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    May 2007
    Posts
    139
    WHEN I take current time from
    $datenow = time();
    how insert in DATETIME datatype ?

  12. #12
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    $DATENOW = format("Y-m-d H:i:s"); //FORMAT THE DATE IN MySQL form
    $SQL = "Insert into mytable (my, column, list, mydatecolumn) values (my, values, list, $DATENOW);";
    $sqlr=@mysql_query($SQL,$cnn) or die (echo ("myerrormessage".mysql_error());

    $cnn is the variable containing a valid MySQL connection
    I'd rather be riding on the Tiger 800 or the Norton

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    why generate the value in php and pass it to the database?

    why not use the database function?

    so instead of this --

    $DATENOW = format("Y-m-d H:i:s"); //FORMAT THE DATE IN MySQL form
    $SQL = "Insert into mytable (my, column, list, mydatecolumn) values (my, values, list, $DATENOW);";

    you would run only this --

    $SQL = "Insert into mytable (my, column, list, mydatecolumn) values (my, values, list, CURRENT_TIMESTAMP);";
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    May 2007
    Posts
    139
    DB field type: timestamp, HOW IS BEING SAVED AND APPEARED ? SAVED AS BIG INT ?

  15. #15
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    what does it matter how it is stored.. its a variant of a datetime column, one you cannot set yourself. think of it as a read only date time.

    it gets changed every time there are changes applied to that row.

    there are functions to manipulate that datatype both in MySQL and PHP.
    http://bugs.mysql.com/bug.php?id=24895

    it looks lke the datetime datatype is an 8 byte value, I'd expect it to be an integer being he number of clicks since an arbitary date (probably the Unix Epoch). you shouldn't be any direct mathmatical manipulation on any date time values, use the presupplied functions.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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