Taxonomy fields and terms relations database update

<?php

ini_set('memory_limit','128M');
ini_set('max_execution_time', 0);
set_time_limit(0);


/**
 * Taken from Wordpress Search and Replace DB tool
 *
 * Search through the file name passed for a set of defines used to set up
 * WordPress db access.
 *
 * @param string $filename The file name we need to scan for the defines.
 *
 * @return array    List of db connection details.
 */
function define_find( $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
    );
}

// Connecting to the database. Mysqli is used by WP, so it should work for everyone.

$db_details = define_find(dirname( __FILE__ ) . '/wp-config.php' );

if ( $db_details ) {

    define('DB_NAME', $db_details['name']);
    define('DB_USER', $db_details['user']);
    define('DB_PASSWORD', $db_details['pass']);
    define('DB_HOST', $db_details['host']);
    define('DB_CHARSET', $db_details['char']);
    define('DB_COLLATE', $db_details['coll']);
} else {
    die('Something is wrong with your wp-config.php, cannot connect to the database.');
}

$mysqli = new mysqli(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);



// Beginning of the script itself

/**
 * STEP 1 - Script for copying all ACF TAXONOMY fields from wp_postmeta table into the wp_term_relationships
 * where links between taxonomies and posts belong by default.
 */


/**
 * Following SQL selects IDs of all "acf-field-group" post types which contains at least one "acf-field" of TAXONOMY type
 * including the REPEATER type consisting of taxonomy fields. Those IDs will further be reused in another query.
 * Moreover we store repeaters fields post_name attribute, which we will need later.
 *
 * Note: You will not find any 'acf-field-group' nor 'repeater' string in the query, it is based on a knowledge
 * how ACF plugin stores its data into the database.
 *
 **/

$sql = "SELECT DISTINCT IF(post_type = 'acf-field' AND post_parent > 0, post_parent, ID) AS id, post_type, post_name
        FROM wp_posts
        WHERE ID IN (
            SELECT DISTINCT post_parent
            FROM wp_posts
            WHERE post_type = 'acf-field' AND post_content LIKE '%\"type\";s:8:\"taxonomy\"%'
            )
        ";

$res = $mysqli->query($sql);

$taxonomy_fields_ids = [];
$taxonomy_repeaters = [];

while ($field_group_data = $res->fetch_assoc()) {
    $taxonomy_fields_ids[] = $field_group_data['id'];
    // this condition passes for repeater fields of taxonomy fields only. We need to store their post_name attributes
    // for the later usage
    if ($field_group_data['post_type'] == 'acf-field') {
        $taxonomy_repeaters[] = $field_group_data['post_name'];
    }
}

/**
 * Now we can select all "acf-field-group" type pots using taxonomy fields or repeaters of taxonomy fields.
 * We need them to make the main query faster by finding out (and specifying in the main query) POST TYPES
 * which uses TAXONOMY fields.
 */

$sql = "SELECT ID, post_content, post_type
    FROM wp_posts
    WHERE ID IN ('" . join("','", $taxonomy_fields_ids) . "')";


$res = $mysqli->query($sql);

$post_types = [];
$unexpected = [];

// Storing taxonomy-ACF-fields-relevant post types. $unexpected array should remain empty after the following loop.
// If it is not, something went wrong.

while ($field_group = $res->fetch_assoc()) {
    $field_group_data = unserialize($field_group['post_content']);
    foreach ($field_group_data['location'] as $conditions) {
        foreach ($conditions as $condition) {
            if ($condition['param'] == 'post_type' && $condition['operator'] == '==') {
                $post_types[] = $condition['value'];
            } else {
                $field_group->post_content = $field_group_data;
                $unexpected[] = $field_group;
            }
        }
    }
}

if (count($unexpected) > 0) {
    echo 'Error while handling following posts: ' . json_encode($unexpected) . '<br><br>';
}


$post_types = array_values(array_unique($post_types));

if (count($post_types)) {

    // Fetch POST_NAME attributes of all TAXONOMY acf-fields. This will be important for building of the main query
    // to make it faster
    $res = $mysqli->query("SELECT * FROM `wp_posts` WHERE `post_type` LIKE 'acf-field' AND post_content LIKE '%;s:8:\"taxonomy\"%'");
    $taxonomy_fields = [];

    while ($field = $res->fetch_assoc()) {
        $taxonomy_fields[] = $field['post_name'];
    }

    // Adding post_names of the repeater fields selected above into the array of taxonomy fields' post names
    $taxonomy_fields = array_merge($taxonomy_fields, $taxonomy_repeaters);

    /**
     * Finally building the main query.
     * This selects all ACF's assignments of taxonomies to posts.
     * Based on this data we can then make inserts into wp_term_relationships table and make ACF-generated links
     * between posts and taxonomies "Wordpress-correct".
     */
    $sql = "SELECT DISTINCT pm.meta_id, pm.post_id, pm.meta_key, pm.meta_value, p.post_type
        FROM wp_postmeta pm
        JOIN wp_postmeta pm2 ON pm2.meta_key = CONCAT('_', pm.meta_key)
        JOIN wp_posts p ON p.ID = pm.post_id
        WHERE p.post_type IN ('" . join("','", $post_types) . "')
        AND pm2.meta_value IN ('" . join("','", $taxonomy_fields) . "')";

    $result = $mysqli->query($sql);

    while ($row = $result->fetch_assoc()) {

        // META_VALUE column can be empty, numeric or array. Let's decide what to do with it based on that.
        if ($row['meta_value']) {
            if (!is_numeric($row['meta_value'])) {
                $data = unserialize($row['meta_value']);
                if (is_array($data)) {
                    $row['meta_value'] = $data;
                } else {
                    continue;
                }
            }
        } else {
            continue;
        }

        /**
         * Building the inserts
         */
        if (is_array($row['meta_value'])) {
            if (count($row['meta_value']) == 1) {
                $sql = sprintf("INSERT IGNORE INTO wp_term_relationships (object_id, term_taxonomy_id) VALUES ('%u','%u')", $row['post_id'], $row['meta_value'][0]);
            } else {
                $sql = "INSERT IGNORE INTO wp_term_relationships (object_id, term_taxonomy_id) VALUES ";
                foreach ($row['meta_value'] as $terms_id) {
                    $sql .= sprintf("('%u','%u'), ", $row['post_id'], $terms_id);
                }
                $sql = substr($sql, 0, -2);
            }
        } else {
            // For numeric meta_value field
            $sql = sprintf("INSERT IGNORE INTO wp_term_relationships (object_id, term_taxonomy_id) VALUES ('%u','%u')", $row['post_id'], $row['meta_value']);
        }

        //echo $sql . '<br>';

        $mysqli->query($sql);

    }

    echo "WP_TERM_RELATIONSHIPS table has been updated. <br>";

}

/**
 * STEP 2 - Make all TAXONOMY ACF fields updating the terms in wp_term_relationships table on their save
 */

$sql = "UPDATE wp_posts
SET field = REPLACE(post_content, '\"save_terms\";i:0;', '\"save_terms\";i:1;')
WHERE post_type = 'acf-field' AND post_content LIKE '%\"type\";s:8:\"taxonomy\"%' ;";

$mysqli->query($sql);

echo "All taxonomy ACF fields has been updated to SAVE their changes into wp_term_relationships table. <br>";

/**
 * STEP 3 - Make all TAXONOMY ACF fields EXCLUDING THOSE FROM REPEATERS (keeping on mind an ACF issue reported here
 * https://support.advancedcustomfields.com/forums/topic/taxonomy-inside-repeater-field/
 * ) LOADING terms from wp_term_relationships table rather than from posts metadata into the admin area fields.
 **/

$sql = "UPDATE wp_posts p1
JOIN wp_posts p2 ON p2.ID = p1.post_parent
SET p1.field = REPLACE(p1.post_content, '\"load_terms\";i:0;', '\"load_terms\";i:1;')
WHERE p1.post_type = 'acf-field'
AND p2.post_type = 'acf-field-group';
AND p1.post_content LIKE '%\"type\";s:8:\"taxonomy\"%'";

$mysqli->query($sql);

echo "All taxonomy ACF fields except of those from repeaters has been updated to LOAD their initial values from wp_term_relationships table. <br>";