How do I query WordPress’s MySQL for a list of users who have a certain meta_value and a day-old registration date?

4 Views

I’m trying to search WordPress’s database for a list of users who 1) Have a certain capability, and 2) Registered more than 24 hours ago.

For part 1, I wrote this query to single out a certain type of user.

Read More
$querystr ="SELECT user_id, meta_value FROM wp_usermeta WHERE meta_value LIKE '%s2member_level5%'";

Then, for part 2, I started writing this query to check for WordPress user accounts that were day-old or older. (I don’t think I’m doing it right.) 

$agingstr ="SELECT ID, user_registered FROM wp_users WHERE datediff(now(),`user_registered`)>=1";

My goal is to combine the two, using the user_id key, and providing us with a list that satisfies both criteria.

The desired list would take the form of user ID keys that meet both criteria in both tables.

Any help much appreciated.

(I know it’s an unfortunate EAV pattern but we’re stuck with it because of the nature of WordPress.)

Post Views: 4

Related posts

Leave a Reply

1 comment

  1. Query to select users that have been registered for more than 24 hours:

    SELECT user_id, user_registered
FROM   wp_users
WHERE  user_registered < NOW() - INTERVAL 24 HOUR

    To combine the queries:

    SELECT user_id, wp_usermeta.meta_value, wp_users.user_registered
FROM   wp_usermeta JOIN wp_users USING (user_id)
WHERE  wp_usermeta.meta_value LIKE '%s2member_level5%'
   AND wp_users.user_registered < NOW() - INTERVAL 24 HOUR