CUNY Academic Commons

<?php

global $wpdb, $bp;

$blog_ids = $wpdb->get_col( "SELECT DISTINCT blog_id FROM {$bp->blogs->table_name_blogmeta}" );

$has_dupes = [];

foreach ( $blog_ids as $blog_id ) {

	$blog_metas = $wpdb->get_results( $wpdb->prepare( "SELECT * FROM {$bp->blogs->table_name_blogmeta} WHERE blog_id = %d", $blog_id ) );

	$blog_meta_ids = [];

	foreach ( $blog_metas as $blog_meta ) {

		$key   = $blog_meta->meta_key;

		$value = $blog_meta->meta_value;

		if ( ! isset( $blog_meta_ids[ $key ][ $value ] ) ) {

			$blog_meta_ids[ $key ][ $value ] = 0;

		}

		$blog_meta_ids[ $key ][ $value ]++;

	}

	foreach ( $blog_meta_ids as $the_key => $values ) {

		foreach ( $values as $value => $count ) {

			if ( $count > 1 ) {

				$has_dupes[ $blog_id ][] = $the_key;

				break;

			}

		}

	}

}

foreach ( $has_dupes as $blog_id => $dupe_keys ) {

	foreach ( $dupe_keys as $dupe_key ) {

		$dupe_ids = $wpdb->get_col( $wpdb->prepare( "SELECT id FROM {$bp->blogs->table_name_blogmeta} WHERE blog_id = %d and meta_key = %s LIMIT 1, 1000", $blog_id, $dupe_key ) );

		$dupe_ids_sql = implode( ',', array_map( 'intval', $dupe_ids ) );

		$wpdb->query( "DELETE FROM {$bp->blogs->table_name_blogmeta} WHERE id in ({$dupe_ids_sql})" );

	}

}