asked on

Need help accessing serialized data from MySql WordPress database

Hi,
I am using WordPress and have a member solution installed which allows members to buy memberships. I need to create some PHP which will allow me to basically export the users given the last 7 days, so I can see who has recently joined, however the member data is stored in a serialised array, and I am struggling to work out how to access this so I can export both the user details and the payment details.

I have created something along the lines of:

SELECT
    u.id,
    u.user_registered,
    (select meta_value from mydb_usermeta where user_id = u.id and meta_key = '_mgm_cf_title' limit 1) as title,
    (select meta_value from mydb_usermeta where user_id = u.id and meta_key = 'first_name' limit 1) as FirstName,
    (select meta_value from mydb_usermeta where user_id = u.id and meta_key = 'last_name' limit 1) as LastName,
    (select meta_value from mydb_usermeta where user_id = u.id and meta_key = '_mgm_cf_email' limit 1) as Email,
    (select meta_value from mydb_usermeta where user_id = u.id and meta_key = '_mgm_cf_address_line_1' limit 1) as 'Address1',
    (select meta_value from mydb_usermeta where user_id = u.id and meta_key = '_mgm_cf_address_line_2' limit 1) as address2,
    (select meta_value from mydb_usermeta where user_id = u.id and meta_key = '_mgm_cf_address_line_3' limit 1) as address3,
    (select meta_value from mydb_usermeta where user_id = u.id and meta_key = '_mgm_cf_city' limit 1) as town,
    (select meta_value from mydb_usermeta where user_id = u.id and meta_key = '_mgm_cf_county' limit 1) as county,
    (select meta_value from mydb_usermeta where user_id = u.id and meta_key = '_mgm_cf_zip' limit 1) as postcode,
    (select meta_value from mydb_usermeta where user_id = u.id and meta_key = '_mgm_cf_phone' limit 1) as telephone,
    (select meta_value from mydb_usermeta where user_id = u.id and meta_key = '_mgm_cf_children_s_details_i_e_' limit 1) as childrens_details,
    (select meta_value from mydb_usermeta where user_id = u.id and meta_key = '_mgm_cf_buzzword_pdf' limit 1) as newsbuzz,
    (select meta_value from mydb_usermeta where user_id = u.id and meta_key = '_mgm_cf_bombus_review' limit 1) as newsbom,
    (select meta_value from mydb_usermeta where user_id = u.id and meta_key = '_mgm_cf_monthly_e_newsletter' limit 1) as enewsletter,
    (select meta_value from mydb_usermeta where user_id = u.id and meta_key = '_mgm_cf_hear_about_us' limit 1) as hear_about_us,
    (select meta_value from mydb_usermeta where user_id = u.id and meta_key = '_mgm_cf_gift_aid' limit 1) as gift_aid,
    (select meta_value from mydb_usermeta where user_id = u.id and meta_key = '_mgm_cf_data_protection_options' limit 1) as data_protection,
    (select meta_value from mydb_usermeta where user_id = u.id and meta_key = 'mgm_member_options' limit 1) as member_options
FROM mydb_users u WHERE u.user_registered >= DATE(NOW()) - INTERVAL 7 DAY

Open in new window

this returns all the values for the last 7 days, however the mgm_member_options is a serialised field. Within here is a value for payment type which I need to extract.

Can anybody help me? I'm tearing my hair out with this one....

Thanks

Damian

Chris Stanyon

In PHP, you will need to use the unserialize() method to convert your serialized strings into anything meaningful, such as an array:

<?php
$str = <<< EOT
a:4:{s:8:"userName";s:8:"CStanyon";s:9:"firstName";s:5:"Chris";s:8:"lastName";s:7:"Stanyon";s:3:"uID";i:123;}
EOT;

$data = unserialize($str);

echo $data['userName']; 
echo $data['uID'];

Open in new window

damianb123

ASKER

Hi Chris,
Thanks for your comment. Yes, that makes sense..... I'm struggling most of all on merging the values. In my Mysql query above it exports the user ID and Registered date:

u.id,
u.user_registered

And also several custom fields which all work fine. However the last field is called mgm_member_options, this ism a serialised array, and within this field I need to be able to extract;

Pack_id and Amount

I think it's the loop of it all, combined with extracting from MySql which has thrown me on this....

Can you think of anything?

Appreciate the help.

Damian

ASKER CERTIFIED SOLUTION

Chris Stanyon

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

damianb123

ASKER

This was just what I needed. Thanks a million Chris.