Author Topic: Looking for suggestions on splitting first name from middle initial (Read 1405 times)

twowheeler · February 23, 2008, 06:48:14 pm

We have had volunteers pounding data into our civicrm database, which sometimes creates quality control problems.

At the moment we have a lot of records where the first name field contains both the first name and a middle initial. In fact, over 900 of them. Editing each record to fix it would take a long time. Is there a way to fix this en masse? I can select the records in Search Builder with first name equal to "% %", but am not sure what to do with them. I'd like to just move the middle initial over to the middle name field. Any suggestions appreciated.

Donald Lobo · February 23, 2008, 09:47:26 pm

your best bet would be to write a php script to go in and modify the table directly. alternatively if you are good at sql, you might even be able to do this in sql. If so please post back your solution for our education

lobo

twowheeler · February 24, 2008, 06:43:12 am

Heh. That's what I was afraid you would say. I'll work on it.

twowheeler · February 24, 2008, 02:09:48 pm

Ok, this worked.

Code: [Select]

<?php

// Script to find & fix contacts with first name and middle name concatenated in the first name field

if (module_exists('civicrm')) {
	civicrm_initialize(TRUE);

	// get ids of contacts
	$sql = "SELECT contact_id FROM civicrm_individual WHERE first_name LIKE '% _' ";
	if (!$result = mysql_query( $sql )) {
		echo 'No records selected<br>';
		exit;
	}

	// for each id get the contact 
	$count = 0;
	while ($item = mysql_fetch_assoc($result)) {
		$data = crm_fetch_contact($item);
		$contact = crm_get_contact($item);
		if ( ! is_a( $contact, 'CRM_Core_Error' )) {
			$name = $data["first_name"];
			list($fname, $minitial) = sscanf($name, "%s %s");
			$params = array("first_name" => $fname, "minitial" => $minitial);
			echo "Name: $name  First: $fname  Middle: $minitial<br>";	
			//crm_update_contact(&$contact, $params);	// uncomment this line when you are ready
			$count++;
		} else {
			echo 'Failed to get contact id='.$item['contact_id'].'<br>';
		}
	}
	echo "$count contacts updated<br>";
}
?>

Dave Greenberg · February 25, 2008, 01:03:47 pm

Would be great if you added this as a child page in the Cookbooks section - I suspect others will need it :-) (Please specify the CiviCRM version you developed it for.)...

http://wiki.civicrm.org/confluence/display/CRMDOC/Cookbooks

CiviCRM Community Forums (archive)

News:

Author Topic: Looking for suggestions on splitting first name from middle initial (Read 1405 times)

twowheeler

Looking for suggestions on splitting first name from middle initial

Donald Lobo

Re: Looking for suggestions on splitting first name from middle initial

twowheeler

Re: Looking for suggestions on splitting first name from middle initial

twowheeler

Re: Looking for suggestions on splitting first name from middle initial

Dave Greenberg

Re: Looking for suggestions on splitting first name from middle initial