SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Enthusiast
    Join Date
    Nov 2000
    Location
    Sydney, Australia
    Posts
    65
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hello there

    Does anybody know how to convert a mySQL DATE data type into a nice format, for example

    dateConvert(2001-1-10)

    returns

    Wednesday, 10 January 2001

    And also, if I wanted to go through dates using a for loop, how would I be able to do that?

    e.g
    for ($d=2001-1-10; $d < 2001-1-17; $d ??) {
    steps...
    }

    Thanks in advance for any help!!
    Ardi N
    http://www.myringtones.net (down again) - Free Ringtones for your Nokia and Ericsson GSM phone : brought to you by The Ringtone Factory

  2. #2
    SitePoint Evangelist
    Join Date
    Jul 2000
    Location
    Warwickshire, England
    Posts
    557
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    if your date is in the year - month - day format, you can use this.. (if you have the dates in a different order, swap the order in mktime).

    $mysqldate = explode ("-", $mysqldate);
    $prettydate = date("l, j F Y",mktime(0,0,0,$mysqldate[1],$mysqldate[2],$mysqldate[0]));

  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)
    As fot the first part

    SELECT DATE_FORMAT(datefield, '%W, %e %b %Y') as date from tablename

    The second part will be a bit more tricky since you can't run a for loop in MySQL you would need to convert your timestamp into unixtime then use mktime to create the days you could do something such as,

    if the value returned from MySQL was 2001-1-10

    <?
    $data = explode("-", $mysqldatefield);
    $year = $data[0];
    $month = $data[1];
    $day = $data[2];
    $unixtime = mktime(0, 0, 0, $month, $day, $year);
    //print today's date
    print DATE("l, d M Y", $unixtime)."<br>";
    //print the date in the smae format for the next one hundred days
    for($i=1;$i<100;$i++) {
    print DATE("l, d M Y", mktime(0, 0, 0, DATE($month), DATE($day + $i), DATE($year)))."<br>";
    }
    ?>
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  4. #4
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    sorry PeterW I didn't see your post till after I posted mine. Yeah your way works however there are some built in MySQL functions so you can do it in the query. Until I found that out I was using your method all the time. Just a note in case you care.
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  5. #5
    SitePoint Enthusiast
    Join Date
    Nov 2000
    Location
    Sydney, Australia
    Posts
    65
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you very much PeterW and freddy for you help =)

    Those solutions have worked like a charm.
    http://www.myringtones.net (down again) - Free Ringtones for your Nokia and Ericsson GSM phone : brought to you by The Ringtone Factory

  6. #6
    SitePoint Evangelist
    Join Date
    Jul 2000
    Location
    Warwickshire, England
    Posts
    557
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by freddydoesphp
    sorry PeterW I didn't see your post till after I posted mine. Yeah your way works however there are some built in MySQL functions so you can do it in the query. Until I found that out I was using your method all the time. Just a note in case you care.
    Thanks for pointing it out
    Yep, I care...

    * quickly changes that code in scripts he has written using it..

  7. #7
    SitePoint Evangelist
    Join Date
    May 2000
    Location
    Canada
    Posts
    533
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i dont like either implementations above

    mysql has a UNIX_TIMESTAMP function which can be used in the form of:

    SELECT UNIX_TIMESTAMP(date_column) as date_timestamp,id,etc FROM table

    it gives you a standard unix timestamp which you can pass to date() .. better than exploding the data ...
    cogito, ergo sum

  8. #8
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Nice one Vinay! but why not use DATE_FORMAT if all you want is a formatted date form a date column for the second part of doing the loop your function is great, but for the first you would still need tio run the query then use DATE() instead of doing it all in one.
    Please don't PM me with questions.
    Use the forums, that is what they are here for.


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
  •