使用SQL SELECT查询返回最少数量的用户

Question

I'm a little new to using SQL and was wondering if someone might be able to help me with this query I'm trying to figure out. I'm developing a Wordpress theme and something I'd like to do in it is generate a random set of users on a page by using a SELECT query. It works great right now, I have it randomly pulling users from the database and displaying them with the right formatting. I can even limit the number of users (using the LIMIT clause) or order them by user properties.

However, my challenge is in getting the query to return at least 4 users (sometimes RAND() function causes only 2 to be displayed, which isn't great for a front-facing page).

I don't want to create a query that will throw an error if there are less than four users (which there will never be, but I'd still want to write clean code).

So, in summary: SELECT query that returns exactly 4 results, randomly ordered and, and doesn't throw an error if there's less than four users.

Any thoughts? I've tried using a few different ways to give RAND() different parameters and looked to see if there's a 'minimum' clause, but I haven't found anything. It's also a bit of a strange query bc most of the time we're more concerned with upper than lower limits.

Thanks everyone!

Here's the code I have in a custom.php file (I'm using the Roots starter theme):

function wpb_random_users() {

global $wpdb;


// Query database for users, change the number of users displayed by changing the limit
$usernames = $wpdb->get_results("SELECT user_nicename, user_url, user_email FROM $wpdb->users ORDER BY RAND() LIMIT 4 ");

// Display users in a list
foreach ($usernames as $username) {

if (!$username->user_url) :
$randomusers .= '<div class="col-md-3">'.'<img class="img-circle img-responsive" src="'.get_wp_user_avatar_src($username->user_email, 'thumbnail').'" alt="" />'.'<h4>'.$username->user_nicename.'</h4>'.'</div>';

endif;
}

return $randomusers;
}

add_shortcode('randomusers','wpb_random_users');

Answer 1

The SQL query is not the reason you're getting less than 4 results... You should get 4 every time, provided there's at least 4 rows in the table.

I suggest you debug this line: if (!$username->user_url) :. You are displaying only users with no URL. So, if you get 4 users back from the SQL query, but only two of them lack a URL, you only get 2 users.

Incidentally, you don't need to seed the rand() function for what you're doing. In fact, it's less desirable. Just use SELECT user_nicename, user_url, user_email FROM $wpdb->users ORDER BY RAND() LIMIT 4 ");