Re: [PHP-DB] generating sequence without AUTO_INCREMENT
Doing what I wanted to do took six hours and 120 lines of code, all in all, but I learnt a good deal on the way. ;-) At times, manual intervention can be a lifesaver. You have already said that you have to 'ring fence' bank holidays, and weekends, so someone has to check or enter the bank holidays. So I would provide two 'fill' functions that populates an issue number table, and will either fill the week if no bank holidays, or fill each selected day when there is a bank holiday in the week. This can then be done monthly or yearly as the look ahead time requires. Or am I missing something - I've only ever used Interbase/Firebird, and never had a problem with this sort of ident. -- Lester Caine - L.S.Caine Electronic Services -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] 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? -- anders thoresson -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] generating sequence without AUTO_INCREMENT
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
RE: [PHP-DB] generating sequence without AUTO_INCREMENT
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:[EMAIL PROTECTED] Sent: Wednesday, June 25, 2003 11:43 PM To: [EMAIL PROTECTED] Subject: [PHP-DB] 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
Re: [PHP-DB] generating sequence without AUTO_INCREMENT
MAX(issue_number)+1 where YEAR(date) = Year(NOW()) I continued to work while waiting for ideas, and have now manage to get a script that work. I'm getting the next issue number this way: // Connect to database to get latest issue number from table un_issue db_connect($dbuser, $dbpassword, $dbdatabase); $query = SELECT * FROM un_issue ORDER BY i_id DESC LIMIT 1; $result = mysql_query($query); $row = mysql_fetch_row($result); $next_issue_number = $row[2] + 1; $previous_issue_date = $row[1]; I didn't think of the problem when two users are adding at the same time since this won't happen in this particular case. But what could I do to avoid the problem in future scripts? Doing what I wanted to do took six hours and 120 lines of code, all in all, but I learnt a good deal on the way. ;-) //Anders -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
