Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 355
  • Last Modified:

select distinct month

Hello, simple little mistake.. i am trying to get and display all distinct months.. it is displaying them all as october 2011

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

 echo  date("F Y", strtotime($row_Recordset_getallevents['BlogMonth']));
0
jblayney
Asked:
jblayney
  • 12
  • 6
  • 4
  • +2
1 Solution
 
amiguraCommented:

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']));

Open in new window

0
 
jblayneyAuthor Commented:
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...
0
 
jblayneyAuthor Commented:
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
0
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

 
Lukasz ChmielewskiCommented:
Show us your output.
0
 
jblayneyAuthor Commented:
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
0
 
Lukasz ChmielewskiCommented:
what happens when you get rid of the LIMIT 12 ?
0
 
jblayneyAuthor Commented:
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
0
 
jblayneyAuthor Commented:
... as soon as i try to make the display as august / september instead OF 8 / 9 is where i get the problem
0
 
Lukasz ChmielewskiCommented:
What about your database ? How do you store them inside ?
0
 
jblayneyAuthor Commented:
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...
0
 
Lukasz ChmielewskiCommented:
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.
0
 
jblayneyAuthor Commented:
Hello,

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

0
 
Ray PaseurCommented:
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.
0
 
Lukasz ChmielewskiCommented:
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));
0
 
ropennerCommented:
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
0
 
jblayneyAuthor Commented:
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

Open in new window

0
 
ropennerCommented:
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>";
0
 
jblayneyAuthor Commented:
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
0
 
amiguraCommented:
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

Open in new window

0
 
Lukasz ChmielewskiCommented:
How about this code:

<?php

   // your connection

    $q = "SELECT DISTINCT MONTH(blog_date) AS BlogMonth, YEAR(blog_date) AS BlogYear  FROM blog  ORDER BY blog_date DESC LIMIT 12";
    $r = mysql_query($q);
    
    while($row_Recordset_getallevents = mysql_fetch_array($r))
    echo  date("F Y", mktime(0,0,0,$row_Recordset_getallevents['BlogMonth'],1,$row_Recordset_getallevents['BlogYear']));
?>

Open in new window

0
 
jblayneyAuthor Commented:
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)); ?>

Open in new window

0
 
Ray PaseurCommented:
So there are only three lines?
0
 
jblayneyAuthor Commented:
thats it Roads.. mktime was what we were looking for...

thank you everyone for your help
0
 
Ray PaseurCommented:
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;
}

Open in new window

0
 
Ray PaseurCommented:
@jblayney: You might enjoy learning about how PHP and MySQL DATETIME strings work together.  This article can help.
http://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
0
 
jblayneyAuthor Commented:
Nice article Ray, thank you.
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 12
  • 6
  • 4
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now