Add date to 1st record per month

Question

I have a guestbook script and wish to add month and year to the first post of each month like Instagram profiles demo

so my question is how can I grab month and year from db timestamp and add to first record per month?

db structure:

post
--id
--msg
--dt (timestamp)

I know (and am sorry) this is a bit of a "can you do the work for me" post but I haven't got a clue where I would start.

tried so far:

SELECT DISTINCT MONTH(dt), YEAR(dt) AS month FROM post GROUP BY MONTH(dt)

...

SELECT DISTINCT dt FROM post ORDER BY dt DESC

What did you try so far? Do you talk about the db's or php's timestamp? — l-x, Dec 02 '13 at 09:57
Possible duplicate http://stackoverflow.com/questions/7670910/how-to-get-month-using-date-in-mysql — Akhil, Dec 02 '13 at 09:58
db timestamp, op ammended, i've tried a couple of things but like I said I haven't been successful — Anna Riekic, Dec 02 '13 at 10:01

score 0 · Answer 1 · answered Dec 02 '13 at 10:01

Select the needed images (all or maybe only a part of them, if there are a lot) ordered by the dt field in decreasing order.
Iterate through the selection result and print out the pictures.
When the n+1. picture's date has a different month, than the previous one (the n. picture), then show the date.

score 0 · Answer 2 · answered Dec 02 '13 at 10:12

Maybe this code-snippet might help you.

It prints out the Month and Year only on a new month.

To format a timestamp you can use the date("format", timestamp) function.

Take a look here for more information.

 <?php
        $currentMonth = "";
        //Your timestamps, here saved in an array and ordered!!
        $array_with_timestamps...
        //Execute for each value in the array
        foreach($array_with_timestamps as $timstamp) {
            //If the month of this timestamp is unequal to the value in $currentMonth
            //then echo "Monthname Year" of this timestamp
            if ($currentMonth != date('F', $timestamp)) { 
                echo date('F', $timestamp)." ".date('Y', $timestamp);
                //Set this month now as the currentMonth
                $currentMonth = date('F', $timestamp);
            }
            //Print picture
        }
  ?>

Add date to 1st record per month

2 Answers2