Different database query in function.php using mysqli

Question

I am trying to connect to a database in the function.php using mysqli. The code seems to run fine when it is not on a separate php file not on wordpress. But when I run it on wordpress I get "Connect failed: No connection could be made because the target machine actively refused it." I am not sure if wordpress is preventing this. So can someone enlighten me on this problem?

Code is provided below if needed.

require_once($_SERVER['DOCUMENT_ROOT'] . '/forum/config.php');
$mysqli = new mysqli($dbhost,$dbuser,$dbpasswd,$dbname);

if ($mysqli->connect_errno)
{
    printf("Connect failed: %s\n", $mysqli->connect_error);
    exit();
}

$data = '<?xml version="1.0"?>
<!-- squad info -->
<squad nick="DC">
<name>Dog Company</name>
<email>n/a@gmail.com</email>
<web> http://www.dog-company.com</web>
<picture>sqd_logo.paa</picture>
<title>Dog Company</title>
<!-- start of member list/info -->
<!-- Updated users and updated to xml on server-->';

$query = 'SELECT *
        FROM '.$table_prefix.'users u 
        LEFT JOIN '.$table_prefix.'profile_fields_data f
        ON (u.user_id = f.user_id)
        LEFT JOIN '.$table_prefix.'user_group g
        ON (u.user_id = g.user_id)
        WHERE g.group_id = 9';

if ($result = $mysqli->query($query)) {

    /* fetch associative array */
    while ($row = $result->fetch_assoc()) {
        if($row['pf_xml_arma_id'] != ""){
            $data .= '<member id="'.$row['pf_xml_arma_id'].'" 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>
            <email>'.$email.'</email>
            <icq>'.$steam.'</icq>
            <remark>'.$quote.'</remark>';
            $data .= '</member>';
        }
    }

    /* free result set */
    $result->free();
}

/* close connection */
$mysqli->close();

$data .= '</squad>'; 

return $data;

Thank you for your time,

UPDATE

Seems like my $query wasn't getting the $table_prefix from my config file. I am not sure why it cannot access it though. Anyone?

Answer 1

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+).

Answer 2

Did you read this: https://codex.wordpress.org/Custom_Queries

I think it will help you understand the basics, then you can take it from there.