Maybe it's because I'm not fully awake yet that the problem seems less complex than it is. What is wrong with using a seperate table for each year? Doug On Thu, 26 Jun 2003 08:43:17 +0200, anders thoresson wrote: >Hi, > 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 > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
