PHP & MySql Navigation Problem...With Dates

I'm trying to create an auto-nav for my news section and so far I've been quite successful. Well I say that but I've run into a snag.

My navigation auto creates a link to each news story which is fine, but what I want it to do is create a link to the month it was made.

This is the code I am using:

function pir_news_nav($nav_y) {
    $mysql = mysqli_connect("HOST", "DB", "PW", "TABLE");
    $newsQ = "SELECT * FROM tbl_news WHERE newsDateAdded LIKE '%". $nav_y ."%' AND newsActive=1 ORDER BY newsDateAdded ASC ";
    $newsRes = mysqli_query($mysql, $newsQ);
        while ($newsRow = mysqli_fetch_array($newsRes)) {
            $newsTitle = $newsRow["newsTitle"];
            $date = $newsRow["newsDateAdded"];
        $y = date("Y", strtotime($date));
        $m = date("m", strtotime($date));
        $month = date("F", strtotime($date));

        echo "<li><a href='?year=".$y."&amp;month=".$m."'>".$month."</a></li>"开发者_开发问答;
    }
}

which spits out an unordered list that look like so

<li>January</li> 
<li>February</li> 
<li>March</li> 
<li>March</li> 
<li>March</li> 
<li>April</li> 
.....etc

Whereas I want it to look like this:

<li>January</li>
<li>February</li>
<li>March</li>
<li>April</li>
.....etc

Use MySQL's "GROUP BY".

I usually have to look up exactly how to get the results I want, but off the top of my head:

$newsQ = "SELECT MONTH(newsDateAdded) as month FROM tbl_news WHERE newsDateAdded LIKE '%". $nav_y ."%' AND newsActive=1 ORDER BY newsDateAdded ASC GROUP BY month";

Of course, this assumes that you ONLY want the month out of this query. It looks like that's what you're doing, but it's an assumption regardless.

Here's the MySQL page on MONTH(). http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_month

(note the month is returned as 1-12, so you would have to handle that conversion to text.)

if i understand you well - you want to SELECT all the news, but show every month only one time?
I'd suggest u to use in boolean array to indicate if u already wrote every month....

You can also try this:

    $archiveList=array();
    foreach($result->result() as $row)
    {
        $archiveList[]=array('text'=>date('F Y', strtotime($row->date_added)), 'link'=>date('Y/m', strtotime($row->date_added)));
    }

    foreach ($archiveList as $k=>$na)
        $new[$k] = serialize($na);
    $uniq = array_unique($new);
    foreach($uniq as $k=>$ser)
        $clean[$k] = unserialize($ser);

    return $clean;

Builds an array with unique values from a list of dates from a database (DATETIME in this applications case)

edit Taken from a CI app, so you'll need to tweak it a bit. $row->date_added is your timestamp