Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

Is it possible to unserialize data in the mysql query at the run time? i don't want to use unserialize() function of php.

I need to fetch the value "mathematics" from the below serialized value saved in wp_postmeta table using mysql query.

a:4:{s:39:"wpcf-fields-checkboxes-option-223412915";s:7:"science";s:39:"wpcf-fields-checkboxes-option-499894518";s:10:"technology";s:40:"wpcf-fields-checkboxes-option-1474335177";s:11:"engineering";s:40:"wpcf-fields-checkboxes-option-1018405300";s:11:"mathematics";}

Array
(
[wpcf-fields-checkboxes-option-223412915] => science
[wpcf-fields-checkboxes-option-499894518] => technology
[wpcf-fields-checkboxes-option-1474335177] => engineering
[wpcf-fields-checkboxes-option-1018405300] => mathematics
)
share|improve this question

2 Answers 2

Ideally, post meta should never be stored in a serialized form to begin with, thus making it easy to query. If the code uses the standard methods for storing meta instead of self-serializing it, then each item would be in its own row and easily queryable.

If the data is stored serialized, then you have very few options. SQL is not capable of unserializing the data by itself. You must process it with PHP code.

share|improve this answer
    
Thanks for your suggestion. if PHP is the only option to unserialize the data saved in MySQL after fetching entire data and apply filtration then this may cause overhead to the database and make delay when there are millions of rows in table (wp_postmeta table of wordpress). There must be some other solution to get out of this. While filtering my data i also have to take care of optimization so that query could execute faster. –  neeraj Sep 24 '12 at 10:30

You can use get_post_meta() function for fetching this value. For ex.

<?php
$options = get_post_meta( $post->ID, 'meta_key_name', true ); //replace meta_key_name with the name of your meta_key
$value = $options[ 'wpcf-fields-checkboxes-option-1018405300' ]; //for fetching mathematics value
?>
share|improve this answer
    
Sorry for the confusion. Actually I wanted to filter my result depending upon the filter value like mathematics, science etc. Eventually i am getting all posts with their related categories (from the wp_postmeta table) and after fetching filtered value like "mathematics" in this example from the query string i want to filter the entire posts. So i need to put LIKE '%mathematics%' in the meta_value filed for that post. But since using LIKE operator is not recommended so that's why i wanted to know if this is possible to unserialize the data in the MySQL at the run time –  neeraj Sep 24 '12 at 8:58
    
i don't want to handle this through PHP but MySQL –  neeraj Sep 24 '12 at 8:59

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.