3 replies to this topic

[Tirus]

Hello all,

I am building a news cms and archive system using mysql and php. What I am trying to do at this point is create a section on my site where there are hyperlinks for every month containing news from that month (this being months that have passed).

A quick example:

August 2006 (current month)
July 2006
June 2006
May 2006


and when each month is clicked, they bring up the news for that month.
Now, I know how to select my rows and columns from my mysql table, I am currently using the query:

SELECT date_format(date,'%M %Y') AS date,news_id FROM `news`ORDER BY date

When I add news, the date is inserted as YYYY-MM-DD and with the select statement, I get it to show as Month Year i.e. August 2006. The problem is, I only want each month to be displayed once BUT, since each date for each piece of news is technically unique since the day is different, my display becomes (if I have 3 pieces of news for August and 2 for July):

August 2006
August 2006
August 2006
July 2006
July 2006

I have tried adding DISTINCT in between SELECT and date_format but I get the same result. I also tried UNIQUE but I get a mysql error.
Am I still getting duplicate Augusts and Julys even with DISTINCT because MySQL is reading the date as 2006-08-18,2006-08-19,2006-08-20 and not August etc etc?

Does anyone know how to simply have each month show up once regardless of how may pieces of news I have for each month.

I thought about trying to alter my INSERT statement on my Add News page as to insert the date into the MySQL table directly as August Bla Bla, 2006 but I get an error....I tried:

INSERT INTO `news` (title, news, date_format (date,'%M %Y') AS date) VALUES ('$t', '$n', NOW())

Variations of that also did not work and I got a MySQL error...

I figured a work around would be to just make an extra field in my Add News page called Month and basically if I am adding news for August, type August in the field and then have the MySQL query select Distinct values from that column. I would honestly rather just have it do it automatically from the date ccolumn so any and all help would be greatly appreciated.

Cheers,
Tirus

Edited by Tirus, 22 August 2006 - 08:26 PM.

[ShadowDeath01]

change the date format in the table in phpmyadmin?

[Tirus]

ShadowDeath01, on Aug 22 2006, 10:56 PM, said:

change the date format in the table in phpmyadmin?

how would i go about doing this?
i tried changing the default from 0000-00-00 to %M %Y etc to get the result I wanted but it did not work.

[Wolfe]

I may be misreading or misinterpreting the situation, but what I would suggest is to use a custom date format that you could easily use REGEX to strip out the day when sorting through months.

So, use a date format like.. [Month] [Day] [Year]

For example:

August 17 2006
August 18 2006
July 24 2006

Then, when getting all the months, use a foreach loop and explode all the dates into 3 groups.

$array[0] = month
$array[1] = day
$array[2] = year

So, you can then use $array[0] to get all of your months.

Of course, you will get multiple entries like that, still. But, just use a simple if conditional to check the coming data with the list you've already accumulated. If they match, don't add it.

Edited by Wolfe, 23 August 2006 - 07:30 PM.