How to store/retreive date fields into/from MySql
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.
Hi All I have date field, I am storing it as "int" in table. When I checked the table in database, the value shows as 1. My code is as below. *'INSERT INTO {rs_table} ( uid, name, qualification, email, altemail, phone, altphone, dob, yrsofexp, prefworkloc) ' ."VALUES (%d, '%s', '%s', '%s', '%s', '%s', '%s', '%d', '%d', '%s')", $user->uid, $form_state['values']['persname'], $form_state['values']['hqualif'], $form_state['values']['email'], $form_state['values']['altemail'], $form_state['values']['phone'], $form_state['values']['altphone'], $form_state['values']['birthdate'], $form_state['values']['yearsexp'], $form_state['values']['prefloc'] );* Whats the wrong here, is there any API I have to make use of to convert from date to int value?? Regards Austin On Mon, Mar 28, 2011 at 8:36 AM, Austin Einter <austin.einter@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.
You might want to look at the strtotime function in php. I believe it would take that date and turn it into a Unix epoch number (i think that's what you're looking for) Brett Evanson On Mon, Mar 28, 2011 at 9:15 AM, Austin Einter <austin.einter@gmail.com> wrote:
Hi All I have date field, I am storing it as "int" in table. When I checked the table in database, the value shows as 1.
My code is as below.
'INSERT INTO {rs_table} ( uid, name, qualification, email, altemail, phone, altphone, dob, yrsofexp, prefworkloc) ' ."VALUES (%d, '%s', '%s', '%s', '%s', '%s', '%s', '%d', '%d', '%s')", $user->uid, $form_state['values']['persname'], $form_state['values']['hqualif'], $form_state['values']['email'], $form_state['values']['altemail'], $form_state['values']['phone'], $form_state['values']['altphone'], $form_state['values']['birthdate'], $form_state['values']['yearsexp'], $form_state['values']['prefloc'] );
Whats the wrong here, is there any API I have to make use of to convert from date to int value??
Regards Austin
On Mon, Mar 28, 2011 at 8:36 AM, Austin Einter <austin.einter@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/ ]
Even with this I get warning. Say the code is *strtotime($form_state['values']['birthdate'])* But $form_state['values']['birthdate'] is an array, but strtotime expects string. So I get warning "warning: strtotime() expects parameter 1 to be string, array given in C:\MyWeb6.20\wamp\www\livejobs1\sites\default\modules\resume_submit\resume_submit.module on line 564." How do I overcome this issue.... Thanks Austin On Mon, Mar 28, 2011 at 8:57 PM, Brett Evanson <brettev@gmail.com> wrote:
You might want to look at the strtotime function in php. I believe it would take that date and turn it into a Unix epoch number (i think that's what you're looking for)
Brett Evanson
On Mon, Mar 28, 2011 at 9:15 AM, Austin Einter <austin.einter@gmail.com> wrote:
Hi All I have date field, I am storing it as "int" in table. When I checked the table in database, the value shows as 1.
My code is as below.
'INSERT INTO {rs_table} ( uid, name, qualification, email, altemail, phone, altphone, dob, yrsofexp, prefworkloc) ' ."VALUES (%d, '%s', '%s', '%s', '%s', '%s', '%s', '%d', '%d', '%s')", $user->uid, $form_state['values']['persname'], $form_state['values']['hqualif'], $form_state['values']['email'], $form_state['values']['altemail'], $form_state['values']['phone'], $form_state['values']['altphone'], $form_state['values']['birthdate'], $form_state['values']['yearsexp'], $form_state['values']['prefloc'] );
Whats the wrong here, is there any API I have to make use of to convert from date to int value??
Regards Austin
On Mon, Mar 28, 2011 at 8:36 AM, Austin Einter <austin.einter@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/ ]
If you are storing birthdates, you probably want to use DATETIME in the schema instead of INT. INT would require the date be stored as a unix timestamp, which is only valid after the year 1970, and birthdays are likely to exist before this date. DATETIME accepts strings formatted like YYYY-MM-DD HH:MM:SS and generally you store the UTC date and convert the timezone later. See some of the functions in the Date API module for help in conversion (comes with Date module). Some of these utilities have been moved into core in Drupal 7. On Mon, Mar 28, 2011 at 9:27 AM, Brett Evanson <brettev@gmail.com> wrote:
You might want to look at the strtotime function in php. I believe it would take that date and turn it into a Unix epoch number (i think that's what you're looking for)
Brett Evanson
On Mon, Mar 28, 2011 at 9:15 AM, Austin Einter <austin.einter@gmail.com> wrote:
Hi All I have date field, I am storing it as "int" in table. When I checked the table in database, the value shows as 1.
My code is as below.
'INSERT INTO {rs_table} ( uid, name, qualification, email, altemail, phone, altphone, dob, yrsofexp, prefworkloc) ' ."VALUES (%d, '%s', '%s', '%s', '%s', '%s', '%s', '%d', '%d', '%s')", $user->uid, $form_state['values']['persname'], $form_state['values']['hqualif'], $form_state['values']['email'], $form_state['values']['altemail'], $form_state['values']['phone'], $form_state['values']['altphone'], $form_state['values']['birthdate'], $form_state['values']['yearsexp'], $form_state['values']['prefloc'] );
Whats the wrong here, is there any API I have to make use of to convert from date to int value??
Regards Austin
On Mon, Mar 28, 2011 at 8:36 AM, Austin Einter <austin.einter@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/ ]
Hi Carl Thanks for help. I am using "datetime" and placeholder as "%s" in schema.
From the form, I can collect the date as $form_state['values']['birthdate'], and its an array not a string. How do I convert "$form_state['values']['birthdate']" to a string. Is there any api or manually we need to code as below
$str = $form_state['values']['birthdate']['day'] .-. $form_state['values']['birthdate']['month'] .-. $form_state['values']['birthdate']['year'] If there is a better way, please let me know. Thanks Austin On Mon, Mar 28, 2011 at 10:06 PM, Carl Wiedemann <carl.wiedemann@gmail.com>wrote:
If you are storing birthdates, you probably want to use DATETIME in the schema instead of INT. INT would require the date be stored as a unix timestamp, which is only valid after the year 1970, and birthdays are likely to exist before this date.
DATETIME accepts strings formatted like
YYYY-MM-DD HH:MM:SS
and generally you store the UTC date and convert the timezone later.
See some of the functions in the Date API module for help in conversion (comes with Date module). Some of these utilities have been moved into core in Drupal 7.
On Mon, Mar 28, 2011 at 9:27 AM, Brett Evanson <brettev@gmail.com> wrote:
You might want to look at the strtotime function in php. I believe it would take that date and turn it into a Unix epoch number (i think that's what you're looking for)
Brett Evanson
On Mon, Mar 28, 2011 at 9:15 AM, Austin Einter <austin.einter@gmail.com> wrote:
Hi All I have date field, I am storing it as "int" in table. When I checked the table in database, the value shows as 1.
My code is as below.
'INSERT INTO {rs_table} ( uid, name, qualification, email, altemail, phone, altphone, dob, yrsofexp, prefworkloc) ' ."VALUES (%d, '%s', '%s', '%s', '%s', '%s', '%s', '%d', '%d', '%s')", $user->uid, $form_state['values']['persname'], $form_state['values']['hqualif'], $form_state['values']['email'], $form_state['values']['altemail'], $form_state['values']['phone'], $form_state['values']['altphone'], $form_state['values']['birthdate'], $form_state['values']['yearsexp'], $form_state['values']['prefloc'] );
Whats the wrong here, is there any API I have to make use of to convert from date to int value??
Regards Austin
On Mon, Mar 28, 2011 at 8:36 AM, Austin Einter <austin.einter@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/ ]
-- [ Drupal support list | http://lists.drupal.org/ ]
On Tue, 29 Mar 2011, Carl Wiedemann wrote:
If you are storing birthdates, you probably want to use DATETIME in the schema instead of INT. INT would require the date be stored as a unix timestamp, which is only valid after the year 1970, and birthdays are likely to exist before this date.
From wikipedia: http://en.wikipedia.org/wiki/Unix_time "The Unix time number is zero at the Unix epoch, and increases by exactly 86 400 per day since the epoch. Thus 2004-09-16T00:00:00Z, 12 677 days after the epoch, is represented by the Unix time number 12 677 × 86 400 = 1 095 292 800. This can be extended backwards from the epoch too, using negative numbers; thus 1957-10-04T00:00:00Z, 4 472 days before the epoch, is represented by the Unix time number −4 472 × 86 400 = -386 380 800." and "The standard Unix time_t (data type representing a point in time) is a signed integer data type, traditionally of 32 bits (but see below), directly encoding the Unix time number as described in the preceding section. Being 32 bits means that it covers a range of about 136 years in total. The minimum representable time is 1901-12-13, and the maximum representable time is 2038-01-19. At 03:14:07 UTC 2038-01-19 this representation overflows. This milestone is anticipated with a mixture of amusement and dread; see year 2038 problem." I looked into this issue once myself and decided that the advantages of adding and subtracting integers and applying SQL intdate<threshold tests on the database server was far easier/more efficient to manage than comparing strings. And also, I'll be retired by 2038. Someone elses problem!
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@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.
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@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@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/ ]
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@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@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@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/ ]
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@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@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@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@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/ ]
participants (4)
-
Austin Einter -
Brett Evanson -
Carl Wiedemann -
Doug du Boulay