[Plugin: ELI's Custom SQL Report Admin with shortcode] How to unserialize record with your plug

Great topic!

I personally have always done this through tediously manipulating the query with complex combinations of SQL String Functions to parse out the part of the serialized data I want. Nothing quite comes close to this tactic for me because I can then use the results in this field to further filter or sort the query. Any PHP solution negates the ability to use the true value you are looking for to sort the query or use the value as a filter against other data.

I’m sorry if this initial answer seems vague or incomplete but I take every circumstance as a unique challenge and have learned a lot of different ways to solve the same problem, each one with their own pros and cons.

If you want to post an example of your query, and an example of your data, as well as what you are trying to get out of it I would be willing to give you an SQL statement that should do the trick.

Aloha, Eli

Hi Eli,

Below are my query and an example of my data:

Query:
SELECT data
FROM wp_visual_form_builder_entries
WHERE entries_id=2

Data:
a:7:{i:0;a:7:{s:2:”id”;s:2:”15″;s:4:”slug”;s:8:”fieldset”;s:4:”name”;s:8:”Fieldset”;s:4:”type”;s:8:”fieldset”;s:7:”options”;s:0:””;s:9:”parent_id”;s:1:”0″;s:5:”value”;s:0:””;}i:1;a:7:{s:2:”id”;s:2:”19″;s:4:”slug”;s:5:”title”;s:4:”name”;s:5:”Title”;s:4:”type”;s:4:”text”;s:7:”options”;s:0:””;s:9:”parent_id”;s:1:”0″;s:5:”value”;s:9:”Project 1″;}i:2;a:7:{s:2:”id”;s:2:”21″;s:4:”slug”;s:6:”period”;s:4:”name”;s:6:”Period”;s:4:”type”;s:4:”date”;s:7:”options”;s:0:””;s:9:”parent_id”;s:1:”0″;s:5:”value”;s:10:”10/10/2012″;}i:3;a:7:{s:2:”id”;s:2:”23″;s:4:”slug”;s:6:”remark”;s:4:”name”;s:6:”Remark”;s:4:”type”;s:8:”textarea”;s:7:”options”;s:0:””;s:9:”parent_id”;s:1:”0″;s:5:”value”;s:7:”testing”;}i:4;a:7:{s:2:”id”;s:2:”16″;s:4:”slug”;s:12:”verification”;s:4:”name”;s:12:”Verification”;s:4:”type”;s:12:”verification”;s:7:”options”;s:0:””;s:9:”parent_id”;s:1:”0″;s:5:”value”;s:0:””;}i:5;a:7:{s:2:”id”;s:2:”17″;s:4:”slug”;s:53:”please-enter-any-two-digits-with-no-spaces-example-12″;s:4:”name”;s:56:”Please enter any two digits with no spaces (Example: 12)”;s:4:”type”;s:6:”secret”;s:7:”options”;s:0:””;s:9:”parent_id”;s:2:”16″;s:5:”value”;s:2:”14″;}i:6;a:7:{s:2:”id”;s:2:”18″;s:4:”slug”;s:6:”submit”;s:4:”name”;s:6:”Submit”;s:4:”type”;s:6:”submit”;s:7:”options”;s:0:””;s:9:”parent_id”;s:2:”16″;s:5:”value”;s:0:””;}}

The data provided here is just a serialize record. What i want to do is to unserialize the record and extract out some data from this 2 dimensional array.

Kindly assist.

Thank you.

So I’m still not sure exactly what data you want out of this but here is an example of how to get the Title:

SELECT substring_index(substring_index(substring_index(data, 's:4:"name";s:5:"Title";', 1), '";}', 1), '"', -1) AS TitleData
FROM wp_visual_form_builder_entries
WHERE entries_id=2

This is a simple example that assumes that the Value of the Title is always the last element in the sub-array and that the Value itself does not contain double-quotes (“). It could be made to handle different variation of this array data but this should do the trick with the example you’ve given.

Let me know if that’s not what you are trying to do of if you need help adapting this example to the actual data you are trying to retrieve.

Aloha, Eli

Ooops…
On second thought I don’t think that first substring_index will catch the right data. Try this instead:

SELECT
substring_index(
substring_index(
substring_index(
substring_index(
data
, 's:4:"name";s:5:"Title";', -1)
, '";}', 1)
, '"', -1) AS TitleData
FROM wp_visual_form_builder_entries
WHERE entries_id=2

Ooops again… LOL
Too many substring_index.
Try this instead:

SELECT substring_index(substring_index(substring_index(data, 's:4:"name";s:5:"Title";', -1), '";}', 1), '"', -1) AS TitleData
FROM wp_visual_form_builder_entries
WHERE entries_id=2

Hi Eli,

Thank you for your prompt reply.
But what i try to achieve is something as below:

Assume that the $serialize_data is a record
(Example –> a:7:{i:0;a:7:{s:2:”id”;s:2:”15″;s:4:”slug”;s:8:”fieldset”;s:4:”name”;s:8:”Fieldset”;s:4:”type”;s:8:”fieldset”;s:7:”options”;s:0:””;s:9:”parent_id”;s:1:”0″;s:5:”value”;s:0:””;}i:1;a:7:{s:2:”id”;s:2:”19″;s:4:”slug”;s:5:”title”;s:4:”name”;s:5:”Title”;s:4:”type”;s:4:”text”;s:7:”options”;s:0:””;s:9:”parent_id”;s:1:”0″;s:5:”value”;s:9:”Project 1″;}i:2;a:7:{s:2:”id”;s:2:”21″;s:4:”slug”;s:6:”period”;s:4:”name”;s:6:”Period”;s:4:”type”;s:4:”date”;s:7:”options”;s:0:””;s:9:”parent_id”;s:1:”0″;s:5:”value”;s:10:”10/10/2012″;})

i got from my sql statement.

i want to unserialize the data using your plugin. In other word, i want to do something as below php coding.
<?php
// Unserialize the data
$var1 = unserialize($serialized_data);
// Show the unserialized data;
var_dump($var1);
?>

Kindly assist.

Thank you.

Ok, I don’t see how the unserialized data is any nicer to look at since it’s an array, but here is the answer you are asking for:

On line 167 on plugins/elisqlreports/index.php you could change:
$report .= '<td>&nbsp;'.$value.'&nbsp;</td>';
to:
$report .= '<td>&nbsp;'.(is_array(maybe_unserialize($value))?var_dump(maybe_unserialize($value)):$value).'&nbsp;</td>';

That will dump the unserialized array if the value happens to be serialized. I am curious how this is helpful to you as I find the data not much easier to read. I never considered working that into my plugin because I always found parsing it on the SQL side more useful.

Let me know how that works for you.

Aloha, Eli

Hi Eli,

Thank you again for your prompt reply. I really appreciated it.
I totally agreed with you on these data is not much easier to read. Therefore, i still need to extract out the element that i required from the sub-array and display it in a table. For example, extract out the element ‘name’ and ‘value’ from the sub-array and display these as heading and content respectively in a table.

Hope you can share with us on how to achieve this objective in your plugin. Thank again for your time.

In that case I would fall back on my old SQL parsing method:

SELECT substring_index(substring_index(substring_index(data, 's:4:"name";s:5:"Title";', -1), '";}', 1), '"', -1) AS TitleData
FROM wp_visual_form_builder_entries
WHERE entries_id=2

I know it would be tedious but you could use this method to get all values:

SELECT substring_index(substring_index(substring_index(data, 's:4:"name";s:8:"Fieldset";', -1), '";}', 1), '"', -1) AS FieldsetData,
substring_index(substring_index(substring_index(data, 's:4:"name";s:5:"Title";', -1), '";}', 1), '"', -1) AS TitleData,
substring_index(substring_index(substring_index(data, 's:4:"name";s:6:"Period";', -1), '";}', 1), '"', -1) AS PeriodData,
substring_index(substring_index(substring_index(data, 's:4:"name";s:6:"Remark";', -1), '";}', 1), '"', -1) AS RemarkData,
substring_index(substring_index(substring_index(data, 's:4:"name";s:12:"Verification";', -1), '";}', 1), '"', -1) AS VerificationData,
substring_index(substring_index(substring_index(data, 's:4:"slug";s:53:"please-enter-any-two-digits-with-no-spaces-example-12";', -1), '";}', 1), '"', -1) AS ExampleData,
substring_index(substring_index(substring_index(data, 's:4:"name";s:6:"Submit";', -1), '";}', 1), '"', -1) AS SubmitData
FROM wp_visual_form_builder_entries
WHERE entries_id=2

Hi Eli,

I guess what you suggest here is the best solution at this moment. But this method required a lot of manual work which doesn’t suit our objective. Therefore, we will look for another way to capture data without serialize it.

I want to emphasize here is that you are really helpful and i want to thank you again for that.