[PHP-DB] Date math functions...
I have a question about using DATE_ADD in MySQL. I am not sure if this is the most effective way of dealing with this problem so I am asking here. The situation is that I am working on modifying a calendar system for our use. Our On-Call rotation is such that when someone is On-Call over the weekend they automatically get Monday off. I have created a page to simplify inputting the On-Call schedule into the calendar system, and now I want the Monday off to be automagically scheduled when a weekend On-Call is entered. The way that I see for doing this is adding 1 day to the end date of the weekend On-Call and using that as the input for the day off scheduling. Here is a bit of what I have at this point... if ($DailyStopYear) { $result = mysql($DBName,SELECT (TO_DAYS('$StopDate') - TO_DAYS('$StartDate'))) or die(mysql_error()); while ($row = mysql_fetch_row($result)) { $dateDiff = $row[0]; } } else { $dateDiff = 0; } if ($dateDiff == 3) { mysql($DBName,UPDATE Balances SET CompEarned=CompTaken+8 WHERE said='$said') or die(mysql_error()); mysql($DBName,INSERT INTO Log VALUES('DATE_ADD($StopDate, INTERVAL 1 DAY','',1,2,'$CalendarDetailsID')) or die(mysql_error()); My big question is about using the DATE_ADD MySQL function inside the INSERT statement. Is this allowed? If it is allowed, is it a bad idea for some reason? Is there a better way of doing this? Thanks in advance. Scott Nipp Phone: (214) 858-1289 E-mail: [EMAIL PROTECTED] Web: http:\\ldsa.sbcld.sbc.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Date math functions...
[snip] if ($dateDiff == 3) { mysql($DBName,UPDATE Balances SET CompEarned=CompTaken+8 WHERE said='$said') or die(mysql_error()); mysql($DBName,INSERT INTO Log VALUES('DATE_ADD($StopDate, INTERVAL 1 DAY','',1,2,'$CalendarDetailsID')) or die(mysql_error()); My big question is about using the DATE_ADD MySQL function inside the INSERT statement. Is this allowed? If it is allowed, is it a bad idea for some reason? Is there a better way of doing this? Thanks in advance. Sure, that's allowed. You have $StopDate in PHP, so you could do it with some math in PHP, also, but then you'd have to worry about the end of months, years, etc, whereas DATE_ADD will do this for you. ---John Holmes... -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Date math functions...
[snip] if ($dateDiff == 3) { mysql($DBName,UPDATE Balances SET CompEarned=CompTaken+8 WHERE said='$said') or die(mysql_error()); mysql($DBName,INSERT INTO Log VALUES('DATE_ADD($StopDate, INTERVAL 1 DAY','',1,2,'$CalendarDetailsID')) or die(mysql_error()); My big question is about using the DATE_ADD MySQL function inside the INSERT statement. Is this allowed? If it is allowed, is it a bad idea for some reason? Is there a better way of doing this? Thanks in advance. Sure, that's allowed. You have $StopDate in PHP, so you could do it with some math in PHP, also, but then you'd have to worry about the end of months, years, etc, whereas DATE_ADD will do this for you. ---John Holmes... Wait... just noticed your syntax error. Don't enclose the function in single quotes, otherwise you're trying to insert a string. Should be: ... VALUES (DATE_ADD($StopDate,INTERVAL 1 DAY), ... ---John Holmes... -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Date math functions...
Thanks. I was noticing that it was not working. Let me give this a try and see how things go. -Original Message- From: 1LT John W. Holmes [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 15, 2003 10:32 AM To: 1LT John W. Holmes; NIPP, SCOTT V (SBCSI); [EMAIL PROTECTED] Subject: Re: [PHP-DB] Date math functions... [snip] if ($dateDiff == 3) { mysql($DBName,UPDATE Balances SET CompEarned=CompTaken+8 WHERE said='$said') or die(mysql_error()); mysql($DBName,INSERT INTO Log VALUES('DATE_ADD($StopDate, INTERVAL 1 DAY','',1,2,'$CalendarDetailsID')) or die(mysql_error()); My big question is about using the DATE_ADD MySQL function inside the INSERT statement. Is this allowed? If it is allowed, is it a bad idea for some reason? Is there a better way of doing this? Thanks in advance. Sure, that's allowed. You have $StopDate in PHP, so you could do it with some math in PHP, also, but then you'd have to worry about the end of months, years, etc, whereas DATE_ADD will do this for you. ---John Holmes... Wait... just noticed your syntax error. Don't enclose the function in single quotes, otherwise you're trying to insert a string. Should be: ... VALUES (DATE_ADD($StopDate,INTERVAL 1 DAY), ... ---John Holmes... -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Date math functions...
Actually this is generating another error. Now, without the single quotes I am getting the following error: Column 'StartDate' cannot be null It looks like for some reason the DATE_ADD is returning a NULL value. Any more ideas? -Original Message- From: 1LT John W. Holmes [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 15, 2003 10:32 AM To: 1LT John W. Holmes; NIPP, SCOTT V (SBCSI); [EMAIL PROTECTED] Subject: Re: [PHP-DB] Date math functions... [snip] if ($dateDiff == 3) { mysql($DBName,UPDATE Balances SET CompEarned=CompTaken+8 WHERE said='$said') or die(mysql_error()); mysql($DBName,INSERT INTO Log VALUES('DATE_ADD($StopDate, INTERVAL 1 DAY','',1,2,'$CalendarDetailsID')) or die(mysql_error()); My big question is about using the DATE_ADD MySQL function inside the INSERT statement. Is this allowed? If it is allowed, is it a bad idea for some reason? Is there a better way of doing this? Thanks in advance. Sure, that's allowed. You have $StopDate in PHP, so you could do it with some math in PHP, also, but then you'd have to worry about the end of months, years, etc, whereas DATE_ADD will do this for you. ---John Holmes... Wait... just noticed your syntax error. Don't enclose the function in single quotes, otherwise you're trying to insert a string. Should be: ... VALUES (DATE_ADD($StopDate,INTERVAL 1 DAY), ... ---John Holmes... -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Date math functions...
On Thursday 16 January 2003 00:55, NIPP, SCOTT V (SBCSI) wrote: Actually this is generating another error. Now, without the single quotes I am getting the following error: Column 'StartDate' cannot be null It looks like for some reason the DATE_ADD is returning a NULL value. Any more ideas? Which probably means your $StopDate is 0. What does it actually contain? You really should assign your query to some variable ($query) THEN plug it into your query function. If and you have any problems you can echo $query to see what you're passing to mysql. -- Jason Wong - Gremlins Associates - www.gremlins.biz Open Source Software Systems Integrators * Web Design Hosting * Internet Intranet Applications Development * /* I'm having BEAUTIFUL THOUGHTS about the INSIPID WIVES of smug and wealthy CORPORATE LAWYERS ... */ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Date math functions...
mysql($DBName,UPDATE Balances SET CompEarned=CompTaken+8 WHERE said='$said') or die(mysql_error()); mysql($DBName,INSERT INTO Log VALUES(DATE_ADD($StopDate, INTERVAL 1 DAY,'',1,2,'$CalendarDetailsID')) or die(mysql_error()); Actually this is generating another error. Now, without the single quotes I am getting the following error: Column 'StartDate' cannot be null It looks like for some reason the DATE_ADD is returning a NULL value. Any more ideas? Are you sure $StopDate has a value and is in the right format? Echo out your query to the screen when you get an error, so you can look for obvious mistakes. Is this just example code? You're using a function called mysql(), which isn't standard. ---John Holmes... -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php