Retrieve and Group Data - PHP syntax, functions?

PHP
1

Hi - I am struggling to find a way to build an archive list of “posts” in a CMS.

(Backstory, briefly: I’m using WordPress and the new “custom post types” but these do not work with WP’s built-in archive-building template tags).

I need to go into my DB, grab all the “years” from each post’s published date, and from that build a simple list of years in which posts where published. For example, if my DB has posts published in 2008, 2009, and 2010, all I need is those three years (not how many posts or anything else). Then I can build a list and make each year link to an archive page specific to that year.

Logically, in terms of steps, I know what I want to do:

[INDENT]1. Query the MySQL DB for all posts and retrieve content from one field - e.g. post_date - this field is stored by default in the format of ‘YYYY-MM-DD 00:00;00’

  1. I only need the year (‘YYYY’) so I need to pull that out - the rest of the post_date data is not needed…

  2. Sort and group those year values such that I get a list of years in which posts where published.[/INDENT]

I know the MySQL query.

The problem is actual language and syntax - I don’t know what PHP functions to use. Specifically:

[INDENT]1. I can assume there will be a loop of some sort, with an increment counter to go through all the returned values and search for the years (‘YYYY’) - a ‘for’ loop?

  1. How would I identity and strip out the YYYY from each string?

  2. How would I sort and group those YYYY’s into a list? I’m guessing the end result here should be an array. And again, I don’t need to know that I have, for example, 28 posts in 2009. That is, I don’t need “2009” listed 28 times. I only need to know that 2009 is a valid year value.

  3. Once I have that list of years, how would I separate them out to be individual strings to then work with - e.g. if I have [2008, 2009, 2010], I need to be able to grab each one individually and do stuff with it (wrap it in some html). Again, I assume this would be a ‘for’ or ‘while’ loop?[/INDENT]

I’m sure this is pretty basic stuff. I’m just stuck on the specific syntax / functions to use. Any suggestions, tips would be great!

2

why not the counts? getting them is virtually free since you’re querying the database anyway…


SELECT YEAR(post_date) AS theyear 
     , COUNT(*) AS posts
  FROM daTable
GROUP
    BY theyear

run that in mysql and look at what it produces

:slight_smile:

3

Try and do as much as you can in your sql query.


"select distinct YEAR(post_date) as theyear from your_table_name"

[google]mysql year function[/google]

ps I have never used this function myself, but I think that is how it should work.

You then just loop through the array of rows that mysql returns and echo them onto the page.


foreach( $rows as $row){

echo $row['theyear']  ; 

}

*YEAR will likely be a protected word in mysql hence I returned theyear

I do not know how you are connecting to the database, but in the example am assuming you are returning a associated arrays if not use $row[0]

I am not a WP user, but the above should be the simplest way to retrieve a list of distinct years.