1. No Profile Picture
    Devshed Newbie (0 - 499 posts)
    I don't want to sound stupid about my lack knowledge of programing and PHP, I'm really new. So I have looked through all the PHP topics on this forum and searched places like devshed, PHPBuilder, the Wizards and Leon Atkinson's book it sitting in front of me. It's 3:3something a.m. and my brain has started to show signs of the Y2K bug.

    I want to format a date queried from my DB. I've managed to get the typical 2000-3-21 date but I need to rearange it to look like Mar. 21 2000. It's probably very simple but I can't seem to get a clear and simple answer with an example. I'm also learning Cold Fusion and it's a piece-o-cake to do this in that, but I want to learn PHP.

    The normal SELECT * from seminars_db stuff has been made and my WHILE loop is running. This is the code I have had the most success with.

    $ID = $row[0];
    $bdate = $row[1];
    $edate = $row[2];
    $presentation = $row[3];
    $place = $row[4];
    $city = $row[5];
    $state = $row[6];
    $sponsor = $row[7];
    $connect = $row[8];

    <snipped html>

    print date("M. j, Y",mktime($bdate));

    I know, this is wrong. At least I got the format right, the date was Dec. 31, 1969 on all records (what's up with that).

    I have learned that $bdate is retrieved as a string and that can't be used with the date function.? I have seen the UNIX_TIMESTAMP and the mktime commands mentioned here, and second queries for just the "bdate" field (not sure if that's effiecient) but Leon's book hasn't helped me and the forum has not answered it thorough enough that I could understand. There has been sucess with the basic date function . . .

    $Today = date("F d,Y");

    . . . and calling it later in the page, and I know I can use this against the "bdate" field. However, I don't need how many days until the event, I need the event M. j, Y pulled from the DB.

    Hey, it's past 4:00 a.m. Shouldn't I be wishing someone on the rock Happy New Year?

  2. #2
  3. No Profile Picture
    Devshed Newbie (0 - 499 posts)
    This is a bit of a kludge but:

    $dbmonth = (int)substr($curdate, strpos($curdate, "-") + 1, strrpos($curdate, "-") - strpos($curdate, "-") - 1);
    $dbday = (int)substr($curdate, strrpos($curdate, "-") + 1);
    $dbyear = (int)substr($curdate, 0, 4);

    print date("M. j, Y", mktime(0, 0, 0, $dbmonth, $dbday, $dbyear));
  4. #3
  5. No Profile Picture
    rod k
    Devshed Newbie (0 - 499 posts)
    The reason date() doesn't work for you is that date() requires a UNIX timestamp to work and you are using a MySQL timestamp. You can use the MySQL DATE_FORMAT function in your query to format the date for you.

    $result=mysql_query("select DATE_FORMAT(bdate,%a. %e, %Y) from seminars_db");

    Where bdate is the column name of the timestamp you wish to convert. The %a returns the abbreviated month name, the %e is the day of the month 1...31 and %Y returns the 4 digit year. The . and , are returned as is so from 2000-3-21 you'd get:

    Mar. 31, 2000

    You can see more formatting options in the mysql manual under date/time functions (section 7.3.10 of the 3.22 manual or section 7.4.11 of the 3.23 manual).

    Of course, this means you won't be able to use * in your select but have to ask for each column.
  6. #4
  7. No Profile Picture
    rod k
    Devshed Newbie (0 - 499 posts)
    Ooops. I don't know why I assumed that you were using MySQL.... well, if you are, there you go. If not, ignore my previous post.
  8. #5
  9. No Profile Picture
    Devshed Newbie (0 - 499 posts)
    It Works!

    Sorry to get so wordy in my question, I was getting a little slap-happy.

    Am I right to assume that CBOLT's answer uses PHP to reformat the date by a generic manipulation of a string and ROD K uses MySQL to reformat the date from the query? I really want to learn this, so reply if you have the time.

    BTW, I am using mySQL and I did read over the 3.23 manual section 7.4.11 but I didn't get how this was used. It didn't seem effecient to name all the fields in the query b/c Cold Fusion allows me to manipulate just about any feild with SELECT *. I'm not saying CF is better, it has just been my progression of learning languages.
  10. #6
  11. No Profile Picture
    Devshed Newbie (0 - 499 posts)
    Yes, my answer was using string manipulation, and rod k's answer (or the right answer :-) used mysql... never knew about the DATE_FORMAT function until now.
  12. #7
  13. No Profile Picture
    rod k
    Devshed Newbie (0 - 499 posts)
    Well, I wouldn't say mine was any more right than cbolt's. Just a matter of opinion. Using PHP to format can be lengthy (and takes longer, usually) but can make your query easier to build. To each his own.

    The added benefit to doing it in PHP as cbolt described is you can always make it a function and include() the function in the necessary scripts. Of course, the optimal solution would be a compiled plug in of a function that could be added to PHP that would allow date formatting of just about any MySQL timestamp. Unfortunately, I'm not enough of a C programmer to take that on.
  14. #8
  15. No Profile Picture
    Devshed Newbie (0 - 499 posts)
    test . . please ignore.

Similar Threads

  1. Replies: 7
    Last Post: December 24th, 2003, 03:52 AM
  2. Date formatting
    By williamcrawley in forum ASP Programming
    Replies: 1
    Last Post: December 12th, 2003, 06:02 AM
  3. formatting date from mysql
    By ashabi in forum PHP Development
    Replies: 3
    Last Post: November 4th, 2003, 03:14 AM
  4. Formatting a MySQL date into PHP
    By Haakon in forum PHP Development
    Replies: 4
    Last Post: October 19th, 2003, 11:50 AM
  5. Time formatting without date() ??
    By etones in forum PHP Development
    Replies: 1
    Last Post: September 21st, 2003, 10:16 AM

IMN logo majestic logo threadwatch logo seochat tools logo