DOB as optional in v2.2 RC2a

[Jaskaran]

Hi to all!

 

I want to keep Date of Birth as optional in v2.2 RC2a. How i can do it??

 

Thanks!!

[Jan Zonjee]

How i can do it??

admin->Configuration->Customer Details->Date of Birth to false?

[Jaskaran]

admin->Configuration->Customer Details->Date of Birth to false?

 

But it hide the Dob Field from Form.

what i want, form process if Dob remain blank by any customer, bt if somebody want to fill then it validate against date format mm/dd/yy.

 

Thanks!!

[♥FWR Media]

But it hide the Dob Field from Form.

what i want, form process if Dob remain blank by any customer, bt if somebody want to fill then it validate against date format mm/dd/yy.

 

Thanks!!

 

There are a few database fields in osCommerce that should be Null but as standard are not null a couple of examples in the customers table are ..

 

customers_gender

customers_dob

 

an example in address_book is

 

entry_gender

 

To cure your issue go into phpMyAdmin

 

Click on table customers

 

Edit the field customers_dob change

 

Null - not null to

Null - null

[Jaskaran]

There are a few database fields in osCommerce that should be Null but as standard are not null a couple of examples in the customers table are ..

 

customers_gender

customers_dob

 

an example in address_book is

 

entry_gender

 

To cure your issue go into phpMyAdmin

 

Click on table customers

 

Edit the field customers_dob change

 

Null - not null to

Null - null

 

Thank you for reply..

 

i changed not null TO null.

But doesn't work..

 

any other idea??

[Jan Zonjee]

To cure your issue go into phpMyAdmin

No need for that IMHO but there would be a number of changes necessary in the code:

 

create_account.php:

 

Around line 80 change to:

 

   if (ACCOUNT_DOB == 'true') {
// add (strlen(trim($dob)) > 0) && 
     if ((strlen(trim($dob)) > 0) && checkdate(substr(tep_date_raw($dob), 4, 2), substr(tep_date_raw($dob), 6, 2), substr(tep_date_raw($dob), 0, 4)) == false) {
       $error = true;

 

Around line 186 change to:

 

     if (ACCOUNT_GENDER == 'true') $sql_data_array['customers_gender'] = $gender;
// add && (strlen(trim($dob)) > 0)
     if (ACCOUNT_DOB == 'true' && (strlen(trim($dob)) > 0)) $sql_data_array['customers_dob'] = tep_date_raw($dob);

 

includes/form_check.js.php

 

Around line 110 change to (comment out):

 

<?php // if (ACCOUNT_DOB == 'true') echo '  check_input("dob", ' . ENTRY_DOB_MIN_LENGTH . ', "' . ENTRY_DATE_OF_BIRTH_ERROR . '");' . "\n"; ?>

 

account_edit.php:

 

Around line 55 change to:

 

   if (ACCOUNT_DOB == 'true') {
// add (strlen(trim($dob)) > 0) && 
     if ((strlen(trim($dob)) > 0) && !checkdate(substr(tep_date_raw($dob), 4, 2), substr(tep_date_raw($dob), 6, 2), substr(tep_date_raw($dob), 0, 4))) {
       $error = true;

[♥FWR Media]

I must have misunderstood this thread.

 

The only reason I mentioned what I did is that on my servers if those fields are left as not null then the form will not process with e.g. customers DOB off as the database throws an error because you are entering a NULL value into a not null field.

[Jan Zonjee]

on my servers if those fields are left as not null then the form will not process with e.g. customers DOB off as the database throws an error because you are entering a NULL value into a not null field.

Interesting and puzzling... In standard osC dob is a not null field with a default value of 0000-00-00 00:00:00

`customers_dob` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',

Since entering DOB is already an option chosen in the admin it just isn't added to the values for inserting when displaying DOB is set to false there.

 

I know some MySQL server configurations to be picky. I think the problem was that if it was a "strict" version it would complain about entering nothing ('') instead of null for a value where there was a default value. Could that be the problem?

[Jaskaran]

Thank you "Jan Zonjee" :thumbsup:

 

Its working!!!

 

Thanks to you also FWR!!

[♥FWR Media]

Interesting and puzzling... In standard osC dob is a not null field with a default value of 0000-00-00 00:00:00

`customers_dob` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',

Since entering DOB is already an option chosen in the admin it just isn't added to the values for inserting when displaying DOB is set to false there.

 

I know some MySQL server configurations to be picky. I think the problem was that if it was a "strict" version it would complain about entering nothing ('') instead of null for a value where there was a default value. Could that be the problem?

 

0000-00-00 00:00:00 is an invalid datetime Jan these things are bugs in osCommerce but only cause issues on stricter databases so rarely raise their heads.

[Jan Zonjee]

0000-00-00 00:00:00 is an invalid datetime Jan these things are bugs in osCommerce but only cause issues on stricter databases so rarely raise their heads.

0000-00-00 00:00:00 Is an invalid datetime, in a way, but osC doesn't try to insert that and MySQL even excepts it as a value:

 

As of MySQL 5.0.2, MySQL does not accept timestamp values that include a zero in the day or month column or values that are not a valid date. The sole exception to this rule is the special value '0000-00-00 00:00:00'.

Only thing I could foresee could be a problem that customers_dob is not added to values when inserting a row into customers when displaying DOB is set to false in the admin.

 

Perhaps the error is that MySQL in strict mode wants you to add it with a null value in the insert query?

[♥FWR Media]

0000-00-00 00:00:00 Is an invalid datetime, in a way, but osC doesn't try to insert that and MySQL even excepts it as a value:

 

 

Only thing I could foresee could be a problem that customers_dob is not added to values when inserting a row into customers when displaying DOB is set to false in the admin.

 

Perhaps the error is that MySQL in strict mode wants you to add it with a null value in the insert query?

 

Actually, my apologies I was mis-remembering it was not customers_dob that was the issue at all it was customers gender, sorry.

 

Well to give you a precise example: -

 

I load a brand new fresh RC2a.

 

Turn off customers dob and gender via admin.

 

Attempt to create an account and get the following: -

 

1364 - Field 'customers_gender' doesn't have a default value

 

insert into customers (customers_firstname, customers_lastname, customers_email_address, customers_telephone, customers_fax, customers_newsletter, customers_password) values ('Robert', 'Fisher', 'xxxxxx', 'xxxxxx', 'xxxxxx', '', 'xxxxxx')

 

So I set customers.gender to NULL then try again and get ..

 

1364 - Field 'entry_gender' doesn't have a default value

 

insert into address_book (customers_id, entry_firstname, entry_lastname, entry_street_address, entry_postcode, entry_city, entry_country_id, entry_company, entry_suburb, entry_zone_id, entry_state) values ('2', 'Robert', 'Fisher', 'xxxxx', 'xxxx', 'xxxx', '222', '', 'xxxx', '0', 'xxxx')

 

So I set address_book.entry_gender to NULL and it finally creates an account ...

[Jan Zonjee]

1364 - Field 'entry_gender' doesn't have a default value

 

insert into address_book (customers_id, entry_firstname, entry_lastname, entry_street_address, entry_postcode, entry_city, entry_country_id, entry_company, entry_suburb, entry_zone_id, entry_state) values ('2', 'Robert', 'Fisher', 'xxxxx', 'xxxx', 'xxxx', '222', '', 'xxxx', '0', 'xxxx')

 

So I set address_book.entry_gender to NULL and it finally creates an account ...

OK, so that is a genuine bug (don't know if it is in the bug database).

 

So declaring those two columns to be nullable and default NULL should be better. I'll propose to add this to RC3 although perhaps it should have been an enum field (values m and f) (nullable with default NULL) because that is how the field is used.

[♥FWR Media]

OK, so that is a genuine bug (don't know if it is in the bug database).

 

So declaring those two columns to be nullable and default NULL should be better. I'll propose to add this to RC3 although perhaps it should have been an enum field (values m and f) (nullable with default NULL) because that is how the field is used.

 

Yes, tbh Jan I never really thought about the correct way to remedy this I just adapted the install SQL so that I could quickly load a fresh RC2a in an sql mode STRICT_TRANS_TABLES environment without faffing about.

[burt]

So declaring those two columns to be nullable and default NULL should be better. I'll propose to add this to RC3 although perhaps it should have been an enum field (values m and f) (nullable with default NULL) because that is how the field is used.

 

Jan, please do propose this!

[Jan Zonjee]

Jan, please do propose this!

I already did. No guarantees though :)

 

I think the most foolproof root to go to an enum field would be this:

 

alter table customers modify customers_gender char(1) default null;
update customers set customers_gender = null where customers_gender != 'm' or customers_gender != 'f';
alter table customers modify customers_gender enum('m','f') default null;

alter table address_book modify entry_gender char(1) default null;
update address_book set entry_gender = null where entry_gender != 'm' or entry_gender != 'f';
alter table address_book modify entry_gender enum('m','f') default null;

 

and for the the feminists among us :) it could be this version:

 

alter table customers modify customers_gender char(1) default null;
update customers set customers_gender = null where customers_gender != 'm' or customers_gender != 'f';
alter table customers modify customers_gender enum('f','m') default null;

alter table address_book modify entry_gender char(1) default null;
update address_book set entry_gender = null where entry_gender != 'm' or entry_gender != 'f';
alter table address_book modify entry_gender enum('f','m') default null;

 

If for some reason another entry than m or f would be in a table somwhere going straight to the enum field would throw an error.

 

This issue has been mentioned in the bug tracking site OSC-556.

[Jaskaran]

Hi!

 

Changes work beautifully but

i am facing a new problem now..

 

I get a warning message..

Warning: checkdate() expects parameter 2 to be long, string given in C:\xampp\htdocs\shum12\create_account.php on line 119

 

Wat i find..

When i write a wrong month(in whole date)like 21/21/1970,warning is like

Warning: checkdate() expects parameter 1 to be long, string given in C:\xampp\htdocs\shum12\create_account.php on line 119

 

When i write a wrong date(in whole date)like 21/212/1970,warning is like

Warning: checkdate() expects parameter 2 to be long, string given in C:\xampp\htdocs\shum12\create_account.php on line 119

 

When i write a wrong year(in whole date)like 02/21/11970,

This date stored in database like 1197-21-02

 

how to resolve this?

 

Thanks!!

[Jan Zonjee]

Hi!

 

Changes work beautifully but

i am facing a new problem now..

 

I get a warning message..

Warning: checkdate() expects parameter 2 to be long, string given in C:\xampp\htdocs\shum12\create_account.php on line 119

 

Wat i find..

When i write a wrong month(in whole date)like 21/21/1970,warning is like

Warning: checkdate() expects parameter 1 to be long, string given in C:\xampp\htdocs\shum12\create_account.php on line 119

 

When i write a wrong date(in whole date)like 21/212/1970,warning is like

Warning: checkdate() expects parameter 2 to be long, string given in C:\xampp\htdocs\shum12\create_account.php on line 119

 

When i write a wrong year(in whole date)like 02/21/11970,

This date stored in database like 1197-21-02

 

how to resolve this?

 

Thanks!!

A. What is line 119. We don't know that.

B. Did you change back the dob field in create account to have a default vaule of 0000-00-00 00:00:00 (not relevant here)

C. Is the way you write the date the same as expected in the language you use for create_account.php. See e.g. includes/languages/english.php: tep_date_raw expect the date to be in the anglosaxon format of mm/dd/yyyy but in german.php it is expected in the form of dd/mm/yyyy

[Jaskaran]

A. What is line 119. We don't know that.

 

Line no 119 is..

 

if ((strlen(trim($dob)) > 0) && checkdate(substr(tep_date_raw($dob), 4, 2), substr(tep_date_raw($dob), 6, 2), substr(tep_date_raw($dob), 0, 4)) == false) {

 

i tried to solve the problem using is_int

 

if ((strlen(trim($dob)) > 0) && checkdate(substr(tep_date_raw(is_int($dob)), 4, 2), substr(tep_date_raw(is_int($dob)), 6, 2), substr(tep_date_raw(is_int($dob)), 0, 4)) == false) {

 

but its worthless...

 

C. Is the way you write the date the same as expected in the language you use for create_account.php. See e.g. includes/languages/english.php: tep_date_raw expect the date to be in the anglosaxon format of mm/dd/yyyy but in german.php it is expected in the form of dd/mm/yyyy

 

Yes,its mm/dd/yyyy

 

 

Thanks!

[Jan Zonjee]

Line no 119 is..

 

if ((strlen(trim($dob)) > 0) && checkdate(substr(tep_date_raw($dob), 4, 2), substr(tep_date_raw($dob), 6, 2), substr(tep_date_raw($dob), 0, 4)) == false) {

 

i tried to solve the problem using is_int

 

if ((strlen(trim($dob)) > 0) && checkdate(substr(tep_date_raw(is_int($dob)), 4, 2), substr(tep_date_raw(is_int($dob)), 6, 2), substr(tep_date_raw(is_int($dob)), 0, 4)) == false) {

 

Yes,its mm/dd/yyyy

I think you should not see the warnings since application_top.php is supposed to surpress them. I might be wrong. If you use 21 for a month the checkdate should return false. Perhaps just surpressing the warning (adding @ before checkdate) suffices:

 if ((strlen(trim($dob)) > 0) && @checkdate(substr(tep_date_raw($dob), 4, 2), substr(tep_date_raw($dob), 6, 2), substr(tep_date_raw($dob), 0, 4)) == false) {

[Jaskaran]

I think you should not see the warnings since application_top.php is supposed to surpress them. I might be wrong. If you use 21 for a month the checkdate should return false. Perhaps just surpressing the warning (adding @ before checkdate) suffices:

 if ((strlen(trim($dob)) > 0) && @checkdate(substr(tep_date_raw($dob), 4, 2), substr(tep_date_raw($dob), 6, 2), substr(tep_date_raw($dob), 0, 4)) == false) {

 

 

Thanks!!!