Get Random Author if had post in last month

This query should work. Note that I’ve used the $wpdb->prepare()` function as this will help add to the security of your function there.

$query = $wpdb->prepare("
    SELECT DISTINCT post_author
    FROM $wpdb->posts, $wpdb->usermeta
    WHERE post_type = 'post'
    AND post_status = 'publish'
    
    AND post_date >= %s
    
    AND user_id = <code>post_author</code>
    AND meta_key = 'wp_capabilities'
    AND meta_value LIKE %s", date ('Y-m-d H:i:s', strtotime ('-1 month')), '%'.$role.'%'
);

Thanks so much for helping @catacaustic. Am I correct that in using your code I would have to change $query to $ids so the whole function would look like this.

function get_random_author_wpa91320($role){
    global $wpdb;
    //this will get all users with posts
    $ids = $wpdb->prepare("
    SELECT DISTINCT post_author
    FROM $wpdb->posts, $wpdb->usermeta
    WHERE post_type = 'post'
    AND post_status = 'publish'
    
    AND post_date >= %s
    
    AND user_id = <code>post_author</code>
    AND meta_key = 'wp_capabilities'
    AND meta_value LIKE %s", date ('Y-m-d H:i:s', strtotime ('-1 month')), '%'.$role.'%'
);
    return $ids[rand(0,count($ids) -1)];
}

Maybe… What does that SQL query return? If you can figure that out (hint: phpMyAdmin is your friend here), then you’ll know if it works or not.

OK, when I get home I will try that. In the mean time trying to get your new version of the code to work I broke the old code lol. My luck. This was the function.

function get_random_author_wpa91320($role){
    global $wpdb;
    //this will get all users with posts
    $ids = $wpdb->get_col("
        SELECT DISTINCT post_author
        FROM $wpdb->posts, $wpdb->usermeta
        WHERE post_type = 'post'
        AND post_status = 'publish'
        AND user_id = <code>post_author</code>
        AND meta_key = 'wp_capabilities'
        AND meta_value LIKE '%\"{$role}\"%'
        "
    );
    return $ids[rand(0,count($ids) -1)];
}

And I was calling the function like this

<?php if ( is_single() ) : ?>
<?php $user = get_user_by('id', get_random_author_wpa91320('author') );
      $ad = xprofile_get_field_data( 'AD', $user->ID );
if (!empty($ad)) {
?>
<div class="travel-blog-banner">
<span class="widget img">
<a href="<?php echo xprofile_get_field_data( 'AD', $user->ID ); ?>" target="_blank" rel="friend" rel="noopener"><?php echo xprofile_get_field_data( 'AD', $user->ID ); ?></a>
</span>
</div>
<?php
} else {}
?>
<?php endif; // is_single() ?>

Do you see something I screwed up? Need some fresh eyes I think. I had it working now it suddenly stopped. What would be better though if I could get your version of the code to work. Thanks so much

• This reply was modified 7 years, 9 months ago by mfalk75.

I think it’s something to do with the wordpress role messing things up but not sure

This is one problem: AND user_id = <code>post_author</code>
The forum’s parser replaced backticks with code tags. Backticks are used as quotes in straight mySQL, but single quotes will work too.
AND user_id = 'post_author'

ah, thank you @bcworkz, I think I was staring at it for too long. I made that change and figured it was going to work again but still no luck. Is there anything wrong with this last part perhaps:
AND meta_value LIKE '%\"{$role}\"%'

The LIKE clause is fine. You could test it in a simple query in phpMyAdmin to confirm. It may not do what you want, but the syntax is acceptable. It identifies post authors that have a particular role. Post authors could also be editors and admins, this query would not pick up on that. To query by capability, the options table would need to be involved. Or use the IN operator to allow for multiple roles to be matched.

I wasn’t paying enough attention when I wrote my previous reply, I didn’t recognize that post_author was a column name, so single quoting it would be inappropriate. The quote style has to be backtick or not at all. Since there are no special characters, it does not need to be quoted.

Lets assume the LIKE clause does what you want for now, and you removed the quotes around post_author. If you are still not getting expected results, I’d be mystified. A quick test of your query in my DB yielded the expected results. The only problem I can see is the role vs. capability issue. Asking for distinct authors will not include editors and admins who authored posts. Other than that, your query should work.

Thank you @bcworkz and @catacaustic. It was the post_author part, I added the backticks and my old code is now working again. I tried to do the same thing with the code utilizing 1 month limit provided by @catacaustic. Took out the < code > tags and replaced them with backticks. Didn’t work however. I ran the code in phpMyAdmin but honestly it’s all a little too advanced for me. haha Here was the results.

ERROR: Unknown Punctuation String @ 15
STR: ->
SQL: $query = $wpdb->prepare("
    SELECT DISTINCT post_author
    FROM $wpdb->posts, $wpdb->usermeta
    WHERE post_type = 'post'
    AND post_status = 'publish'
    
    AND post_date >= %s
    

    AND user_id = <code>post_author</code>
    AND meta_key = 'wp_capabilities'
    AND meta_value LIKE %s", date ('Y-m-d H:i:s', strtotime ('-1 month')), '%'.$role.'%'
)

SQL query: Documentation

$query = $wpdb->prepare(" SELECT DISTINCT post_author FROM $wpdb->posts, $wpdb->usermeta WHERE post_type = 'post' AND post_status = 'publish' AND post_date >= %s AND user_id = <code>post_author</code> AND meta_key = 'wp_capabilities' AND meta_value LIKE %s", date ('Y-m-d H:i:s', strtotime ('-1 month')), '%'.$role.'%' )

MySQL said: Documentation

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '$query = $wpdb->prepare("
    SELECT DISTINCT post_author
    FROM $wpdb->post' at line 1 

You cannot put PHP code directly into phpMyAdmin, it only speaks mySQL, not PHP! You need to to do the PHP part for it, resulting in a pure mySQL query. This means no initial PHP, including double quotes. The mySQL query begins with SELECT. You need to place actual values in place of all variables. $wpdb->posts becomes wp_posts if your prefix is the default wp_. %s is replaced by an actual date or role. Include the % wildcards around the role. Don’t forget to remove the post_author code tags!

It also appears the PHP may be missing the terminal double quote, though that may be an artifact of copying from phpMyAdmin.

ah, OK, I’m completely unfamiliar with using mySQL but I did what you said but still couldn’t get it to work properly. Instead I changed it to this and now it seems to be working fine.

function get_random_author_wpa91320($role){
    global $wpdb;
    //this will get all users with posts
    $ids = $wpdb->get_col("
        SELECT DISTINCT post_author
        FROM $wpdb->posts, $wpdb->usermeta
        WHERE post_type = 'post'
        AND post_date > NOW() - INTERVAL 1 MONTH
        AND post_status = 'publish'
        AND user_id = <code>post_author</code>
        AND meta_key = 'wp_capabilities'
        AND meta_value LIKE '%\"{$role}\"%'
        "
    );
    return $ids[rand(0,count($ids) -1)];
}

So basically it’s my original function with the added post_date part. I could not get it to work using prepare instead of get_col for security as mentioned by @catacaustic. My main question is this function secure?

the code part is backticks

Gotchya. It’s curious how the forum’s parser handles nested backticks. Oddly, this thread is the first time I’ve run into it. I’m not sure there’s a workaround. I think using HTML entities may work. As it happens in this case, you don’t really need backticks in the SQL anyway.

The purpose of $wpdb->prepare() is to sanitize variables that may be of suspect origin. $role is the only variable. What is the source of this value? If it is other script with no user input, you do not need to use $wpdb->prepare(). If there is any chance of external influence, direct or indirect, $wpdb->prepare() must be used. It IS a very important method that cannot be lightly dismissed, failing to use it where needed can allow for easy SQL injection attacks that can lead to very bad things happening to your data.

That said, there should be no reason $wpdb->prepare() would not work anyway. If it wasn’t working, there’s an issue with how it was used. But since it’s apparently not necessary, the issue is moot.

Thankyou @bcworkz for all the information. I am defining the variable as author in the code when I call it from my single.php file. So I should be OK then correct? Otherwise, I might just remove the variable and define it as author in the function.

So before this function is called you have something like $role = 'author';? Then yes, that’s fine, no need for $wpdb->prepare() in that case if that is the only condition under which the function is called.

If somewhere else there was get_random_author_wpa91320( $_POST['role']); there would be some serious concern since $_POST elements could contain almost any value coming from a form submit. A bad actor could enter author%; DROP {DB_NAME};" in the form field and without prepare() your entire DB could be erased! I don’t think my example would actually work, only because I’m not a black hat hacker. A real hacker would come up with something that will work.