Posting date to mysql

What's the trick? I need to record the contents of a datetimepicker field to my mysql database in a date field...and there are vague references to it here but nothing spells it out.

Can someone help me out please?

Hi cbehan,

the date format coming out from the picker is not in the mysql format i think so use a VARCHAR field instead!

regards
Max

Is there a way to convert it? I need to use the data for some reporting and dates would make it much easier.

I think there is a trick to use, but first try to have a DB field of type VARCHAR, do you get your data saved there ?

If it's a varchar it saves the date to the db in dd/mm/yyyy format.

Hi cbehan,

Here are a couple of snippets that I've used to convert date for a calendar to a MySQL date and back. My calendar format is 'dd-mm-yyyy' so you'll need to modify them a bit. To access from the database

$some_date = date('d-m-Y', strtotime($some_date));
 if ( $some_date == '01-01-1970' ) {
 $some_date = '';
}

and to save to the database

if ( $some_date == '' ) {
  $some_date = '0000-00-00 00:00';
} else {
  $temp_date = explode('-', $some_date);
  $some_date = $temp_date[2]."-".$temp_date[1]."-".$temp_date[0]." 00:00:00";
}

Bob

Hi cbehan,

Here are a couple of snippets that I've used to convert date for a calendar to a MySQL date and back. My calendar format is 'dd-mm-yyyy' so you'll need to modify them a bit. To access from the database

$some_date = date('d-m-Y', strtotime($some_date));
 if ( $some_date == '01-01-1970' ) {
 $some_date = '';
}

and to save to the database

if ( $some_date == '' ) {
  $some_date = '0000-00-00 00:00';
} else {
  $temp_date = explode('-', $some_date);
  $some_date = $temp_date[2]."-".$temp_date[1]."-".$temp_date[0]." 00:00:00";
}

Bob

Thanks Bob, but one more question: Where do I put this code?

Hi cbehan,

try this in the onsubmit before email box (assuming you have some email setup in your form ?)

<?php
JRequest::setVar('date_field_name', date('d-m-Y', strtotime(JRequest::getVar('date_field_name'))));
?>

Where should i put this code?

<?php
JRequest::setVar('date_field_name', date('d-m-Y', strtotime(JRequest::getVar('date_field_name'))));
?>

Here ?

right, I see you changed it alittle but I'm not sure if it will work after your changes!

Max

is not working in my case... 😟
But i solved my case using ServerSide Validation. 😀 I put my code there.

<?php
$dt=explode("/",JRequest::getVar('date_1'));
$dt_time=$dt[2]."-".$dt[1]."-".$dt[0]." - 00:00:00";
JRequest::setVar( 'date_1', $dt_time);
?>

note: i have to use explode because

date('Y-m-d - H:i:ss',strtotime(JRequest::getVar('date_1')))

didn't work. very strange !! it always return "01/01/1970 07:00:00". did i miss something?

I think the strtotime function didn't like something with the string!! :?