Get/Update data from a separate database - Wordpress

get-update-data-from-separate-db-wordpress.php

<?php
// Create global variable for another db
add_action('init', 'get_certain_db');
function get_certain_db() {
  global $another_db;
  $another_db = new wpdb(USERNAME, PASSWORD, DATABASE_NAME, HOSTNAME);
}

// Example #1 Get taxonomies from another db
function listing_cat_list() {
    global $another_db;
    $another_db_rows = $another_db->get_results("SELECT * FROM `wp_term_taxonomy` WHERE taxonomy = 'another_db_cat'");
    
    $cat_arr = array();
    foreach($another_db_rows as $key => $term) {
        $cat_data = $another_db->get_results("SELECT * FROM `wp_terms` WHERE term_id = $term->term_id");
        foreach($cat_data as $data ) {
             $cat_arr[$data->slug] = $data->name;
        }
    }
    return $cat_arr;
}
// Display data --> print_r(listing_cat_list())

// Example #2 Create post on another db
function create_post_on_another_db() {
    global $another_db;
    $post_args = array(
              'post_type'     => 'post',
              'post_title'    => 'title_here',
              'post_content'  => '',
              'post_status'   => 'publish',
              'post_author'   => 1 // Admin
    );
    $another_db->insert( 'wp_posts', $listing_args);
}

// Example #3 update post meta on another db
// check meta_key if already exists
$meta_exist = array();
$meta_exist = $another_db->get_row( "SELECT * FROM wp_postmeta WHERE meta_key = 'your_meta_key'" );
$post_meta_args = 
              array(
                  'post_id'     => 'your_post_id',
                  'meta_key'    => 'your_meta_key',
                  'meta_value'  => 'your_value'
              );
if(!empty($meta_exist)) {
  $another_db->update( 'wp_postmeta', array('meta_value'  => 'your_value', array('meta_key'  => 'your_meta_key' ));
} else {
  $another_db->insert( 'wp_postmeta', $post_meta_args);
}

// Example #4 update post terms on another db
$post_cat_args =
      array(
          'object_id'         => 'post_id_here',
          'term_taxonomy_id'  => 'cat_id_here',
          'term_order'        => 0
      );     
                      
  // Check if it has aleady a taxonomy
  $has_taxonomy = $another_db->get_row( "SELECT * FROM wp_term_relationships WHERE object_id = post_id_here" );
  if($has_taxonomy) {
    // get taxonomy using taxonomy name
    $taxonomy_list_data = $another_db->get_results("SELECT * FROM wp_term_taxonomy WHERE taxonomy = 'taxonomy_name_here'");
    $taxonomy_list = array();
    foreach ($taxonomy_list_data as $key) {
        $taxonomy_list[] = $key->term_id;
    }
  // get all taxonomy of your post
    $post_taxonomy_list = array();
    $$post_taxonomy_list_data = $dmpdb->get_results("SELECT * FROM wp_term_relationships WHERE object_id = 'post_id_here'");
    foreach ($post_taxonomy_list_data as $key) {
        $post_taxonomy_list[] = $key->term_taxonomy_id;
    }
    foreach ( $taxonomy_list as $key => $value ) {
      // if taxonomy already set, delete them so we can replace them with the new one
        if( in_array( $value, $post_taxonomy_list_data )) {
            $another_db->delete( 'wp_term_relationships', array( 'term_taxonomy_id' => $value ) );
        }
    }
    // then insert the new
    $another_db->insert( 'wp_term_relationships', $post_cat_args );
} else{
    $another_db->insert( 'wp_term_relationships', $post_cat_args );
}