Extract month and year from mysql date format

Question

I have a lot of dates stored in the database like so:

2017-06-01

And there are different months worth of data in the database. I want to show this in a dropdown menu so that a user would see:

April 2017 May 2017 June 2017

I tried this in a while loop as a start to try get the month alone:

$month = date('F', strtotime($row['b_date']));

but it just shows January 7 times.

$stmt = $link->prepare("SELECT 'b_date' FROM `summary`");
    $stmt->execute();
    $result = $stmt->get_result();
    $numRows = $result->num_rows;
    if($numRows > 0) {
        while($row = $result->fetch_assoc()) {
            $month = date('F', strtotime($row['b_date']));
            echo "<option value=''>{$month}</option>";
        }
    }

hard to say without seeing the code for this and the schema. At best, DISTINCT/GROUP BY? — Funk Forty Niner, Jun 01 '17 at 19:06
Yes they are. You have backticks around `summary` and quotes around `b_date` — AbraCadaver, Jun 01 '17 at 19:12
What are the contents of `$row`, what happens when you print it — Jeremy Hamm, Jun 01 '17 at 19:13
@AbraCadaver, apologies. You are correct. For some reason I have done that by accident! — Iggy's Pop, Jun 01 '17 at 19:14
But now I get the correct months, it is showing multiple of the same month, like April x 7 — Iggy's Pop, Jun 01 '17 at 19:15
I believe we should clear up some of the comments except the first one, which should answer this; somewhat and keep Jeremy's also. — Funk Forty Niner, Jun 01 '17 at 19:15
I have tried using DISTINCT in my sql query and GROUP BY but it still shows the same month multiple times — Iggy's Pop, Jun 01 '17 at 19:16
can you edit your question with the values and the schema? it's hard to say here. You could try a `WHERE` clause also. — Funk Forty Niner, Jun 01 '17 at 19:17
@Fred-ii-, the values are just dates like 2017-04-01, 2017-04-02, 2017-04-03 etc. and that database column is set to DATE — Iggy's Pop, Jun 01 '17 at 19:19
the manual on php's date http://php.net/manual/en/function.date.php shows `F` as being *"A full textual representation of a month, such as January or March"* - yet your `2017-06-01` may be at fault; this TBH is a bit tricky for me and can't put my finger on it. — Funk Forty Niner, Jun 01 '17 at 19:24

Jeremy Hamm · Accepted Answer · 2017-06-01T19:26:57.280

0

You can try something like this in your query

SELECT b_date 
FROM summary
GROUP BY YEAR(b_date), MONTH(b_date)

Similar to this issue SQL group dates by month. My guess would be you are using MySQL, here are the docs for date manipulation

edited Jun 01 '17 at 19:26

answered Jun 01 '17 at 19:25

Jeremy Hamm

499
1
5
14

1

Better group by year first. – Don't Panic Jun 01 '17 at 19:25
@Don't Panic Good call – Jeremy Hamm Jun 01 '17 at 19:27
Glad to hear it – Jeremy Hamm Jun 01 '17 at 19:30

Extract month and year from mysql date format

1 Answers1