query user display_name failed in a custom mysql query foreach

Question

I met some trouble. I tried to get user display_name in a custom mysql query foreach, but it failed. I have tried three method. new WP_User, get_user_by and get_userdata. but they only can get back the result $user->display_name which is ID=1 in table wp_users(admin). failed get any $user->display_name which ID=2(3,4...) in table wp_users. What should I do to solve this question? many thanks.

$results = mysql_query("SELECT * FROM custom_table");// query connect from a custom database, not in database wp.
while($row = mysql_fetch_array($results)) {
global $wpdb;
$user = new WP_User($row['user_id']);
//$user = get_user_by('id',$row['user_id']);                
//$user = get_userdata($row['user_id']);
var_dump($user);
}

edit

Here is my full code. same code, If SELECT * FROM test_table where id = 1 I could get the user display name admin, but if I try SELECT * FROM test_table where id = 3 I could not get the display name, the back value is empty (I have checked the wp_users table, ID = 3 really has a member.)

header("Content-type: text/html; charset=utf-8"); 
$mysql_hostname = "localhost";
$mysql_user = "root";
$mysql_password = "root";
$mysql_database = "test_db";
$db = mysql_connect($mysql_hostname, $mysql_user, $mysql_password)
or die('Error connection to database server: ' . mysql_error());
mysql_select_db($mysql_database, $db)
or die('Error connection to database server: ' . mysql_error());
mysql_query("SET NAMES utf8");
$results = mysql_query("SELECT * FROM test_table where id = 3");
while($row = mysql_fetch_array($results)) {
    //echo $row['id'];  I can get the $row['id'] value.
    $foo_db = new wpdb('root','root','wp','localhost'); 
    $foo_result = $foo_db->get_results("SELECT * FROM `wp_users` WHERE ID = '".addslashes($row['id'])."'",OBJECT);
    foreach($foo_result as $user){ 
        var_dump($user);
    }
}

BTW: test_table is very simple, just has 1 field, record the user ID. (ID:1 - ID:33)

Answer 1

You should use all of the goodness of the wpdb object.

#Create a new WPDB object for your custom DB
$foo_db = new wpdb('username','password','database','localhost');
$query = ('SELECT * FROM custom_table');

#Use the get_results method - You can return OBJECT(default), OBJECT_K, ARRAY_A, ARRAY_N
$results = $foo_db->get_results($query, OBJECT);
foreach($results as $row){
    $user = new WP_User($row->user_id);
}

You can read more here: http://codex.wordpress.org/Class_Reference/wpdb#SELECT_Generic_Results

To note, if you are selecting from the same server that uses the same credentials, try the following instead so as not to nest another identical connection:

global $wpdb;
$query = 'SELECT * FROM db_name.custom_table';
$results = $wpdb->get_results($query);

Hope this helps you!

Answer 2

try it like this one:

$results = mysql_query("SELECT * FROM custom_table");// all the stuff you did up until here
global $wpdb;
while($row = mysql_fetch_array($results)) {

    $query = "SELECT display_name FROM wp_users WHERE ID = %d";
    $user_display_name = $wpdb->get_var( $wpdb->prepare( $query, $row['user_id'] ) );
    echo $user_display_name . "<br />";

}

this should do it.

notice the prepare function used on the $wpdb object to ensure the safety of the query.