Only displaying part of a returned MySQL Query?

Question

I am trying to filter out results of a MySQL query. I am pulling data from a Wordpress database. Specifically, I am trying to pull just the user role.

In Wordpress, the returned value is something like this:

a:1:{s:13:"administrator";s:1:"1";}

All I want to see returned is the "administrator", "author", "subscriber", etc, and not all the other information.

From my understanding (I am very much a beginner however), I can't use a substr() because the length of characters is different, and the information before the role varies as well.

Is there anyway to filter out all the other information and just return "administrator" in the MySQL query directly?

Here is my current query (I am also pulling names, email address, passwords, etc from another table):

SELECT wp_users.ID, wp_users.user_email, wp_users.user_pass, wp_users.display_name, wp_usermeta.user_id, wp_usermeta.meta_value
FROM wp_users 
JOIN wp_usermeta 
ON wp_users.ID = wp_usermeta.user_id
WHERE (wp_usermeta.meta_value LIKE '%admin%') 
OR (wp_usermeta.meta_value LIKE '%author%') 
OR (wp_usermeta.meta_value LIKE '%subscriber%')

SELECT wp_users.display_name FROM wp_users JOIN wp_usermeta ON wp_users.ID = wp_usermeta.user_id WHERE (wp_usermeta.meta_value LIKE '%admin%') OR (wp_usermeta.meta_value LIKE '%author%') OR (wp_usermeta.meta_value LIKE '%subscriber%') — Death-is-the-real-truth
– Death-is-the-real-truth, Commented Feb 16, 2016 at 18:34
Why do people insist on placing large blocks of code in comments? They're all but unreadable. — Jay Blanchard
– Jay Blanchard, Commented Feb 16, 2016 at 18:35
apologies for the block of code, and thanks for the fix. This was my first post, will keep that in mind going forward. — Dustin Mansfield
– Dustin Mansfield, Commented Feb 16, 2016 at 18:48

mvorisek · Accepted Answer · 2016-02-16 18:56:26Z

1

The string is PHP serialized (serialized via serialize() PHP function).

At first you need it deserialize:

$dbValue = 'a:1:{s:13:"administrator";s:1:"1";}';
$arr = unserialize($dbValue)

print_r($arr);

Which outputs:

array(1) {
  ["administrator"]=>
  string(1) "1"
}

If you are sure, that the role/user group will be always first, you can get it like.

$role = array_keys($arr)[0];

Did I solve your problem?

edited Feb 16, 2016 at 18:56

answered Feb 16, 2016 at 18:35

mvorisek

3,4584 gold badges21 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Dustin Mansfield Over a year ago

I think this sets me in the right direction. I was looking at it backwards, which explains why I couldn't find an answer. I'll research PHP serialization a bit more, but so far its looking right.

C3roe · Accepted Answer · 2016-02-16 18:35:25Z

1

That’s PHP’s serialization data format, so you can simply use unserialize to get it parsed back into a usable data structure.

(And if you are not totally sure what that data structure contains, then use var_dump to make a debug output of it.)

answered Feb 16, 2016 at 18:35

C3roe

97.7k15 gold badges100 silver badges172 bronze badges

Collectives™ on Stack Overflow

Only displaying part of a returned MySQL Query?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related