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');
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 ");
You need to read the rand() function description. You don't need to pass argument to the function just do:
$usernames = $wpdb->get_results("SELECT `user_nicename`, `user_url`, `user_email` FROM $wpdb->users ORDER BY RAND() LIMIT 4");
Remember that rand() function isn't effective so if you have large amount of data there are better solutions.
Rand() function is invoked for every row in table.
If you have at least 4 users the query will return them. You can debug to see with following code before foreach
print_r($usernames);
To get a random ordering in SQL:
select a, b, c
from(
select a, b, c, rand() OrderBy
from tablename
)
order by OrderBy;
That will give you a table dump in random order. You want only up to 4 rows. There are two places you can put the limit and the results will be different depending on where you put it.
select a, b, c
from(
select a, b, c, rand() OrderBy
from tablename
limit 4
)
order by OrderBy;
That will give you the same 4 rows, just shuffled around.
select a, b, c
from(
select a, b, c, rand() OrderBy
from tablename
)
order by OrderBy
limit 4;
That will give you 4 random rows from within the entire table. This seems to be what you want but it's still not very efficient as it also has to scan the entire table. A compromise would be to limit the inline view to a reasonable set that provides at least 4 rows but not so many more as to be terribly inefficient. Say the inline view generates 40 rows. That means the following will pull 4 random rows from the same set of 40. That is probably close enough for your needs.
select a, b, c
from(
select a, b, c, rand() OrderBy
from tablename
where some-limiting-factor -- generates about 40 rows
)
order by OrderBy
limit 4;