CiviCRM Community Forums (archive)

I have an issue while trying to create a membership using the civicrm_contact_membership_create api. What I have found is that the DATE fields won't insert into the table. It appears that the insert statement is not being properly created, because the date's are interpreted as being numbers, not strings.. Here's the params and the result.  I'm using CiviCRM 2.0.3 and Drupal 5.7 (php5 and Mysql 5)

Here's the parameters to the call:

Membership = Array
(
    [membership_type_id] => 2
    [contact_id] => 9453
    [join_date] => 2007/01/20
    [start_date] => 2007/01/20
    [end_date] => 2007/06/30
    [custom_56] => 70.0000
)

And here's the result:

Error Details:

Array
(
    [callback] => Array
        (
            
 => CRM_Core_Error
[1] => handle
        )

    [ code] => -1
    
[message] => DB Error: unknown error
    [mode] => 16
    [debug_info] => INSERT INTO civicrm_membership (contact_id , membership_type_id , join_date , start_date , end_date , status_id , is_override , reminder_date ) VALUES ( 9453 ,  2 ,  2007 ,  2007 ,  2007 ,  4 ,  NULL ,  NULL )  [nativecode=1292 ** Incorrect date value: '2007' for column 'join_date' at row 1]
    [type] => DB_Error
    [user_info] => INSERT INTO civicrm_membership (contact_id , membership_type_id , join_date , start_date , end_date , status_id , is_override , reminder_date ) VALUES ( 9453 ,  2 ,  2007 ,  2007 ,  2007 ,  4 ,  NULL ,  NULL )  [nativecode=1292 ** Incorrect date value: '2007' for column 'join_date' at row 1]
    [to_string] => [db_error: message="DB Error: unknown error" code=-1 mode=callback callback=CRM_Core_Error::handle prefix="" info="INSERT INTO civicrm_membership (contact_id , membership_type_id , join_date , start_date , end_date , status_id , is_override , reminder_date ) VALUES ( 9453 ,  2 ,  2007 ,  2007 ,  2007 ,  4 ,  NULL ,  NULL )  [nativecode=1292 ** Incorrect date value: '2007' for column 'join_date' at row 1]"]
)

After some investigation, I have found that the table is defined this way.

civicrm_membership table items => Array
(
    [id] => 129
    [contact_id] => 129
    [membership_type_id] => 129
    [join_date] => 4
    [start_date] => 4
    [end_date] => 4
    [source] => 2
    [status_id] => 129
    [is_override] => 16
    [reminder_date] => 4
    [owner_membership_id] => 1
    [is_test] => 16
)

Note that all the date fields have a value of 4 (DB_DATABOBJECT_DATE).  This sounds good at first blush, but my impression is that the [start_date]  et al. should really have a value of 6 (DB_DATABOBJECT_DATE + DB_DATABOBJECT_STR) not just 4.  This would cause the date value to be added to the insert as a string (quoted), but because this is not set, it's failing in this way.

Now, because I can, I changed DB_DataObject.insert() to add in the DATE flag in the DataObject.php at line 994 (ish) where the string is output, so that any date field would output as a string. This had the correct result in that it quoted the date fields, but it caused the [contact_id] field to become corrupt. (showing a 4 instead of 9453) See Below:

            if ($v & (DB_DATAOBJECT_STR | DB_DATAOBJECT_DATE) {
                $rightq .= $this->_quote((string) (
                        ($v & DB_DATAOBJECT_BOOL) ?
                            // this is thanks to the braindead idea of postgres to
                            // use t/f for boolean.
                            (($this->$k == 'f') ? 0 : (int)(bool) $this->$k) : 
                            $this->$k
                    )) . " ";
                continue;
            }

I am wondering if this is actually an issue with the field flags not being set correctly on this table definition because A) the DATE field type is not handled in the insert() method (except for a null check), and B) the Generator code looks like it should always be setting both the DATE and STR flags for any date field in the database.

Can someone tell me what I'm doing wrong?

As you initially guessed the problem is with date formats.

I tried it this way & it works perfectly :
$Membership = array (
                             'membership_type_id' => 2,
                             'contact_id' => 102,
                             'join_date'  => 20070120,
                             'start_date' => 20070120,
                             'end_date'   => 20070630,
                             );

There is a similar issue discussed in this post - http://forum.civicrm.org/index.php/topic,3325.0.html

Ohk, now i can understand your second post and looks correct that checkMembershipRelationship() returns a key => value pair and not only contact-Ids.

But the pair is [related_contact_id] => relationship_status and NOT [related_contact_id] => membership_type, where the value 1 for relationship_status implies past relationship, 2 for disabled and 3 for current relationship.

would be great if you can

Thanks