SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Addict
    Join Date
    Dec 2000
    Location
    BOSTON MA
    Posts
    335
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hi, i need to insert a date value and a datetime value into the db as part of a user's information.

    these values are going to be used to store/display the date when the user became a member (using the date field) and the date and time of their last login (using datetime).

    i've been doing some research a to learn how to handle the date in mysql and so far the only way i learned to insert the date is by defining the variable $date_join as $date_join = date("y.m.d"); and then inserting $date_join long with other values.

    what i've also learned is that mysql is only inserting the date value as yyyy-mm-dd. and it will only insert the date when the variable $date_join is defined as date("y.m.d"); and not if it's defined as NULL.

    the problem is that i want to display the date as mm-dd-yy and not as yyyy-mm-dd. i haven't even gotten around to dealing with the datetime value yet.

    can anyone help me with this? i need to know if i'm inserting the date into the db correctly and also how to format the outputted format of the date.

    you don't need to write a whole script for me, just a couple of examples will do. i'd really like to learn from this.

    thanks in advance.
    . . . chris

  2. #2
    SitePoint Addict
    Join Date
    Dec 2000
    Location
    BOSTON MA
    Posts
    335
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    someone? anyone?

    noone knows anything about the date in mysql?

    any tidbit of info would be appreciated. just to point me in the right direction?

    oh well ....
    . . . chris

  3. #3
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Chris I am a little fuzzy about what you are trying to accomplish. But let's tackle the date thing, I would suggest storing the dates as a unixtime stamp. You can manipulate them to look like anything you want but a uniztimestamp is down to the second so it will be much easier to deal with.

    Example 1:
    How to convert mm.dd.yyyy to unixtime
    PHP Code:
    $date strtotime("2001-3-5");
    $date date("m-d-Y"$date); 
    Example 2:
    How to make unixtime from three values month, day and year
    PHP Code:
    $month 3;
    $day 5;
    $year 2001;
    $date mktime(0,0,0,$month,$day,$year); 


    Example 3: how to work with timestamps in MySQL
    PHP Code:
    $result mysql_query("SELECT DATE_FORMAT(datefield, '%m-%d-%y) as newdate from tablename"); 
    This will print mm-dd-yy from the unix timestamp stored in the field.



    For more on formatting dates in MySQL see the manual
    http://www.mysql.com/documentation/m...time_functions
    Last edited by freddydoesphp; Mar 5, 2001 at 19:15.
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  4. #4
    SitePoint Addict
    Join Date
    Dec 2000
    Location
    BOSTON MA
    Posts
    335
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks so much for the reply.
    what i'm trying to accomplish is this:

    1- the user fills out a form.
    2- the data is inserted into the db.
    3- along with this data the date is inserted to record when the user submitted. this date will be display on another page in this format " Bob has been a member since mm.dd.yy " . this date will only be inserted/updated once at signup.
    4- i want a timestamp inserted into the db at signup also. this will be updated everytime the user logs in and updates his/her information.
    5- the timestamp will be used for admin purposes such as " Bob last logged in on $timestamp ".

    one of the problems i was having is that on the "display" page, when all the data is retrieved from the db, they are retrieved using a while loop and array, displaying a certain number of records per page. the problem is that i can retrieve the date fine, except that i don't know how to format it. i'm not even sure i'm inserting it right.

    in the db there is the column date_join defined as DATE, but when i try to set the default as NOW(), it always defaults to zeros.

    i insert it like this: $date_join = date(y.m.d) and then insert $date_join along with all the other variables.

    sorry for such a long post. i will experiment with what you gave me and hopefully make sense of it.
    could you just tell me if i'm inserting the date into the db correctly?

    again, many many thanks for your help. i hope to repay you someday.
    . . . chris

  5. #5
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Okay so I made a table with the following structure

    +-------+------------------+------+-----+---------+----------------+---------------------------------+
    | Field | Type | Null | Key | Default | Extra | Privileges |
    +-------+------------------+------+-----+---------+----------------+---------------------------------+
    | ID | int(11) unsigned | | PRI | NULL | auto_increment | select,insert,update,references |
    | date1 | datetime | YES | | NULL | | select,insert,update,references |
    | date2 | date | YES | | NULL | | select,insert,update,references |
    +-------+------------------+------+-----+---------+----------------+---------------------------------+



    My insert query looks like this

    PHP Code:
    $result mysql_query("INSERT into datetest set date1 = NOW(), date2 = NOW()"); 
    Now my table has this in it

    +----+---------------------+------------+
    | ID | date1 | date2 |
    +----+---------------------+------------+
    | 1 | 2001-03-05 18:29:19 | 2001-03-05 |
    +----+---------------------+------------+



    one of the problems i was having is that on the "display" page, when all the data is retrieved from the db, they are retrieved using a while loop and array, displaying a certain number of records per page. the problem is that i can retrieve the date fine, except that i don't know how to format it. i'm not even sure i'm inserting it right.
    So that is how I would store it now for retrieveng it, if you format the date in the query you won't have to worry about doing it in the while loop. It will come from the wquery in the correct format.

    SELECT DATE_FORMAT(date2, '%m.%d.%y') as date_joined from tablename



    Hope that helps.
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  6. #6
    SitePoint Addict
    Join Date
    Dec 2000
    Location
    BOSTON MA
    Posts
    335
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok, once again, you are my hero. i owe you another one

    i had to wedge the select date_format function into the script to get it to work with the existing while loop and it worked.

    i was able to output the date joined along with other data with bit of weaking in my code (your code was fine) and it worked.

    the only thing that was a little weird was when i was trying to output the datetime similar to the date format (eg. %m. %d. %y %g. %i. %s")

    the g or G (being the hour in 12 and 24 hour format w/out leading zeros) would not work. but h and H (the hour in 12 and 24 hour format with leading zeros) worked no problem.
    it's not that big of a deal but weird anyway.

    one last important question please. one of my dilemas in learning php and mysql is that when i come across a problem such as this post, i don't know whether it's a php issue or a mysql issue.
    for example this line :SELECT DATE_FORMAT(date2, '%m.%d.%y') as date_joined from tablename.

    how much of that is php and how much is mysql? specifically DATE_FORMAT. is it a php or mysql function? should i just shut it and grab me a mysql book?

    seriously though, thanks. i owe you alot.
    . . . chris

  7. #7
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    No worries Chris, the DATE_FORMAT() function is indeed a MySQL function. There are a ton of functions built right in to MySQL that one can use. My motto has always been that doing it in the query will always be faster than doing it with php after getting the data out of the database. So whether you form your date in the query or after the data is retrieved is a personal choice, but for efficiency's sake I always opt to do it in the query when I can.
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  8. #8
    SitePoint Addict
    Join Date
    Dec 2000
    Location
    BOSTON MA
    Posts
    335
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    freddy, hi.

    guess what? i've just spent the last 4 hrs trying to figure out why i couldn't format the date certain ways using the date_format() function.

    certain format strings would work and other ones wouldn't. i consulted the php site, a couple of books and searched through about a million post here about date formatting.

    well, to keep this short, after all that time i decided to check out the mysql site for some info on date_format and of course *bingo bango* problem gone.

    now, i'm not upset or anything and i'm sure you didn't do this on purpose because i saw many posts that were "explaining" date_format() but it would've saved me alot of time if i'd known that the php and mysql date formatting strings are different.

    so instead of getting " Mar 2001 " i was getting " March 2001 " or " r 2001 ".

    again, please don't take this the wrong way, i just wanted to let you and others know that date formatting uses different characters.

    so if anyone else is reading this and has had the same problem, now you know.

    freddydoesphp is still my hero .......
    . . . chris

  9. #9
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Chris I didn't take what you said the wrong way but let me show you how I am confident that if you really though about it you could have figured that out in a bout 5 minutes lets take this line

    PHP Code:
    $result mysql_query("SELECT DATE_FORMAT(date2, '%m.%d.%y') as date_joined from tablename"); 
    as opposed to
    PHP Code:
    $newday date("Y-m-d"$timestamp); 

    There is two thing that should tell you right away that this DATE_FORMAT is not a php function, first being that it is in between quotes so it can't be a php function and the second that it references a field name not a variable like the second. If the date2 was $date2, then I could see the confusion, Lastly this line:

    PHP Code:
    Example 3how to work with timestamps in MySQL 
    PHP
    :--------------------------------------------------------------------------------
    $result mysql_query("SELECT DATE_FORMAT(datefield, '%m-%d-%y) as newdate from tablename"); 

    Notice the bit about how timestamps work in MySQL. I am sure I wasn't as clear as I could have been, but there are things that people can do on their end too, just food for thought.
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  10. #10
    SitePoint Addict
    Join Date
    Dec 2000
    Location
    BOSTON MA
    Posts
    335
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    what makes me feel more like an idiot (i just noticed this now) is that i specifically asked you wether date_format was a php or mysql function and you TOLD me that it was in fact a mysql function.

    so the blame falls on me.
    that's what happens when all someone really wants is to get the script to work and they don't pay as close attention as they think they are.

    if i had bothered to actually take a step back and really look at what i was doing, i probably wouldh've noticed sooner. it was a good learning experience though. now i know.

    thanks for the explanation. it helps to have a visual.
    . . . chris


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
  •