Insert date value in msql

[nyler01]

Good Day,
I am looking for threads here in daniweb which will contain my question but the return is not similar to my problem..

Please help me..

i don't know what to do with this. I just want to store date field in mysql database but i can't here is the code i am using

the post value is mm-dd-yyyy so i have to convert it to the format that mysql accepts.

//training period
$from=$_POST;
$to=$_POST;

//convert to mysql date format From
$dateFrom =$from;
$dateTimeFrom = new DateTime($dateFrom);

$formatted_from=date_format ( $dateTimeFrom, 'Y-m-d' );

//convert to mysql date format To
$dateTo =$to;
$dateTimeTo = new DateTime($dateTo);

$formatted_to=date_format ( $dateTimeTo, 'Y-m-d' );

$sql="INSERT INTO `transaction` (ProfileID, ProgramID, ProgramName, ModuleID, ModuleName, EnrollmentID, ProgramFee, OJTFee, WorkshopFee, NCAssessment, OtherFees, TotalAmount, Date, From, To)
Values ('$assessprofileid','$assessprogramid', '$assessprogram',$assessmoduleid, '$assessmodule', $assessenrollmentid, $programfee, $ojtfee, $workshopfee, $ncfee, $otherfees, $total, curdate(), '$formatted_from', '$formatted_to')";

// i also tried to remove the quotes from $formatted_from and $formatted_to but i still have an error

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'From) Values ('56','42', 'Faculty Development Program',13, 'Housekeeping', 34, ' at line 1

please help me..

thanks in advance

[Shanti C]

echo $sql="INSERT INTO `transaction` (ProfileID, ProgramID, ProgramName, ModuleID, ModuleName, EnrollmentID, ProgramFee, OJTFee, WorkshopFee, NCAssessment, OtherFees, TotalAmount, Date, From, To)
Values ('$assessprofileid','$assessprogramid', '$assessprogram',$assessmoduleid, '$assessmodule', $assessenrollmentid, $programfee, $ojtfee, $workshopfee, $ncfee, $otherfees, $total, curdate(), '$formatted_from', '$formatted_to')";

post echoed query.
and post your table structure.

[nyler01]

what? should i have to echo the query? i did not have any query aside from that insert query because the data that i am going to input from and to fields is came from a form in previous page..

if i am going to remove the formatted_from and formatted_to in query, this code is working but if i am going to add the

"INSERT INTO `transaction` (ProfileID, ProgramID, ProgramName, ModuleID, ModuleName, EnrollmentID, ProgramFee, OJTFee, WorkshopFee, NCAssessment, OtherFees, TotalAmount, Date, From, To)
Values ('$assessprofileid','$assessprogramid', '$assessprogram',$assessmoduleid, '$assessmodule', $assessenrollmentid, $programfee, $ojtfee, $workshopfee, $ncfee, $otherfees, $total, curdate(), '$formatted_from', '$formatted_to')" there is an error coming out..

please elaborate this instruction more so i can understand

"post echoed query.
and post your table structure."

thank you and i hope you can help me..

[Shanti C]

what is data type of your columns(From, To) in your data base table.

[nyler01]

the data type is "date"

[Shanti C]

iam asking you to print this query on browser then post this query here:

echo $sql="INSERT INTO `transaction` (ProfileID, ProgramID, ProgramName, ModuleID, ModuleName, EnrollmentID, ProgramFee, OJTFee, WorkshopFee, NCAssessment, OtherFees, TotalAmount, Date, From, To)
 Values ('$assessprofileid','$assessprogramid', '$assessprogram',$assessmoduleid, '$assessmodule', $assessenrollmentid, $programfee, $ojtfee, $workshopfee, $ncfee, $otherfees, $total, curdate(), '$formatted_from', '$formatted_to')";

[nyler01]

do you have any suggestion for my problem?

[SEOCoder]

Try this:

echo $sql="INSERT INTO `transaction` (ProfileID, ProgramID, ProgramName, ModuleID, ModuleName, EnrollmentID, ProgramFee, OJTFee, WorkshopFee, NCAssessment, OtherFees, TotalAmount, Date, From, To)
 Values ('$assessprofileid','$assessprogramid', '$assessprogram',$assessmoduleid, '$assessmodule', $assessenrollmentid, $programfee, $ojtfee, $workshopfee, $ncfee, $otherfees, $total, ".date('Y-m-d').", '$formatted_from', '$formatted_to')";

[nyler01]

the problem is on from and to field.. with value of $formatted_from and $formatted_to.. not on date and curdate() function..

[SEOCoder]

would this not give you what you want?

$from= date('Y-m-d', $_POST['from']);
$to= date('Y-m-d', $_POST['to']);

if not, post the result of $from and $to so we can see their original formats, then identify the format you are looking to convert it to.

[Shanti C]

or

$dateFrom ='18-08-1985';
$date = new DateTime($dateFrom);
echo $date->format('Y-m-d H:i:s');

[nyler01]

if i am using this
$from= date('Y-m-d', $_POST);

if i inputted 01-06-2011

the output is 1970-01-01

but if i am using this format

$dateFrom =$from;
$dateTimeFrom = new DateTime($dateFrom);

$formatted_from=date_format ( $dateTimeFrom, 'Y-m-d' );

the output is 2011-01-06

[SEOCoder]

so what output format are you looking to achieve??

[nyler01]

there is no output that i want to achieve. what i want is to insert the date to mysql table.. I don't know what is the problem since i converted the date values to mysql format but still it has an error when i am trying to run that code.

[nicksontinko]

I did not have any query aside from that insert query since the data that i am going to input from and to fields is came from a form in premature page.

[diafol]

Date and From are key words in MySQL. If you insist on calling your fields by these names, include them in backticks in your queries.

[nyler01]

thanks ardav!! You are right.. i changed the from and to to another keyword and i've already inserted the date value on the database.. thanks a lot..

I hope this thread will be helpful to others .. thank you my fellow daniwebian.. LOL

[tiggsy]

Hopefully, you told the person inputting to input the date in a specific format eg. MM/DD/YY or DD/MM/YYYY or whatever. So the first thing to do is to see if it's a valid date - the MM part must be a number between 1 and 12, DD must be a number between 1 and the length of the month you got in MM, and YYYY should be in a range you decided for yourself.

Check these for each date, then if they are wrong, issue an error message and send them back to the form, otherwise, split the date with eg.

$frompieces = explode("/",$from);
$topieces = explode("/",$to);

assuming that you are using standard American-style dates (mm/dd/yyyy), then month is in $frompieces[0], day in $frompieces[1] and year in $frompieces[2] - same deal with $topieces. The rest of this note assumes this is the format you used.

(replace / with whatever delimiter you are using)

then create a MySQL date with:

$fromdate = date("Y-m-d",gettime(0,0,0,date($frompieces[0]+0),date($frompieces[1]+0),date($frompieces[2]+0)));

(same for $todate, obviously replace "from" with "to" throughout)

The +0s are to stop you getting a warning about using strings instead of integers.

To save it, put it in single quotes in your query eg.

$sql="INSERT INTO `transaction` (ProfileID, ProgramID, ProgramName, ModuleID, ModuleName, EnrollmentID, ProgramFee, OJTFee, WorkshopFee, NCAssessment, OtherFees, TotalAmount, Date, From, To) values ('$assessprofileid','$assessprogramid', '$assessprogram',$assessmoduleid, '$assessmodule', $assessenrollmentid, $programfee, $ojtfee, $workshopfee, $ncfee, $otherfees, $total, '".date('Y-m-d')."', '$fromdate', '$todate')";

Does this answer all your questions?

[Hatem Salem]

Make Sure About Fields (From,To) Data Type on The Database That Fits Your Value of Date. Please Check That And Inform Us Here ..

Hatem

[nyler01]

Thanks. in my part, i am using a date picker so i dont need to restrict the inputs of the user. since the date picker is going to send the right format to the database. Thanks to all.

[diafol]

> Thanks. in my part, i am using a date picker so i dont need to restrict the inputs of the user. since the date picker is going to send the right format to the database. Thanks to all.

Datepickers use JS to store a date in a particular format. A spoofed form could be sent to your server which totally fries your SQL. Make sure you have robust server-side validation going on for your POST values. Do not rely on a datepicker to ensure that dates of a particular format are going to reach your server.

[nyler01]

Yes it reach the server properly because of some modifications in JS code.. btw.. Thanks for more information ardav.. Hope you will also help in my other problems.. Have a good day.. God Bless