I'm working on a web based article planning system for a news paper, based on PHP and MySQL. One vital table in the setup will contain release date and number for each issue. It's a simple table with just three columns:
id, which is the primary key and AUTO_INCREMENT
date, which is the release date for each issue
issue_number
I need help with a solution to automatically create the issue_number. The conditions is as follow:
1. The paper is published every Monday to Friday, except for national holidays, so issue_number isn't the same as the day number
2. The first issue each year should be number 1, and then the rest should follow in sequence
The first condition is taken care of manually, where one of the editors will input a start date and an end date between which the newspaper will be released Mondays to Fridays. That way I'll handle the national holidays, for each year the editors have to enter a couple of date sequences to "fence out" the holidays.
But keeping track of each and every issue_number manually isn't practical. I've looked at the MySQL manual, but all I find is LAST_INSERT_ID(). But from what I've understood, that function is only useful when it's the same value that is being updated, and this isn't the case in my problem.
So I guess I'll have to write my own function. The solution I think of right now is adding new issues in two steps: First adding the date for the new issue to the table, then checking the issue_number for the date before, and then adding the issue_number next in turn to the new issue, or, if the year differs start over from 1 again.
Is there a smarter way of doing this?
-- anders thoresson
-- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
