MySQL Query to check membership

#1

Can someone give me an example query please which would return the username if they have current membership?
(Outside of wordpress)

User register on WP website, pays via S2member.
They download OpenVPN client
Enter login details which they chose on WP/S2member signup
OpeVPN script checks auth against MySQL database.

I have this working on a standard WP install (no s2member) as I had modified the MySQL table to add new rows (vpn_enabled). Then it was simple query to check username & vpn_enabled = 1 as all of this was on 1 row in wp_users.
but its complicated as wp_usermeta has loads of rows for each user and I guess this is where I check if they have s2membership.

Please can somebody help with this.

$openvpn_user is passed to the script from elsewhere.

Obviously this DOESNT work as this is the result of lots of failed attempts, I am not super great with MySQL.
this is the sort of thing I need please:

$userresult = $wpdb->get_var( "SELECT meta_value FROM wp_usermeta WHERE wp_capabilities LIKE '%s2member_level1%' AND user_login = '$openvpn_user';" );

End result should just return the username…(if they have valid membership).

Or if there’s an easier way…? It would be great if there was a field in wp_users which I could checked against.

Thank you.

#2

Just because you are looking for stuff created by s2Member doesn’t mean it’s really an s2Member issue. In this case, it seems you just need some guidance on constructing a MySQL query.

A quick Google came up with this: http://wordpress.stackexchange.com/questions/30802/sql-select-of-users-by-metadata

#3

Try checking the wp_user_level in usermeta. This will be something above 0 for a Member. Admins are 10.

#4

Thanks. I have 2 users, one is admin and one standard user.

I made the standard user into S2_member _level 1 (via profile edit page) for testing, but the wp_user_level still shows 0.

Is there anything I have to change in S2 options, I think there is something along the lines of “use wordpress roles” but Im unsure exactly what that does.

Thanks

#5

Can’t you use a cascade of “current_user_is(‘role’)” tests, shown in s2M scripting examples?

#6

Unfortunately, that idea isn’t going to work. By default, it’s will be 0 for every s2Member level.

I think you’re thinking of the Force WordPress to use your labels? option in General Options. That only changes labels on the admin pages, so won’t help here.

#7

I know its not an S2member “issue” but after paying £60+ for Pro, it is quite disappointing to have had support fob me off with a “we dont support excel” despite an issue was related to s2member user list export, and “we dont support mysql” etc.

Authing against WP users was easy, even a beginner MySQL person (me) could work it out, well, though I copied this from a guide but it is simple to understand and modify, being on a single line. It did mean adding fields to wp_users but that’s still easy stuff:
$userresult = $wpdb->get_var( “SELECT user_login FROM wp_users WHERE ( (vpn_credit > 0) AND (vpn_enabled = 1) AND (user_login = ‘$openvpn_user’) );” );

Authing against s2member is complicated and I hoped someone might have an easy solution.
that stack exchange answer is a bit complex . I guess I will have to learn about inner join etc unless anyone has an easier solution. Thanks for the link.

I wonder, does s2member have the ability to run a script / command on membership starting and EOT?
Just a simple modify database field wp_users “vpn_enabled” to 1 or 0?

Thanks

#8

@paule3d: I’m not part of WebSharks but, if you paid only 60 GBP for s2Member Pro, you can’t really expect support for anything and everything that might somehow be related. That’s enterprise-level support, for which you would be expected to pay enterprise-level fees. (Some WordPress plugins, indeed, charge hundreds of dollars to answer support tickets.)

You will be much more likely, as I suggested, to get an answer to your question if you Google for it. There’s only a few of us here who answer questions, whereas Google gives you access to the whole internet.

I Google for answers all the time. StackExchange is a great resource, but there are many others, and Google is the key to them all.

#9

I do google for answers, thats how I found a script to check against WP database.
But adding s2member complicates things a lot.

I asked a decent Q on stack, it was specific and tagged for the relevant topics, as questions on there should be.
I got downvoted for it with no explanation!
So I try here.

#10

That’s life! I have found a thick skin to be worth its weight in gold!