select distinct month

SELECT blog_date FROM blog  group by blog_date  ORDER BY blog_date DESC LIMIT 12

 echo  date("F Y", strtotime($row_Recordset_getallevents['blog_date']));

Select allOpen in new window

Hello Amigura, thanks for the help, that isnt the solution though, I want distinct months.. with your script, If I have 10 entries in september, it will display september 10 times...

my sql is perfect (i think) it is the display which is messing it up... if I display as

 $row_Recordset_getallevents['BlogMonth'];

if displays the proper unique months, it is once i am wrapping it in  date("F Y", strtotime($row_Recordset_getallevents['blog_date'])); which is causing the probelm

Show us your output.

hello, Roads roads...  if I display  as

echo $row_Recordset_getallevents['BlogMonth'];

i get  (which is correct)
9
8



if I display as

echo date("F", strtotime($row_Recordset_getallevents['BlogMonth']));

I get
october
october

what happens when you get rid of the LIMIT 12 ?

Hello,

no difference...

the problem is in my conversion to a pretty date....

if I dump my  DISTINCT MONTH (blog_date)  AS BlogMonth,....   i have august(8) and september(9) entries...

as soon as i try to make the display as august / september instead don 8 / 9 is where i get the problem

... as soon as i try to make the display as august / september instead OF 8 / 9 is where i get the problem

What about your database ? How do you store them inside ?

they are a date field...

Roads, I do not think you are understanding my problem...

I am able to successfully extract distinct months from my database as numbers....

i do not want to display them as numbers, I want them to display as pretty text.. eg, january, february, etc...

Yes, I do understand, what you need is to convert the fields into names. You should provide some more data, a piece of the table for example. It is hard to diagnose the problem without knowing the db structure for example.

Hello,

the field is a date field... "date" and the data is stored as "2011-01-05"

Please make a query that says this: SELECT blog_date FROM blog ORDER BY blog_date DESC LIMIT 40

Run that query, then use var_dump() to print every row of the output.  Copy the output and post it into a code snippet here.

I don't get the "only month" extraction from the db but try this:

echo  date("F Y", mktime(0,0,0,$row_Recordset_getallevents['BlogMonth'],1,2011));

SELECT MONTH(blog_date) as g, blog_date FROM blog GROUP BY g ORDER BY blog_date DESC LIMIT 12
while ($v = $st->fetch_assoc()) {
 echo  date("F Y", strtotime($v{'created'})),"<BR>";
}

outputs something similar to:

September 2007
August 2007
July 2007
June 2007
May 2007
April 2007
March 2007
February 2007
January 2007
December 2006
November 2006
October 2006

Hello Ray,

i didn't use var_dump, but i did echo my loop and this is what I got..

2011-09-30
2011-09-22
2010-12-23

Select allOpen in new window

correction since the rest of the code is not relevant


SELECT MONTH(blog_date) as g, blog_date FROM blog GROUP BY g ORDER BY blog_date DESC LIMIT 12
echo  date("F Y", strtotime($results_associative_array{'blog_date'})),"<BR>";

Hello Roads,

It is a blog.. i have a list of archives which are sorted by month, very similar to how wordpress does it...

so I want to go into the database and get evert distinct month and then display them under my archive list of months... this could span over multiple years, so your code above will not work....  what i ahve so far and it works is this....

SELECT DISTINCT MONTH(blog_date) AS BlogMonth, YEAR(blog_date) AS BlogYear  FROM blog  ORDER BY blog_date DESC LIMIT 12

this is getting my correct months and years as numbers.. but i want to display the months as words, not intengers

i presume because of your code the the filed was just month

if you want a specific year you might want to add a where query instead of limit 12

will work on yyyy-mm-dd

SELECT blog_date FROM blog  group by DATE_FORMAT(`blog_date`,'%Y%m')  ORDER BY blog_date DESC LIMIT 12

Select allOpen in new window

ropener, i got a fatal error..

veryone here is my final code and the output.... tis works, it is just ugly...


// output

9 2011
12 2010

$query_Recordset_getallevents = "SELECT DISTINCT MONTH(blog_date) AS BlogMonth, YEAR(blog_date) AS BlogYear  FROM blog  ORDER BY blog_date DESC LIMIT 12";
$Recordset_getallevents = mysql_query($query_Recordset_getallevents, $newsstuff) or die(mysql_error());
$row_Recordset_getallevents = mysql_fetch_assoc($Recordset_getallevents);
$totalRows_Recordset_getallevents = mysql_num_rows($Recordset_getallevents);

 <?php 
 do { ?>
   <a href="blog_feed.php?month=<?php echo $row_Recordset_getallevents['BlogMonth']; ?>&year=<?php echo $row_Recordset_getallevents['BlogYear']; ?>"><?php 
   
  echo $row_Recordset_getallevents['BlogMonth']." ". $row_Recordset_getallevents['BlogYear']. "";
	 ?></a><br />
   <?php } while ($row_Recordset_getallevents = mysql_fetch_assoc($Recordset_getallevents)); ?>

Select allOpen in new window

So there are only three lines?

thats it Roads.. mktime was what we were looking for...

thank you everyone for your help

See http://www.laprbass.com/RAY_temp_jblayney.php

I think my strategy would be to select the entire date into one variable and use that with strtotime() and date() to reformat the date into a named month.

And I am not sure exactly what you want to do for output, but you might select MONTH() of the date column into another variable and use that in GROUP BY

<?php // RAY_temp_jblayney.php
error_reporting(E_ALL);

date_default_timezone_set('America/New_York');

$arr = array
( '2011-09-30'
, '2011-09-22'
, '2010-12-23'
)
;

foreach ($arr as $date)
{
    echo "<br/>";
    echo $date;
    echo " ";
    echo date('F', strtotime($date));
    echo PHP_EOL;
}

Select allOpen in new window

@jblayney: You might enjoy learning about how PHP and MySQL DATETIME strings work together.  This article can help.
https://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_201-Handling-date-and-time-in-PHP-and-MySQL.html

Good luck with your project, ~Ray

Nice article Ray, thank you.