SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Addict
    Join Date
    Feb 2003
    Location
    Berlin
    Posts
    370
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Changing ISO date format into European date format

    I'm having a mysql database with two fields containing dates ( a start and a end date of an event)
    The MySQL database writes the format as yyyy-mm-dd (ISO standart).

    When the records are shown on my webpage I would like to have the european date format dd.mm.yyyy and the day of the week shown.

    How can I do this?

    Thanks
    Floezen

  2. #2
    SitePoint Wizard
    Join Date
    Oct 2001
    Posts
    2,686
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You can format the date in your query using MySQL function DATE_FORMAT().
    Code:
    SELECT DATE_FORMAT(date, '%d.%m.%Y') AS dateFormatted FROM tbl
    That should do it.

    -Helge

  3. #3
    SitePoint Addict
    Join Date
    Feb 2003
    Location
    Berlin
    Posts
    370
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks, didn't think to do this with sql itself...

    Now it seems simple to do similar with the weekday:


    PHP Code:
    WEEKDAY(start) AS startdayFormatted 
    This prints out a number between 0 and 6 (yes I know, select DAYNAME(start); would print out the name, but I need the name in German...), so I thougt I could do something like:

    PHP Code:
    $dayofweek=startdayFormatted;

    if (
    $dayofweek == 0) {
    $dayofweek="Montag";
    }
    elseif (
    $dayofweek == 1) {
    $dayofweek="Dienstag";
    }
    elseif (
    $dayofweek == 2) {
    $dayofweek="Mittwoch";
    }
    elseif (
    $dayofweek == 3) {
    $dayofweek="Donnerstag";
    }
    elseif (
    $dayofweek == 4) {
    $dayofweek="Freitag";
    }
    elseif (
    $dayofweek == 5) {
    $dayofweek="Samstag";
    }
    elseif (
    $dayofweek == 6) {
    $dayofweek="Sonntag";
    }
    else {
    $dayofweek="nothing";

    to get the actual day in my language and place this with
    PHP Code:
    <?=$row[$dayofweek]?>
    into the right position on my page.

    Alltogether it would looke like this:

    PHP Code:
    $rs = new MySQLPagedResultSet("SELECT *, DATE_FORMAT(start,'%d.%m.%Y') AS startFormatted, WEEKDAY(start) AS startdayFormatted
                                FROM event_cal , event_addr
                                WHERE addr_id=event_addr.id 
                                AND start<='$today' AND end>='$today' Order by end",$resnumber,$cnx);


    $dayofweek=startdayFormatted;

    if ($dayofweek == 0) {
    $dayofweek="Montag";
    }
    elseif ($dayofweek == 1) {
    $dayofweek="Dienstag";
    }
    elseif ($dayofweek == 2) {
    $dayofweek="Mittwoch";
    }
    elseif ($dayofweek == 3) {
    $dayofweek="Donnerstag";
    }
    elseif ($dayofweek == 4) {
    $dayofweek="Freitag";
    }
    elseif ($dayofweek == 5) {
    $dayofweek="Samstag";
    }
    elseif ($dayofweek == 6) {
    $dayofweek="Sonntag";
    }
    else {
    $dayofweek="nothing";
    }


    <table width="500" border="0" cellspacing="0" cellpadding="0">
    <tr>
                    <td colspan="3" width="500">
                        <hr>
                    </td>
                </tr>
    <?php while ($row $rs->fetchArray()): ?>
                <tr>
                    <td width="300"><?=$row['title']?></td>
                    <td width="100"><?=$row['start']?></td>
                    <td width="100"><?=$row['end']?></td>
                </tr>
                <tr>
                    <td width="300"><i><?=$row['location']?></i></td>
                    <td width="100"><?=$row['startFormatted']?></td>
                    <td width="100"><?=$row[$dayofweek]?></td>
                </tr>
                <tr>
                    <td colspan="3" width="500">
                        <hr>
                    </td>
                </tr>
    <?php endwhile; ?>
            </table>
    but doesn't work

    How would one solve this problem?!

    Hoping for better solutions

    Florian

  4. #4
    SitePoint Addict
    Join Date
    Feb 2003
    Location
    Berlin
    Posts
    370
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK found the error in my way of thinking...
    must set the if - else construction into the array:

    PHP Code:
    $rs = new MySQLPagedResultSet("SELECT *, DATE_FORMAT(start,'%d.%m.%Y') AS startFormatted, WEEKDAY(start) AS startdayFormatted
                                FROM event_cal , event_addr
                                WHERE addr_id=event_addr.id 
                                AND start<='$today' AND end>='$today' Order by end",$resnumber,$cnx);


    $dayofweek=startdayFormatted;

    <table width="500" border="0" cellspacing="0" cellpadding="0">
    <tr>
                    <td colspan="3" width="500">
                        <hr>
                    </td>
                </tr>
    <?php while ($row $rs->fetchArray()): ?>
                <tr>
                    <td width="300"><?=$row['title']?></td>
                    <td width="100"><?=$row['start']?></td>
                    <td width="100"><?=$row['end']?></td>
                </tr>
                <tr>
                    <td width="300"><i><?=$row['location']?></i></td>
                    <td width="100"><?=$row['startFormatted']?></td>
                    <td width="100"><?
    if ($row[$dayofweek] == 0) {
    echo(
    "Montag");
    }
    elseif (
    $row[$dayofweek] == 1) {
    echo(
    "Dienstag");
    }
    elseif (
    $row[$dayofweek] == 2) {
    echo(
    "Mittwoch");
    }
    elseif (
    $row[$dayofweek] == 3) {
    echo(
    "Donnerstag");
    }
    elseif (
    $row[$dayofweek] == 4) {
    echo(
    "Freitag");
    }
    elseif (
    $row[$dayofweek] == 5) {
    echo(
    "Samstag");
    }
    elseif (
    $row[$dayofweek] == 6) {
    echo(
    "Sonntag");
    }
    else {
    echo(
    "");
    }

    ?></td>
                </tr>
                <tr>
                    <td colspan="3" width="500">
                        <hr>
                    </td>
                </tr>
    <?php endwhile; ?>
            </table>
    If someone knows a more elegant way, I would be glad to hear about it


    Floezen

  5. #5
    midnight coder
    Join Date
    Dec 2000
    Location
    The flat edge of the world
    Posts
    838
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    How come all the other rows you're using $row['title'], but you're not using $row['startdayFormatted']?

    Your query just retuns a result set, not an array, so you can't just do $dayOfWeek = startDayFormatted. What's startDayFormatted? It's not even a variable, even if it was meant to be $startDayFormatted, $startDayFormatted doesn't hold any value 'cos you never assigned anything to that var.

    To save you from the if/elseif, try something like
    PHP Code:
    $dates = array('mon''tue''wed''thur''fri''sat''sun');

    while (
    $row $rs->fetchArray()) {
    echo 
    $dates[$row['startdayFormatted']];

    Work smarter, not harder. -Scrooge McDuck

  6. #6
    SitePoint Addict
    Join Date
    Feb 2003
    Location
    Berlin
    Posts
    370
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Robo
    How come all the other rows you're using $row['title'], but you're not using $row['startdayFormatted']?
    The MySQL query returns for the field start (which is a Date in ISO Format) the number of the day of the week when writing WEEKDAY(start). Then I define an alias for it: WEEKDAY(start) AS startdayFormatted.

    When I would use row['startdayFormatted'], I would get a number between 1 and 7. I don't think it would satisfy our visitors to get "1" instead of "Monday".
    Since our users native language is German, I would rather have "Monday" called "Montag".

    Your query just retuns a result set, not an array, so you can't just do $dayOfWeek = startDayFormatted. What's startDayFormatted? It's not even a variable, even if it was meant to be $startDayFormatted, $startDayFormatted doesn't hold any value 'cos you never assigned anything to that var.
    Why can't i do $dayOfWeek = startDayFormatted ???
    PHP Code:
    $rs = new MySQLPagedResultSet("SELECT *, DATE_FORMAT(start,'%d.%m.%Y') AS startFormatted, WEEKDAY(start) AS startdayFormatted 
    assignes the day of the week, as explained above. Thus the variable is set. And by the way, my construction works!

    But the question is: is there a better way to solve the language problem when using daynames???

    Any suggestions? I'm sure I'm not the only one with this problem?!

    Thanx,
    Fl÷zen

  7. #7
    midnight coder
    Join Date
    Dec 2000
    Location
    The flat edge of the world
    Posts
    838
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I assume $rs = new MySQLPagedResultSet() returns some sort of MySQL result right? It does not return an array, or a string. $rs->fetchArray() returns an array.

    In this line:

    $dayOfWeek = startDayFormatted

    What is startDayFormatted? it's not a variable 'cos it doesn't have a $ sign, so PHP will think it's a constant. You never defined a constant call startDayFormatted.

    What happens when you run my code? You said $row['startdayFormatted'] will return a number between 1 to 7, so by having a $dates array like I have in my code above (where you replace 'mon', 'tue' to German words), the line:

    echo $dates[$row['startDayFormatted']];

    will effectively be echoing $dates[1], $dates[2], eg 'mon', 'tue', which is what you want.

    Work smarter, not harder. -Scrooge McDuck

  8. #8
    SitePoint Addict
    Join Date
    Feb 2003
    Location
    Berlin
    Posts
    370
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Robo!

    Your tipp makes it much easier!
    PHP Code:
    $dates = array('Montag', 'Dienstag', 'Mittwoch', 'Donnerstag', 'Freitag', 'Samstag', 'Sonntag');

    <?=$dates[$row['startdayFormatted']];?>
    Solves my problem

    What is startDayFormatted? it's not a variable 'cos it doesn't have a $ sign, so PHP will think it's a constant. You never defined a constant call startDayFormatted.
    Actually I dont need it anymore, but just because im interested (and very new to php), is
    $dayOfWeek = startDayFormatted
    not definig the variable $dayOfWeek with the result returned by the Database query? (startDayFormatted is a DB field)


    Fl÷zen

  9. #9
    midnight coder
    Join Date
    Dec 2000
    Location
    The flat edge of the world
    Posts
    838
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    $dayOfWeek = startDayFormatted does not define the variable $dayOfWeek with the result returned by the Database query.

    To access stuff you got from db, you turn each row into an array, and the elements in that array are the fields.
    Work smarter, not harder. -Scrooge McDuck


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
  •