A few years ago I started using a news CMS to manage all of the news we post on OCModShop.com, but there has been one weird problem that has always plagued me. If I put a news item in more than one child category, then it either shows up twice, or not at all.
There are several news items about products that don’t necessarily work in one category. For example, iPhone games can be considered Cell Phone News and Gaming news. NAS devices can be in the “storage” and “networking” categories. Some games are available on multiple platforms such as the PS3, Xbox 360 and Wii at the same time.
I had been toying with the SQL for a long time, trying to isolate the problem so that I can categorize news properly. I used GROUP BY and nested SELECT queries, and had been searching online to try and solve the problem which I thought would be rather complicated. Since I have a many-to-many relationship between the categories and the news items, I use an intermediary table to link the two together.
Then I remembered DISTINCT. I dropped all the weird nesting I was trying to get working and used this method, and now everything works properly. Since all of the individual RSS feeds are controlled by this system, a particular new item can now be in more than one feed if it belongs there.
There are many improvements to be done to OCModShop, but when I see the mountain of work it gets discouraging. Finally tackling this problem has given me enough gumption to roll up my sleeves and ‘git er done’.
