Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

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)

share|improve this question
2  
You really need to look at the wpdb documentation. codex.wordpress.org/Class_Reference/wpdb –  Brian Fegter Jul 25 '12 at 10:11
    
@Brian Fegter, so do you know how to do it? I have even tried $user = $wpdb->get_row($wpdb->prepare("SELECT * FROM wp_users WHERE ID = '".addslashes($row['id'])."'")); instead of $user = new WP_User($row['user_id']);, still failed. –  yuli chika Jul 26 '12 at 12:30

2 Answers 2

up vote 2 down vote accepted
+50

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!

share|improve this answer
    
I have tried your method, sitll failed, check my edit in question part. I have update a minium code for test. thanks. –  yuli chika Jul 27 '12 at 7:23

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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