CODEWITHSUNDEEP
mysqlwordpressserialization
Ali
Ali

Reputation: 5111

Wordpress Meta Value (Serialized) Filter

I have a meta key _student_registration_data stored in wordpress database having meta value as a serialized array like below:

a:9:{s:4:"name";s:20:"John Doe";s:11:"father_name";s:10:"Arnold";s:4:"cnic";s:13:"5540545612812";s:12:"home_address";s:9:"Scheme-33";s:16:"telephone_number";s:12:"923332654324";s:15:"registration_id";s:6:"08CS68";s:10:"program_id";s:1:"1";s:9:"cohort_id";s:1:"6";s:10:"section_id";s:1:"7";}

Now I want a mysql query or some builtin wordpress function with the help of which I can filter usermeta row having registration_id equal to 08CS68.

Thanks.

Upvotes: 0

Views: 383

Answers (2)

Ali
Ali

Reputation: 5111

Nick's answer was the key, but it didn't solve my problem. Here's what actually worked:

SELECT * FROM (SELECT * FROM `cn_usermeta` WHERE meta_key = '_student_registration_data') as META WHERE SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(META.meta_value, 'registration_id', -1), ';', 2), ':', -1) = '"08CS68"'

Special thanks to Nick :)

Upvotes: 0

Nick
Nick

Reputation: 147146

You can extract the registration_id from your string using SUBSTRING_INDEX:

SELECT *
FROM yourtable
WHERE SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(_student_registration_data,
                    'registration_id', -1), ';', 2), ':', -1) = '"08CS68"'

Upvotes: 1

Related Questions