Multiple table query returns nothing in Wordpress

Question

I have a regular wp_users table and sh_users table.

wp_users table is standard one: ID, user_login, user_pass, user_nicename, user_email, user_url, user_registered, user_activation_key, user_status, display_name

sh_users table has: id (auto-increment), user_id (this matches wp_users ID), user_photo (Varchar 255), user_gender (varchar 255)

Now when I do query like this

$results = $wpdb->get_results( "
SELECT user_login, user_email, user_photo
FROM wp_users, sh_users
WHERE wp_users.ID = sh_users.user_id
");

... and this is var dump of that query:

string '
SELECT user_login, user_email, user_photo
FROM wp_users, sh_users
WHERE wp_users.ID = sh_users.user_id
' (length=109)

It returns nothing... + no errors generated what so ever.... so I'm just asking what's the problem there ???

------UPDATE--------

This is dump for wp-users:

INSERT INTO `wp_users` (`ID`, `user_login`, `user_pass`, `user_nicename`, `user_email`, `user_url`, `user_registered`, `user_activation_key`, `user_status`, `display_name`) VALUES 
(1, 'pete', 'xxxxxxx', 'pete', '[email protected]', '', '2013-07-02 16:42:04', '', 0, 'pete'),
(15, 'test', 'xxxxxx', 'test', '[email protected]', '', '2013-07-15 11:17:01', '', 0, 'test'),
(16, 'test1', 'xxxxx', 'test1', '[email protected]', '', '2013-07-15 20:05:32', '', 0, 'test1'),
(17, 'test5', 'xxxxxx', 'test5', '[email protected]', '', '2013-07-16 09:48:57', '', 0, 'test5'),
(23, 'ban', 'xxxxx', 'ban', '[email protected]', '', '2013-07-24 23:24:31', '', 0, 'bane'),
(24, 'ban2', 'xxxx', 'ban2', '[email protected]', '', '2013-07-31 17:41:46', '', 0, 'ban2');

Mysql dump for sh_users:

---- Dumping data for table `sh_users`--
INSERT INTO `sh_users` (`id`, `user_id`, `user_registered`, `user_active`, `user_location`, `user_gender`, `user_photo`, `user_meet`, `user_text`, `user_subscribed`) VALUES 
(5, 15, '1373894222', 'Y', '', '', '17-profile.jpg', '', '', ''), 
(6, 16, '1373925933', 'Y', '', '', '17-profile.jpg', '', '', ''), 
(7, 17, '1373975337', 'Y', 'Midtjylland', 'Hende', '17-profile.jpg', 'Nej', '', 'Y'), 
(13, 23, '1375298672', 'N', 'Midtjylland', 'Hende', '23-profile.jpg', 'Nej', '', 'Y'), 
(14, 24, '1375299707', 'N', '', '', 'no-image.png', '', '', '');

The syntax seems OK, and if there is no error, I assume tables/columns references are valid. Is there really any wp_users and sh_users having the same id? Could you post some sample data on sqlfiddle.com — Sylvain Leroux
could you post the table structure as well (CREATE TABLE...), this is required in order to test. — Sylvain Leroux
@SylvainLeroux There you go fiddles: sqlfiddle.com/#!2/d667b and sqlfiddle.com/#!2/b9f57e — Peter
This actually works as expected, I think: sqlfiddle.com/#!2/abd71/1 :D The problem is probably not with the DB. How do you process the result of the query at application level? — Sylvain Leroux

David Gard David Gard · Accepted Answer · 2013-08-01T10:59:28

You need to tell the query which table to select the results from -

$results = $wpdb->get_results( "
    SELECT wp_users.user_login, wp_users.user_email, sh_users.user_photo
    FROM wp_users, sh_users
    WHERE wp_users.ID = sh_users.user_id
");

This is assuming you want user_photo only from your sh_users table, so change if that is incorrect.

Also, for troubleshooting purposes, the $wpdb global has several useful class variables. Directly after you run your query, add this code -

echo '<pre>Last query: '; print_r($wpdb->last_query); echo '</pre>';
echo '<pre>Last error: '; print_r($wpdb->last_error); echo '</pre>';
echo '<pre>Last result: '; print_r($wpdb->last_result); echo '</pre>';

If $wpdb->last_error returns anything, hopefully it will help you pinpoint the problem.

Multiple table query returns nothing in Wordpress

2 Answers