Change the Collation of all tables in a WordPress Database. Will try to extract Database details from wp-config.php file.

change-db-collation.php

<?php
/*
 * Change the Collation of all tables in a WordPress Database
 * 
 * WARNING! WARNING! WARNING! WARNING! WARNING! WARNING!
 * Take a backup first, and carefully test the results of this code.
 * USE OF THIS SCRIPT IS ENTIRELY AT YOUR OWN RISK. I/We accept no liability from its use.
 * 
 * USE:
 * Simply place this script in the root of your WordPress site and run it from your browser.
 * It will change the Collation of all tables to the preferred WP Collation (specified below).
 *
 * Written: Anthony Hortin
 * Maddison Designs
 * https://maddisondesigns.com
 * 
 * License: GPL v3
 * License URL: http://www.gnu.org/copyleft/gpl.html 
 *
 */

$hostname = 'localhost';
$username = 'YOUR-DB-USERNAME';
$password = 'YOUR-DB-PASSWORD';
$database = 'YOUR-DB-TABLE';
$collation = 'utf8mb4_unicode_ci';
$tableCount = 0;

$wpconfig = getWPConfig();

if ( is_array( $wpconfig ) ) {
	echo "<p>Found WordPress configuration file. Using stored database details.</p>";
	$username = $wpconfig['user'];
	$password = $wpconfig['pass'];
	$database = $wpconfig['name'];
}

if( $username === 'YOUR-DB-USERNAME' || $password === 'YOUR-DB-PASSWORD' || $database === 'YOUR-DB-TABLE' ) {
	echo "<p>ERROR: You need to specify the Database Username & password details</p>";
} else {
	$db = new mysqli( $hostname, $username, $password, $database );

	if( $db->connect_errno ) {
		echo "<p>ERROR: Cannot connect to the database - " . $db->connect_error . "</p>";
	}
	else {
		$query = 'show tables';
		$result = $db->query( $query );
	
		echo '<p>Altering all tables in ' . $database . ' to ' . $collation .'</p>';
	
		while( $row = $result->fetch_array( MYSQLI_NUM ) ) {
			$alterresult = $db->query( 'ALTER TABLE ' . $row[0] . ' COLLATE ' . $collation );
			if( !$alterresult ) {
				 echo $row[0] . ' - ERROR: NOT ALTERED</br>';
			}
			else {
				 echo $row[0] . ' - Altered</br>';
				$tableCount++;
			}
		}
	
		echo '<p>The collation of your database tables has been successfully changed!</p>';
		echo '<p>' . $tableCount . ' Tables have been altered</p>';
	}	
}

/*
 * Read the WordPress WP-CONFIG.PHP file to get the Database Username & Password details
 * 
 * This function is courtesy of the InterconnectIT Database Search and Replace Script
 * http://interconnectit.com/124/search-and-replace-for-wordpress-databases
 */
function getWPConfig( $filename = 'wp-config.php' ) {

	if ( $filename == 'wp-config.php' ) {
		$filename = dirname( __FILE__ ) . '/' . basename( $filename );

		// look up one directory if config file doesn't exist in current directory
		if ( ! file_exists( $filename ) )
			$filename = dirname( __FILE__ ) . '/../' . basename( $filename );
	}

	if ( file_exists( $filename ) && is_file( $filename ) && is_readable( $filename ) ) {
		$file = @fopen( $filename, 'r' );
		$file_content = fread( $file, filesize( $filename ) );
		@fclose( $file );
	}

	preg_match_all( '/define\s*?\(\s*?([\'"])(DB_NAME|DB_USER|DB_PASSWORD|DB_HOST|DB_CHARSET|DB_COLLATE)\1\s*?,\s*?([\'"])([^\3]*?)\3\s*?\)\s*?;/si', $file_content, $defines );

	if ( ( isset( $defines[ 2 ] ) && ! empty( $defines[ 2 ] ) ) && ( isset( $defines[ 4 ] ) && ! empty( $defines[ 4 ] ) ) ) {
		foreach( $defines[ 2 ] as $key => $define ) {

			switch( $define ) {
				case 'DB_NAME':
					$name = $defines[ 4 ][ $key ];
					break;
				case 'DB_USER':
					$user = $defines[ 4 ][ $key ];
					break;
				case 'DB_PASSWORD':
					$pass = $defines[ 4 ][ $key ];
					break;
				case 'DB_HOST':
					$host = $defines[ 4 ][ $key ];
					break;
				case 'DB_CHARSET':
					$char = $defines[ 4 ][ $key ];
					break;
				case 'DB_COLLATE':
					$coll = $defines[ 4 ][ $key ];
					break;
			}
		}
	}

	return array(
		'host' => $host,
		'name' => $name,
		'user' => $user,
		'pass' => $pass,
		'char' => $char,
		'coll' => $coll
	);
}