Different database query in function.php using mysqli

Question

Since version 3.9 (that will be released soon, actually in beta 2) WordPress will use mysqli to connect to database (3.9- versions use mysql) so you can actually use new wpdb instances and helper function to connect externa values from WordPress.

What I suggest is to configure external database using constants in wp-config.php in this way you are sure that thery are available in WordPress: you have to duplicate your config, but I don’t think it’s really an issue.

So in wp-config.php, before the line

/* That's all, stop editing! Happy blogging. */

write you external db config in constants:

define( 'MY_EXT_DB_HOST', 'xxx.xxx.xxx.xxx' );
define( 'MY_EXT_DB_USER', 'my-dummy-user' );
define( 'MY_EXT_DB_PWD', 'my-dummy-password' );
define( 'MY_EXT_DB_NAME', 'my-dummy-db-name' );
define( 'MY_EXT_DB_PREFIX', 'mydummyprefix_' );

After that, in functions.php write a function that initialize and return a new wpdb instance

function get_my_extenal_db() {
  static $myextdb;
  if ( is_null( $myextdb ) ) {
    // new wpdb instance
    $myextdb = new wpdb( MY_EXT_DB_USER, MY_EXT_DB_PWD, MY_EXT_DB_NAME, MY_EXT_DB_HOST );
    // set prefix
    $myextdb->set_prefix( MY_EXT_DB_PREFIX, FALSE );
    // set table names:
    // 1. add all unprefixed table names to an array
    $tables = array( 'users', 'profile_fields_data', 'user_group' ); 
    // 2. replace standard tables
    $myextdb->tables = $tables;
    $myextdb->global_tables = array();
    $myextdb->ms_global_tables = array();
    // 3. set your tables
    foreach ( $tables as $table ) {
      $myextdb->$table = MY_EXT_DB_PREFIX . $table;
    }
    // optional, set DB collate and chartset
    $myextdb->collate="utf8_general_ci";
    $myextdb->charset="utf8";
  }
  return $myextdb;
}

Now you are ready to use your external db in WordPress way.

// get db instance
$mydb = get_my_extenal_db();

// prepare query
$query = $mydb->prepare(
   "SELECT * FROM {$mydb->users} u 
   LEFT JOIN {$mydb->profile_fields_data} f ON (u.user_id = f.user_id)
   LEFT JOIN {$mydb->user_group} g ON (u.user_id = g.user_id)
   WHERE g.group_id = %d", 9
);

// get results
$results = $mydb->get_results( $query );

After that you can just use data retrieved. By default wpdb::get_results() return an array of objects, where every row is an object whose properties are the values for row columns:

// do stuff with data
if ( ! empty( $results ) ) {
   foreach ( $results as $row ) {
     if( $row->pf_xml_arma_id != "" ){
        $data .= '<member id="' . $row->pf_xml_arma_id;
        $data .= '" nick="' . $row->pf_xml_player_name . '">';
        $name  = $row->pf_xml_name === '' ? "N/A" : $row->pf_xml_name;
        $email = $row->pf_xml_email === '' ? "N/A" : $row->pf_xml_email;
        $steam = $row->pf_xml_steam_user === ''
                 ? "N/A"
                 : "Steam: {$row->pf_xml_steam_user}";
        $quote = $row->pf_xml_remark === '' ? "N/A" : $row->pf_xml_remark;
        $data  .= "<name>{$name}</name>";
        $data  .= "<email>{$email}</email>";
        $data  .= "<icq>{$steam}</icq>";
        $data  .= "<remark>{$quote}</remark>";
        $data  .= "</member>";
     }
   }
 }

If you need to query db different times, just call $mydb = get_my_extenal_db() everytime you need: the connection will be done and setup only once.

You can use this code in WordPress versions prior to 3.9 and when you’ll update to 3.9 it will work without any intervention. But in 3.9 it will use mysqli, in previous version mysql (that will cause deprecated notices using PHP 5.5+).

Related Posts: