Web Analytics Made Easy -
StatCounter date order - CodingForum

Announcement

Collapse
No announcement yet.

date order

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • date order

    at the moment, i'm using this code:

    PHP Code:
    $postSql mysql_query("SELECT *, DATE_FORMAT(date, \"%F %d%S, %Y %h:%i %A\") FROM `post` WHERE `threadid` = '$id' ORDER BY `date` ASC"); 
    but it orders the items by month first, then the date/time

    This is for a forum so the order needs to be oldest to newest, any ideas?

    Thanks
    Matt Davenport - Freelance Web Developer

  • #2
    If your field named "date" is an actual date-type field, it will sort perfectly. This is one really good reason to store your dates in date-type fields (as opposed to char-type fields).

    Comment


    • #3
      Originally posted by Fumigator View Post
      If your field named "date" is an actual date-type field, it will sort perfectly. This is one really good reason to store your dates in date-type fields (as opposed to char-type fields).
      hey, thanks for the reply!
      Ive changed the MySQL format to DATETIME, and I'm entering dates as follows:

      2006-10-02 06:06:12

      But I'm not too sure about how to convert this back to the format:

      F dS, Y h:i A

      And then how to convert that back to the MySQL format?
      Matt Davenport - Freelance Web Developer

      Comment


      • #4
        The way it works is it always stores the date in a certain format. You need not worry about this format-- it can be thought of as "internal stuff". Most relational databases always makes sure the value stored in a date, time, timestamp, or datetime field is a valid value-- that is, the value is a valid date or time. (I should note unfortunately MySQL allows some invalid dates to be stored, such as feburary 31st, but the general rule in relational databases is date and time fields always have valid date and time values in them.)

        Once you store a date in a field, you can then format that date any way you like using the date_format() function. The actual date stored doesn't change, but the value that is retrieved from the database is formatted the way you prefer. Even if you insert or update a date field and try to "force" a particular date format into the field, the actual stored date will look like all the other dates in all the other date-type fields. Some databases (such as DB2) will even pack a date value in such a way that it's not even readable as a date value just to save space.

        So use that date_format() function just like you did in the code you posted, and it will come out looking like you want it to look. Hope this clears things up for you.

        Comment


        • #5
          hey, thanks again for the reply

          My code looks like this:

          PHP Code:
          $postSql mysql_query("SELECT *, DATE_FORMAT(date, \"%F %d%S, %Y %h:%i %A\") FROM `post` WHERE `threadid` = '$id' ORDER BY `date` ASC");
          while (
          $post mysql_fetch_array($postSqlMYSQL_ASSOC))
          {
                  
                  
          $postid $post["id"];
                  
          $postcontent $post["content"];
                  
          $postdate $post["date"];
                  
          $author $post["authorid"];
          echo(
          $postdate);

          but the date still comes out in this format:
          2006-10-02 03:37:36
          Last edited by guelphdad; Oct 3, 2006, 01:26 PM.
          Matt Davenport - Freelance Web Developer

          Comment


          • #6
            now the problem is obvious, it is because you are selecting date and not your formatted date. in order to select that you need to give your formatted date column an alias and refer to that.

            PHP Code:
            $postSql mysql_query("SELECT *, 
            DATE_FORMAT(date, \"%F %d%S, %Y %h:%i %A\") as formatted FROM `post` 
            WHERE `threadid` = '
            $id' ORDER BY `date` ASC");
            while (
            $post mysql_fetch_array($postSqlMYSQL_ASSOC))
            {
                    
                    
            $postid $post["id"];
                    
            $postcontent $post["content"];
                    
            $postdate $post["formatted"];
                    
            $author $post["authorid"];
            echo(
            $postdate);

            also get out of the habit of using select * only select the columns you are displaying.

            Also to note that %F and %A specifiers do not exist in date formatting in mysql. Perhaps you are thinking of different ones in PHP. Check the date and time functions area of the manual to choose the appropriate specifiers to display your date like you want.
            Last edited by guelphdad; Oct 3, 2006, 01:31 PM.

            Comment

            Working...
            X