CiviCRM Community Forums (archive)

Hello,

This isn't really a 'using import' forum topic.  More a 'Import techniques' advanced import forum post.  Pete asked me about a script to condense multiple memberships into one.

Copied below is a script I used to do this once.  The memberships existed in one civicrm database and they got condensed in this script and transfered into another database.

The killer bit is

SELECT
    contact_id,
    membership_type_id,
    MIN(join_date) as join_date,
    MIN(start_date) as start_date,
    MAX(end_date) as end_date,
    source,
    status_id,
    is_override,
    reminder_date,

FROM
    `civicrm_membership`

WHERE is_test!=1

GROUP BY contact_id

This query will takes the min and max start and end date but the rest of the fields are taken at random from one particular row.  If you want to garuntee a particular row (say the latest row) you'll need to use a 'within-group aggregate' query - http://www.artfulsoftware.com/infotree/queries.php?&bw=1920#101

PS.  The includes at the top include some functions that allow you to translate from external id to civicrm id, put a database query a database into an array and not that much else

Hope this is helpful

Michael

<?php

include('../config.php');
include('/Users/michaelmcandrew/p/functions.php');

require_once "api/v2/Membership.php";
require_once 'api/v2/MembershipContact.php';


$eit=getEit();


$membersRaw=query('

SELECT
   contact_id,
   membership_type_id,
   MIN(join_date) as join_date,
   MIN(start_date) as start_date,
   MAX(end_date) as end_date,
   source,
   status_id,
   is_override,
   reminder_date,

FROM
   `civicrm_membership`

WHERE is_test!=1

GROUP BY contact_id

');

foreach($membersRaw as $memberRaw) {
   $member = $memberRaw;
   $member['contact_id'] = $eit[$memberRaw['contact_id']];
   $member['source'] = $memberRaw['fn'].' '.$memberRaw['ln'];
   $r = civicrm_contact_membership_create($member);
   if($r['is_error']){
      print_r($r);
      print_r($member);
   } else {
      echo '.';
   }
}

good way to start would be describing your use case in more detail.  are all of these in one civicrm db? are you doing an import? can you give an example of the merge you want to do?