WordPress User Info Query

perpetualshaun

So I wrote this query for a high_school alumni site I'm working on:

SELECT
	u.ID, u.user_login,
	m1.meta_value AS alumni_first_name,
	m2.meta_value AS alumni_last_name,
	m3.meta_value AS alumni_class,
	m4.meta_value AS wp_capabilities
FROM
	wp_users AS u
JOIN
	wp_usermeta AS m1 ON (m1.meta_key='first_name' AND m1.user_id=u.ID)
JOIN
	wp_usermeta AS m2 ON (m2.meta_key='last_name' AND m2.user_id=u.ID)
JOIN
	wp_usermeta AS m3 ON (m3.meta_key='alumni_graduation_year' AND m3.user_id=u.ID)
JOIN
	wp_usermeta AS m4 ON (m4.meta_key='wp_capabilities' AND m4.user_id=u.ID)
WHERE
	m4.wp_capabilities='a:1:{s:6:"alumni";s:1:"1";}'

With that WHERE on the end, it doesn't work. Just trying to run this through phpMyAdmin at the moment, but eventually I'll need to have it working on the site and I'll need to add some searches and resort columns to what's on the screen. At the moment, I ccan't resort the results I get from the above query in phpMyAdmin either. Can anyone see what I'm missing here?

Thanks,
Shaun

bradgrafelman

If you eliminate the WHERE condition, do you get results? If so, can you find a result that contains that exact string in the given column?

(Side note: Does WordPress seriously store serialized data in a SQL DB?!?! Wow... not that I needed any more reasons to never use WP, but that's definitely a (very thick) layer of icing on the proverbial cake. Yikes.)

Bonesnap

perpetualshaun;11018379 wrote:
With that WHERE on the end, it doesn't work.

That's because wp_capabilities is not a column in the usermeta table. You have the usermeta table aliased as m4, and then have m4.wp_capabilities, which simply does not exist.

Instead of this query, get all the users with the role "alumni" and then use the get_userdata() function to get a WP_User object, which has all their usermeta information. If you need data, rule of thumb is the WordPress API can get it for you.

bradgrafelman;11018429 wrote:
(Side note: Does WordPress seriously store serialized data in a SQL DB?!?! Wow... not that I needed any more reasons to never use WP, but that's definitely a (very thick) layer of icing on the proverbial cake. Yikes.)

For some things, yes, but they're mostly used by the CMS itself and not intended to be accessed by the user or the developer, but usually can be if required by using the API. What the OP is trying to do shouldn't be done through SQL commands.