SitePoint Sponsor

User Tag List

Results 1 to 18 of 18
  1. #1
    SitePoint Zealot
    Join Date
    Feb 2008
    Posts
    165
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Format Date in 2 ways on the same page

    I need to format a date retrieved from a mysql query, and do it in two places. The two formats I'm looking for are:
    Code:
    (l, F j, Y)
    and
    Code:
    (D M J)
    Originally I formatted the date directly in the query like this:
    Code:
    SELECT DATE_FORMAT(event_date, '%W, %M %d, %Y') AS date FROM events
    But then realized I need to format it 2 ways on the same page, and that it might be easier to handle the default date format from the query (YYYY-MM-DD) and format it using PHP. Can you help me format the date when using a date value from the query?
    Last edited by ggeiger; Nov 24, 2009 at 11:35. Reason: misspelling

  2. #2
    Web Professional
    Join Date
    Oct 2008
    Location
    London
    Posts
    862
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    SELECT UNIX_TIMESTAMP(event_date) AS date FROM events
    And then, in PHP, format it using date function.

  3. #3
    SitePoint Zealot
    Join Date
    Feb 2008
    Posts
    165
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the response. I realize I should be using date(); to format within PHP, but I'd like an example of how to grab the data from event_date and use it with date(); in two separate places, using the formats described above.

  4. #4
    SitePoint Wizard PHPycho's Avatar
    Join Date
    Dec 2005
    Posts
    1,201
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by ggeiger View Post
    Thanks for the response. I realize I should be using date(); to format within PHP, but I'd like an example of how to grab the data from event_date and use it with date(); in two separate places, using the formats described above.
    You can even use single query to format two date (for example say
    .... AS date1
    ,.... AS date2

  5. #5
    SitePoint Zealot
    Join Date
    Feb 2008
    Posts
    165
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Just tried that. Threw me a mysql error...
    Warning: mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given

  6. #6
    SitePoint Zealot
    Join Date
    Feb 2008
    Posts
    165
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    My trouble is while reading the PHP manual regarding date(); the examples given show how to use the current date from the server. I need an example of how to grab the date from a database field that uses the date format, and plug it into date();

    Could you provide an example of that?

  7. #7
    SitePoint Guru
    Join Date
    Jan 2005
    Location
    heaven
    Posts
    953
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    take a look at strptime and strftime
    Creativity knows no other restraint than the
    confines of a small mind.
    - Me
    Geekly Humor
    Oh baby! Check out the design patterns on that framework!

  8. #8
    SitePoint Wizard
    Join Date
    Nov 2005
    Posts
    1,191
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by decowski View Post
    Code:
    SELECT UNIX_TIMESTAMP(event_date) AS date FROM events
    And then, in PHP, format it using date function.
    mentioned above, this would also work if all you want to do is use timestamps in the php

  9. #9
    Web Professional
    Join Date
    Oct 2008
    Location
    London
    Posts
    862
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by ggeiger View Post
    My trouble is while reading the PHP manual regarding date(); the examples given show how to use the current date from the server. I need an example of how to grab the date from a database field that uses the date format, and plug it into date();

    Could you provide an example of that?
    I've given you an example.

    Pass your date through UNIX_TIMESTAMP() function in your query. This way you will get a unix time stamp (instead of a formatted date) which can be passed as a second parameter to the date() function in PHP.

  10. #10
    SitePoint Wizard cranial-bore's Avatar
    Join Date
    Jan 2002
    Location
    Australia
    Posts
    2,634
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    if($result $db->query("SELECT UNIX_TIMESTAMP(event_date) AS date FROM events")) {
        while(
    $row $result->fetch_assoc()) {
            echo 
    "<ul>",
                
    "<li>Timestamp: " $row['date'] . "</li>",
                
    "<li>" date('l, F j, Y'$row['date']) . "</li>"
                
    "<li>" date('D M J'$row['date']) . "</li>"
            
    "</ul>";
        }
    }
    else echo 
    $db->error
    Btw, I doubt D M J is the formatting string you want. It produces output like Monday June J

  11. #11
    SitePoint Zealot
    Join Date
    Feb 2008
    Posts
    165
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by decowski View Post
    I've given you an example.

    Pass your date through UNIX_TIMESTAMP() function in your query. This way you will get a unix time stamp (instead of a formatted date) which can be passed as a second parameter to the date() function in PHP.
    I understand what you mean now and thanks for the example. This will work fine. It's amazing how fast one can pick this stuff up, but only to find there is a lot more to learn. I'm still climbing!

  12. #12
    SitePoint Zealot
    Join Date
    Feb 2008
    Posts
    165
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by cranial-bore View Post
    PHP Code:
    if($result $db->query("SELECT UNIX_TIMESTAMP(event_date) AS date FROM events")) {
        while(
    $row $result->fetch_assoc()) {
            echo 
    "<ul>",
                
    "<li>Timestamp: " $row['date'] . "</li>",
                
    "<li>" date('l, F j, Y'$row['date']) . "</li>"
                
    "<li>" date('D M J'$row['date']) . "</li>"
            
    "</ul>";
        }
    }
    else echo 
    $db->error
    Btw, I doubt D M J is the formatting string you want. It produces output like Monday June J
    Thanks, cranial-bore, for taking this one step further. Once I saw your code I could see how the date(); could be used to include the $row['date'] from the query. Awesome!
    Oh, and I didn't mean to use D M J in the php code, that format was meant for the mysql string.

  13. #13
    SitePoint Zealot
    Join Date
    Feb 2008
    Posts
    165
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Any idea how I could break the date up into 3 variables? I'm prepopulating a form with data for the user to edit and resubmit, and the "event date" for my form is a drop down list for month, for day and for year. I'll need to break up the date and check the month against $row, the day against $row and the year against $row for a match, then add selected="selected" to the <option> tag.

  14. #14
    SitePoint Wizard rguy84's Avatar
    Join Date
    Sep 2005
    Location
    Durham, NC
    Posts
    1,659
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)
    I usually let my query do the formatting:
    Code SQL:
    SELECT DATE_FORMAT('YourField','%M') AS 'eventMonth'...
    then
    PHP Code:
    Echo $row['eventMonth']; 
    I am also trying to make the drop downs auto select the month/day/year. No progress yet
    Ryan B | My Blog | Twitter

  15. #15
    SitePoint Wizard
    Join Date
    Nov 2005
    Posts
    1,191
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by rguy84 View Post
    I am also trying to make the drop downs auto select the month/day/year. No progress yet
    PHP Code:
    $selected = ($eventdate == 4) ? ' selected="selected"' '';
    echo 
    '<option value="4"'.$selected'.>' 
    Just do the same thing as your sql with php date() to find out what day it is etc.

  16. #16
    Unobtrusively zen silver trophybronze trophy
    paul_wilkins's Avatar
    Join Date
    Jan 2007
    Location
    Christchurch, New Zealand
    Posts
    14,729
    Mentioned
    104 Post(s)
    Tagged
    4 Thread(s)
    That would be with todays date.
    http://php.net/manual/en/function.date.php

    Here's an example for the days.

    Code javascript:
    $now = strtotime('now');
    $day = intval(date('j'));
     
    $dayOptions = '';
    for ($i = 1; $i <= 31; $i++) {
        $selected = '';
        if ($i === $day) {
            $selected = 'selected="selected"';
    	}
        $dayOptions .= '<option '.$selected.'name="day" value="'.$i.'">'.$i.'</option>';
    }
    echo '<select name="day">'.$dayOptions.'</select>';
    Programming Group Advisor
    Reference: JavaScript, Quirksmode Validate: HTML Validation, JSLint
    Car is to Carpet as Java is to JavaScript

  17. #17
    SitePoint Zealot
    Join Date
    Feb 2008
    Posts
    165
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the responses and for the code! It's been very helpful to see your examples and try it out on my own.

    I've now gotten the date to split up into month, day and year and have them pre-selected in the form. A little background, my page is meant to be an "edit date" page where the user can click on an event, and the event ID is carried over in the URL to this page. Here is my sql query and my form:

    PHP Code:
    $q "SELECT UNIX_TIMESTAMP(event_date) AS date WHERE event_id='{$_GET['event_id']}'";
    $r = @mysqli_query($dbc$q);

    if (
    mysqli_num_rows($r) == 1
    // a valid event id, show the form

        
    $row mysqli_fetch_array($rMYSQLI_ASSOC);
            
           echo 
    '<form action="edit_event.php" method="post">',
    '<label class="required">Date</label>';
                
                
    // STICKY MONTH //
                
    $months = array(=> 'January''February''March''April''May''June''July''August''September''October''November''December');
        
                
    $event_month date('n'$row['date']);
                
                echo 
    '<select name="month">',
                
    '<option value=""></option>';
                foreach (
    $months as $key => $value) {
                    if(
    $key == $event_month) {
                        echo 
    '<option value="'.$key.'" selected>'.$value.'</option>';
                    } else {
                        echo 
    '<option value="'.$key.'">'.$value.'</option>';
                    }
                }
                echo 
    '</select>';
                
                
    // STICKY DAY //
                
    $event_day date('j'$row['date']);
                echo 
    '<select name="day">';
                for (
    $day 1$day <= 31$day++) {
                    if (
    $day == $event_day) {
                        echo 
    '<option value="'.$day.'" selected>'.$day.'</option>';
                    } else {
                        echo 
    '<option value="'.$day.'">'.$day.'</option>';
                    }
                }
                echo 
    '</select>';
                
                
    // STICKY YEAR //
                
    $event_year date('Y'$row['date']);
                echo 
    '<select name="year">';
                for (
    $year 2010$year <= 2012$year++) {
                    if (
    $year == $event_year) {
                        echo 
    '<option value="'.$year.'" selected>'.$year.'</option>';
                    } else {
                        echo 
    '<option value="'.$year.'">'.$year.'</option>';
                    }
                }
                echo 
    '</select></form>'
    I hope this helps!!!

  18. #18
    I solve practical problems. bronze trophy
    Michael Morris's Avatar
    Join Date
    Jan 2008
    Location
    Knoxville TN
    Posts
    2,053
    Mentioned
    66 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by ggeiger View Post
    I need to format a date retrieved from a mysql query, and do it in two places. The two formats I'm looking for are:
    Code:
    (l, F j, Y)
    and
    Code:
    (D M J)
    Originally I formatted the date directly in the query like this:
    Code:
    SELECT DATE_FORMAT(event_date, '%W, %M %d, %Y') AS date FROM events
    But then realized I need to format it 2 ways on the same page, and that it might be easier to handle the default date format from the query (YYYY-MM-DD) and format it using PHP. Can you help me format the date when using a date value from the query?
    SELECT is a bit more powerful than most people give it credit for. If you need the same field in two formats then simply select the same field twice using two different aliases.

    Code:
    SELECT DATE_FORMAT(event_date, '%W, %M %d, %Y') AS date,
    DATE_FORMAT(event_date, '[other format here]') AS shortdate 
    
    FROM events
    Whatever you need the PHP array member to be named goes after the AS clause. You can put the results of any of MySQL's functions into a PHP array member this way, or even put a flat string in like in this example.

    Code:
    SELECT 'default string' AS string
    FROM events
    That will give you an array with 'default string' and a number of elements equal to the total row count of the events table.

    Now, all this said I normally store times as timestamps and apply formatting using PHP date because the format of the date is a function of the view code, not the model code (which is part of MySQL). But if you aren't using an MVC approach having MySQL do the formatting will be quicker.


Tags for this Thread

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
  •