Code to Search and Replace all user id's in all tables

SuiteCRM Forum - English Language Developer Collaboration
1

I recently had to replace a user id in all tables in SuiteCRM

I wrote the following code and am sharing it in case anyone finds it useful


if (! defined ( 'sugarEntry' ) || ! sugarEntry)
	die ( 'Not A Valid Entry Point' );

	$old='ORIGID';
	$new = 'NEWID';

$db = DBManagerFactory::getInstance ();

$fields = array (
		'assigned_user_id',
		'modified_user_id',
		'user_id',
		'created_by' 
);

foreach ( $fields as $field ) {
	$sql = ("SELECT DISTINCT table_name
		FROM   information_schema.`COLUMNS` C
		WHERE  TABLE_SCHEMA = 'YOURDATABASE'
		and C.COLUMN_NAME = '$field'
		");
	
	$result = $db->query ( $sql, true );
	while ( $row = $db->fetchByAssoc ( $result ) ) {
		
		$table = $row ['table_name'];
		
		if (preg_match ( '/user_pref/', $table ))
			continue;
		
		$sql = ("
				SELECT count(*)
				FROM   $table
				WHERE  $field = '$old' 
				");
		
		$count = $db->GetOne ( $sql, true );
		
		if ($count > 0) {
			print "$table $field $count" . '<br>';
			
			$sql = ("
				UPDATE $table
					set $field = '$new'
					where $field = '$old' 
			");
			
			$db->Query($sql,true);
		}
	}
}
exit ();
1 Like