1

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?

  • Why not use the wpdb functions instead of creating a separate connection? – Kumar Mar 21 '14 at 12:51
  • Because I am connecting to a different database. And from what I know wpdb connects to the wp database. – madmanali93 Mar 21 '14 at 13:09
  • what are the host name and other variable values you are using, also can you paste the variables in wp-config.php ? My point is to check them if you are using a proper hostname and port and all – Kumar Mar 21 '14 at 13:32
  • Yes they are proper. It is in the file of my config for the forums. It is working fine when I use a separate php page like test.php so I am confident the script works and it is getting the correct information. But when I place it in a function in wordpress I get the error. I don't know if wordpress does not allow. – madmanali93 Mar 21 '14 at 13:42
  • You can create new wpdb instances – s_ha_dum Mar 21 '14 at 13:55
4

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

  • Thank you. This helps a lot. There is a bug somewhere though because the site stopped functioning. I'll try to find it though and report back when its fully functional. – madmanali93 Mar 21 '14 at 16:59
  • It fails at this line if ( ! empty( $results ) ) – madmanali93 Mar 21 '14 at 17:05
  • My apologies initially I had something wrong in the config file. But now that's fixed the code stops at the foreach loop. I get a white page. The var dump gives out "Arrayarray(0) { }" and "Arraystring(160) "SELECT * FROM phpbb_users u LEFT JOIN f ON (u.user_id = f.user_id) LEFT JOIN phpbb_user_group g ON (u.user_id = g.user_id) WHERE g.group_id = 9"", respectively. – madmanali93 Mar 21 '14 at 17:23
  • I just checked the database. It is set correct as user, user_group, profile_fields_data. – madmanali93 Mar 21 '14 at 17:49
  • Ok just fixed the query. Now the there is information in the var_dump but it still turn to a black screen on the foreach loop. – madmanali93 Mar 21 '14 at 17:54
0

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.

  • I am connecting to a different database from what wp is connected on. I run this script on a separate php file like test.php and it works. But with wordpress it just doesn't – madmanali93 Mar 21 '14 at 13:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.