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

[Carl Wiedemann]

Based on your example, it looks as though you were trying to create user
profiles on the site. If this is the case, the Content Profile module turns
user profiles into a separate content type, which can leverage CCK and
Views.

The Date module allows date-based CCK fields. So, it seems you could
accomplish at least some part of your example by adding a date field to the
profile content type which Content Profile provides. It might be worth
investigating, and could save some headache figuring out schema/CRUD
questions.


On Thu, Mar 31, 2011 at 5:25 PM, Austin Einter <austin.einter at gmail.com>wrote:

> 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/b842f953/attachment-0001.html