[support] How to store/retreive date fields into/from MySql

[Austin Einter]

Thanks Carl. This worked for me with schema as datetime.

Schema used as "  'dob' => array('type' => 'datetime', 'length' => 19,
'default' => '0000-00-00 00:00:00'),   "

If I use schema as date,  when I reinstall module, I get error.

Schema used as " 'dob' => array('type' => 'date', 'length' => 19, 'default'
=> '0000-00-00'), "

Am I doing anything wrong with Schema declaration?.

Error
-------------
*user warning: 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
'DEFAULT '0000-00-00', `yrsofexp` INT unsigned NOT NULL DEFAULT 0,
`prefworkloc' at line 9 query: CREATE TABLE resubmt_table ( `uid` INT
unsigned NOT NULL DEFAULT 0, `name` VARCHAR(64) NOT NULL DEFAULT '',
`qualification` VARCHAR(64) NOT NULL DEFAULT '', `email` VARCHAR(64) NOT
NULL DEFAULT '', `altemail` VARCHAR(64) NOT NULL DEFAULT '', `phone`
VARCHAR(15) NOT NULL DEFAULT '', `altphone` VARCHAR(15) NOT NULL DEFAULT '',
`dob` DEFAULT '0000-00-00', `yrsofexp` INT unsigned NOT NULL DEFAULT 0,
`prefworkloc` VARCHAR(64) NOT NULL DEFAULT '', INDEX name (name), INDEX
yrsofexp (yrsofexp), INDEX prefworkloc (prefworkloc) ) /*!40100 DEFAULT
CHARACTER SET utf8 */ in
C:\MyWeb6.20\wamp\www\livejobs1\includes\database.inc on line 551.*


As such there is no reason for not using Date, Content-profile. I do not
know what content-profile does .., I will make use of Date.

Regards
Austin.
On Tue, Mar 29, 2011 at 10:40 AM, Carl Wiedemann
<carl.wiedemann at gmail.com>wrote:

> I assume you're using Drupal 6. See the date_api.module functions like
> date_make_date() which will return a Date object that can be formatted with
> date_format_date().
>
> Your schema type should be DATETIME or DATE
> http://dev.mysql.com/doc/refman/5.0/en/datetime.html
>
> You probably want to have something like this to INSERT/UPDATE the db.
>
> $date_array['year'] . '-' . $date_array['month'] . '-' . $date_array['day']
> . ' 00:00:00';
>
> Then, when you SELECT the value you can do something like this.
>
> $displayed_date = date_format_date(date_make_date($value, 'UTC'), 'custom',
> 'm/d/Y');
>
>  Non-language specific strings do not need to be handled with t(), too. :)
>
> Also, is there a reason you're not using Content Profile with Date module
> to accomplish your task?
> http://drupal.org/project/content_profile
> http://drupal.org/project/date
>
>
>   On Mon, Mar 28, 2011 at 10:49 PM, Austin Einter <austin.einter at gmail.com
> > wrote:
>
>>   After all search, I found it really difficult to deal with date. I am
>> trying to store the date in MySql table as varchar type and placeholder as
>> %s.
>>
>> However I need two functions to convert date from array format to string
>> format (required while storing into table) and string format to array format
>> (Required while showing it in form).
>>
>> The below code does that for me.
>>
>>
>> *function dateArrToStr(&$customDateArr)
>> {
>>  $strDate = t($customDateArr['day']).t('-
>> ').t($customDateArr['month']).t('-') . t($customDateArr['year']);
>>  return $strDate;
>> }*
>> **
>> **
>> *function dateStrToArr (&$customDateStr, $length)
>> {
>>     $convDate = array();
>>  $index = 0;
>>  $customToken = t('');
>>  $dashPosition = 0;
>>
>>  for($index = 0; $index < $length; $index++)
>>  {
>>   if($customDateStr[$index] == t('-'))
>>   {
>>    if($dashPosition == 0)
>>    {
>>     //Set the day
>>     $dashPosition++;
>>        $convDate['day'] =  $customToken;
>>    }
>>    else if ($dashPosition == 1)
>>    {
>>     //Set the month
>>     $dashPosition++;
>>     $convDate['month'] = $customToken;
>>    }
>>    $customToken = t('');
>>   }
>>   else
>>   {
>>    $customToken = t($customToken) . t($customDateStr[$index]) ;
>>   }
>>  }
>>  //Set year
>>  $convDate['year'] = $customToken;
>>  return $convDate;
>> }*
>>
>>
>> Regards
>> Austin
>>
>> On Mon, Mar 28, 2011 at 8:36 AM, Austin Einter <austin.einter at gmail.com>wrote:
>>
>>> Hi All
>>> I have a date field in my form.
>>> How do I store/retreive the value into/from MySQL.
>>>
>>> On google search, I saw people are suggesting to use either "int" or
>>> "datetime" type.
>>> Please suggest which one needs to be used.
>>>
>>> If I use int (I hope core uses int), while storing can I just
>>> store "form['values']['date1']" as %d and while retreivin, what are the
>>> related apis to convert back to date.
>>>
>>> Regards
>>> Austin.
>>>
>>>
>>>
>>
>>
>> --
>> [ Drupal support list | http://lists.drupal.org/ ]
>>
>
>
> --
> [ Drupal support list | http://lists.drupal.org/ ]
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.drupal.org/pipermail/support/attachments/20110401/8d21a1f7/attachment-0001.html