You could you do MAX(issue_number)+1 where YEAR(date) = Year(NOW()) to get next issue number for the current year, but if you had 2 people entering issues at the same time, it wouldn't work so well. Just trying to give you a starting point because it seems like you haven't really gotten an answer yet. > -----Original Message----- > From: anders thoresson [mailto:listor@thoresson.net] > Sent: Wednesday, June 25, 2003 11:43 PM > To: php-db@lists.php.net > Subject: generating sequence without AUTO_INCREMENT > > > 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? -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
