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
damianb123Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Chris StanyonWebDevCommented:
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

0
damianb123Author Commented:
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
0
Chris StanyonWebDevCommented:
Hey Damian,

The general idea here is that you'll run your query to retrieve the records you want. You then loop through those records, unserializing the data, checking it, extracting it etc, and then you do something with the data (write to file / insert to another db / table etc.

Have a quick look at this;

$dbh = new PDO("mysql:host=$hostname;dbname=$database", $username, $password);
$dbh->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_OBJ);

$sqlString = <<< EOT
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
EOT;

foreach ($dbh->query($sqlString) as $row):
    $myData = unserialize($row->member_options);
    echo $row->title;
    echo $myData['Pack_id'];
    echo $myData['Amount'];
endforeach;

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
damianb123Author Commented:
This was just what I needed.  Thanks a million Chris.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.