Stored Proc - Date not inserting into the Record

I nave 2 stored procedures:

DROP PROCEDURE IF EXISTS `Insert_OHC_Sun`;
DELIMITER $$
CREATE DEFINER=`donw`@`` PROCEDURE `Insert_OHC_Sun`(theDate
DATE,theDateRaw INT)
BEGIN
INSERT INTO Office_Hours_Cuttoff
(ohc_Date,ohc_Date_Raw,Office_Status)
VALUES (theDate,theDateRaw,"Closed");
END
$$

DROP PROCEDURE IF EXISTS `Insert_OHC_Day`;
DELIMITER $$
CREATE DEFINER=`donw`@`` PROCEDURE `Insert_OHC_Day`(theDate
DATE,theDateRaw INT)
BEGIN
INSERT INTO Office_Hours_Cuttoff (ohc_Date,ohc_Date_Raw)
VALUES (theDate,theDateRaw);
END
$$

Then I have PHP Code to insert a YEAR of days in a table:

if($_POST['new_year']) {
//New Year
if(in_array($_POST['pick_year'], $ExistingYears)) {
$Message = "
The year ".$_POST['pick_year']." is already
existing.
Please use the DELETE YEAR feature first. Then ADD the
year again.
";
} else {

//Add Year
$first_day = mktime(0,0,0,1, 1, $_POST['pick_year']);
$last_day = mktime(0,0,0,12, 31, $_POST['pick_year']);

$cDate = $first_day;
$num = 1;


while($cDate <= $last_day) {

$nDate = Date('Y-m-d', $cDate);

$db->next_result();
if(date('D', $cDate) == "Sun") {
$db->query("CALL Insert_OHC_Sun({$nDate},{$cDate})");
}else{
$db->query("CALL Insert_OHC_Day({$nDate},{$cDate})");
}

$cDate+=86400;
$num++;

}
}
}



The records are inserting into the table BUT the field "och_Dates" is
not getting the proper value. It gets "0000-00-00".

Frustrating. My code looks right and I echoed the value on the page
and it is formatted properly. The field in mySQL is formatted as a
DATE type.

Little help please :-)


Don Wieland
D W D a t a C o n c e p t s
~~~~~~~~~~~~~~~~~~~~~~~~~
donw@dwdataconcepts.com
Direct Line - (949) 305-2771

Integrated data solutions to fit your business needs.

Need assistance in dialing in your FileMaker solution? Check out our
Developer Support Plan at:
http://www.dwdataconcepts.com/DevSup.html

Appointment 1.0v9 - Powerful Appointment Scheduling for FileMaker Pro
9 or higher
http://www.appointment10.com

For a quick overview -
http://www.appointment10.com/Appt10_Promo/Overview.html


--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Stored Proc - Date not inserting into the Record

Change the input argument type as a varchar instead of date

Ex:
CREATE DEFINER=`donw`@`` PROCEDURE `Insert_OHC_Sun`(theDate
VARCHAR(50),theDateRaw INT)

surly it will work

- Peter


Don Wieland wrote:
> I nave 2 stored procedures:
>
> DROP PROCEDURE IF EXISTS `Insert_OHC_Sun`;
> DELIMITER $$
> CREATE DEFINER=`donw`@`` PROCEDURE `Insert_OHC_Sun`(theDate
> DATE,theDateRaw INT)
> BEGIN
> INSERT INTO Office_Hours_Cuttoff
> (ohc_Date,ohc_Date_Raw,Office_Status)
> VALUES (theDate,theDateRaw,"Closed");
> END
> $$
>
> DROP PROCEDURE IF EXISTS `Insert_OHC_Day`;
> DELIMITER $$
> CREATE DEFINER=`donw`@`` PROCEDURE `Insert_OHC_Day`(theDate
> DATE,theDateRaw INT)
> BEGIN
> INSERT INTO Office_Hours_Cuttoff (ohc_Date,ohc_Date_Raw)
> VALUES (theDate,theDateRaw);
> END
> $$
>
> Then I have PHP Code to insert a YEAR of days in a table:
>
> if($_POST['new_year']) {
> //New Year
> if(in_array($_POST['pick_year'], $ExistingYears)) {
> $Message = "
The year ".$_POST['pick_year']." is already
> existing.
Please use the DELETE YEAR feature first. Then ADD the
> year again.
";
> } else {
>
> //Add Year
> $first_day = mktime(0,0,0,1, 1, $_POST['pick_year']);
> $last_day = mktime(0,0,0,12, 31, $_POST['pick_year']);
>
> $cDate = $first_day;
> $num = 1;
>
>
> while($cDate <= $last_day) {
>
> $nDate = Date('Y-m-d', $cDate);
>
> $db->next_result();
> if(date('D', $cDate) == "Sun") {
> $db->query("CALL Insert_OHC_Sun({$nDate},{$cDate})");
> }else{
> $db->query("CALL Insert_OHC_Day({$nDate},{$cDate})");
> }
>
> $cDate+=86400;
> $num++;
>
> }
> }
> }
>
>
>
> The records are inserting into the table BUT the field "och_Dates" is
> not getting the proper value. It gets "0000-00-00".
>
> Frustrating. My code looks right and I echoed the value on the page
> and it is formatted properly. The field in mySQL is formatted as a
> DATE type.
>
> Little help please :-)
>
>
> Don Wieland
> D W D a t a C o n c e p t s
> ~~~~~~~~~~~~~~~~~~~~~~~~~
> donw@dwdataconcepts.com
> Direct Line - (949) 305-2771
>
> Integrated data solutions to fit your business needs.
>
> Need assistance in dialing in your FileMaker solution? Check out our
> Developer Support Plan at:
> http://www.dwdataconcepts.com/DevSup.html
>
> Appointment 1.0v9 - Powerful Appointment Scheduling for FileMaker Pro
> 9 or higher
> http://www.appointment10.com
>
> For a quick overview -
> http://www.appointment10.com/Appt10_Promo/Overview.html
>
>

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Stored Proc - Date not inserting into the Record

--------------080907060103050103040106
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

FYI

Please Pass your input within quotes
$db->query("CALL Insert_OHC_Sun(*'*{$nDate}*'*,{$cDate})");

surly it will work

- Peter


Don Wieland wrote:
> I nave 2 stored procedures:
>
> DROP PROCEDURE IF EXISTS `Insert_OHC_Sun`;
> DELIMITER $$
> CREATE DEFINER=`donw`@`` PROCEDURE `Insert_OHC_Sun`(theDate
> DATE,theDateRaw INT)
> BEGIN
> INSERT INTO Office_Hours_Cuttoff
> (ohc_Date,ohc_Date_Raw,Office_Status)
> VALUES (theDate,theDateRaw,"Closed");
> END
> $$
>
> DROP PROCEDURE IF EXISTS `Insert_OHC_Day`;
> DELIMITER $$
> CREATE DEFINER=`donw`@`` PROCEDURE `Insert_OHC_Day`(theDate
> DATE,theDateRaw INT)
> BEGIN
> INSERT INTO Office_Hours_Cuttoff (ohc_Date,ohc_Date_Raw)
> VALUES (theDate,theDateRaw);
> END
> $$
>
> Then I have PHP Code to insert a YEAR of days in a table:
>
> if($_POST['new_year']) {
> //New Year
> if(in_array($_POST['pick_year'], $ExistingYears)) {
> $Message = "
The year ".$_POST['pick_year']." is already
> existing.
Please use the DELETE YEAR feature first. Then ADD the
> year again.
";
> } else {
>
> //Add Year
> $first_day = mktime(0,0,0,1, 1, $_POST['pick_year']);
> $last_day = mktime(0,0,0,12, 31, $_POST['pick_year']);
>
> $cDate = $first_day;
> $num = 1;
>
>
> while($cDate <= $last_day) {
>
> $nDate = Date('Y-m-d', $cDate);
>
> $db->next_result();
> if(date('D', $cDate) == "Sun") {
> $db->query("CALL Insert_OHC_Sun({$nDate},{$cDate})");
> }else{
> $db->query("CALL Insert_OHC_Day({$nDate},{$cDate})");
> }
>
> $cDate+=86400;
> $num++;
>
> }
> }
> }
>
>
>
> The records are inserting into the table BUT the field "och_Dates" is
> not getting the proper value. It gets "0000-00-00".
>
> Frustrating. My code looks right and I echoed the value on the page
> and it is formatted properly. The field in mySQL is formatted as a
> DATE type.
>
> Little help please :-)
>
>
> Don Wieland
> D W D a t a C o n c e p t s
> ~~~~~~~~~~~~~~~~~~~~~~~~~
> donw@dwdataconcepts.com
> Direct Line - (949) 305-2771
>
> Integrated data solutions to fit your business needs.
>
> Need assistance in dialing in your FileMaker solution? Check out our
> Developer Support Plan at:
> http://www.dwdataconcepts.com/DevSup.html
>
> Appointment 1.0v9 - Powerful Appointment Scheduling for FileMaker Pro
> 9 or higher
> http://www.appointment10.com
>
> For a quick overview -
> http://www.appointment10.com/Appt10_Promo/Overview.html
>
>

--------------080907060103050103040106--