Announcement

Collapse
No announcement yet.

Group articles by day?

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

  • Group articles by day?

    Let's say I want to display the last 20 articles/threads from a database. I want grouped chronologically by day, i.e.

    Sunday, February 20, 2005
    Article 1
    Article 2
    ..

    Saturday, Februrary 19, 2005
    Article 3
    Article 4
    ..
    etc.

    Each article in the database contains a timestamp in unix format. Could anyone help me with the code for grouping the articles?

  • #2
    Use strtotime() to find the unix timestamp for 12:00 AM today and 11:59 PM today and then do a SQL query where timestamp > 12 AM Today and timestamp < 1159 PM today.



    EDIT: Actually, that would only get one day, I overlooked the fact you'd like to group them by day. I'm not sure, sorry.

    Comment


    • #3
      Thanks anyways

      Yeah, I need to find a routine that sorts an array of fetched articles and group them by day.

      Comment


      • #4
        Well you could grab all of them then do something like this:

        PHP Code:
        $articles = array();
        while (
        $article mysql_fetch_array($resourceMYSQL_ASSOC))
        {
            
        $articles[$article['timestamp']] = $article;
        }

        krsort($articles);
        reset($articles);

        foreach (
        $articles as $article)
        {
            
        // reverse chronological order, but technically they are grouped by day =P or you can group them by day from here on out

        Comment


        • #5
          Thank you. I know how you can sort the articles by time (you could actually use the timestamp in your mysql query to save the additional ksort step).

          What I am missing is how to group the articles by day (see my first post).

          Comment


          • #6
            Yes sorry very slow today. Of course you could sort by timestamp in the SQL query. What I am saying is something like this:

            PHP Code:
            $getarticles mysql_query("
                SELECT * 
                FROM articles 
                ORDER BY posttime DESC 
                LIMIT 20
            "
            ); //last 20 articles

            if (!mysql_num_rows($getarticles))
                exit;

            $currentdate '';
            $articles = array();
            while (
            $article mysql_fetch_array($getarticlesMYSQL_ASSOC))
            {
                if ((
            $temp date('n-j-Y'$article['posttime'])) != $currentdate)
                {
                    
            $currentdate $temp;
                }

                 
            $articles[$currentdate][] = $article;
            }

            mysql_free_result($getarticles); 
            Now all the articles are grouped by dates in the $articles array.

            Comment


            • #7
              xmitchx, I haven't tested the code yet, but you have my gratitude already now for helping me out! Thanks!

              Comment


              • #8
                Database solution

                If you're looking for a piece of SQL to do this, how about:

                select fields-you-want FROM table GROUP BY date_format(your-timestamp-field, "%Y%m%d") ORDER BY your-timestamp-field DESC;

                Comment


                • #9
                  No - I am totally misunderstanding the requirement!

                  Sorry - I'm in database mood. Forgive me

                  Comment


                  • #10
                    Originally posted by AlexanderT
                    xmitchx, I haven't tested the code yet, but you have my gratitude already now for helping me out! Thanks!
                    No problem Please let me know if it works.

                    Comment


                    • #11
                      It does work! Thank you again

                      Comment


                      • #12
                        works

                        Hey, I used that too, thanks for the tips.

                        Andy
                        www.teleco4.com

                        Comment

                        Related Topics

                        Collapse

                        Working...
                        X