?
Solved

Can I strip a value from a serialized array in a select statement using mySQL?

Posted on 2014-08-03
14
Medium Priority
?
616 Views
Last Modified: 2014-08-19
Hi,

I need to grab some data from a mysql database table in a quick query.  Just something for the owner of the database to get a sum of some values.  

For example, here is the serialized data -
a:19:{s:13:"checkout_data"s:1:"1";s:6:"qtyOne";s:1:"1";s:7:"qtyFive";s:0:"";s:9:"qtyTwenty";s:0:"";s:8:"qtyTotal";s:1:"1";s:12:"total_amount";s:1:"1";}

And I need so sum values from qtyOne, qtyFive, qtyTwenty, qtyTotal, and total_amount for a date range (the date is in it's own field).

Can this be done or do I need to write a script to unserialize all values in the date range and add them up manually?

Thanks!
0
Comment
Question by:lthames
  • 5
  • 5
  • 3
13 Comments
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 40237925
I would do the latter.  I think you'll spend more time trying to figure out how to extract the data with SQL than you will with unserialize()
0
 
LVL 58

Expert Comment

by:Gary
ID: 40237926
Why not just sum the values in the query?
What is the date range to do with the returned data - is this used as part of the query where you are selecting the rows?

You've kinda given us half the story.
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 40237928
Sidebar note about serialize() and unserialize()... I think you might want to use JSON strings going forward.  A minus for serialize(): there are some strange things that happen when you try to put serialized data into a PHP session, and a plus for JSON: It can be consumed by many languages, including JavaScript, making it more of a universally recognized encoding.  And for data transport, JSON is much more compact than XML.
0
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
LVL 111

Expert Comment

by:Ray Paseur
ID: 40237931
Also, it looks like the sample data given here is invalid.  What is a "real" example, please?
0
 

Author Comment

by:lthames
ID: 40237958
I have no control over the data structure, it was already in place.

Regarding the real data, I had removed all of the unneeded fields . .
Here is the actual value - with personal information edited.

a:19:{s:13:"checkout_data";s:1:"1";s:6:"qtyOne";s:1:"1";s:7:"qtyFive";s:0:"";s:9:"qtyTwenty";s:0:"";s:8:"qtyTotal";s:1:"1";s:8:"FullName";s:14:"Ceceria Thomas";s:11:"PhoneNumber";s:12:"903-555-5555";s:5:"Email";s:16:"cece@mydomai.com";s:3:"DoB";s:10:"09/01/1994";s:8:"Address1";s:13:"P.O. Box 1111";s:8:"Address2";s:0:"";s:4:"City";s:10:"Mt. Vernon";s:5:"State";s:2:"VA";s:3:"Zip";s:5:"11457";s:10:"CardNumber";s:16:"xxxxxxxxxxxxxxxx";s:12:"ExpDateMonth";s:2:"00";s:11:"ExpDateYear";s:2:"00";s:3:"CVC";s:3:"000";s:12:"total_amount";s:1:"1";}
0
 
LVL 58

Accepted Solution

by:
Gary earned 1000 total points
ID: 40237962
So just?
<?php

$a=unserialize('a:19:{s:13:"checkout_data";s:1:"1";s:6:"qtyOne";s:1:"1";s:7:"qtyFive";s:0:"";s:9:"qtyTwenty";s:0:"";s:8:"qtyTotal";s:1:"1";s:8:"FullName";s:14:"Ceceria Thomas";s:11:"PhoneNumber";s:12:"903-555-5555";s:5:"Email";s:16:"cece@mydomai.com";s:3:"DoB";s:10:"09/01/1994";s:8:"Address1";s:13:"P.O. Box 1111";s:8:"Address2";s:0:"";s:4:"City";s:10:"Mt. Vernon";s:5:"State";s:2:"VA";s:3:"Zip";s:5:"11457";s:10:"CardNumber";s:16:"xxxxxxxxxxxxxxxx";s:12:"ExpDateMonth";s:2:"00";s:11:"ExpDateYear";s:2:"00";s:3:"CVC";s:3:"000";s:12:"total_amount";s:1:"1";}');

$sum=$a['qtyOne']+$a['qtyFive']+$a['qtyTwenty']+$a['qtyTotal']+$a['total_amount'];

echo $sum;

Open in new window

0
 
LVL 111

Assisted Solution

by:Ray Paseur
Ray Paseur earned 1000 total points
ID: 40237963
Please see http://iconoun.com/demo/temp_lthames.php

<?php // demo/temp_lthames.php
error_reporting(E_ALL);
echo '<pre>';

// SEE http://www.experts-exchange.com/Programming/Languages/Scripting/PHP/Q_28489696.html

// EACH ROW WILL HAVE SOMETHING LIKE THIS
$ser = <<<EOD
a:19:{s:13:"checkout_data";s:1:"1";s:6:"qtyOne";s:1:"1";s:7:"qtyFive";s:0:"";s:9:"qtyTwenty";s:0:"";s:8:"qtyTotal";s:1:"1";s:8:"FullName";s:14:"Ceceria Thomas";s:11:"PhoneNumber";s:12:"903-555-5555";s:5:"Email";s:16:"cece@mydomai.com";s:3:"DoB";s:10:"09/01/1994";s:8:"Address1";s:13:"P.O. Box 1111";s:8:"Address2";s:0:"";s:4:"City";s:10:"Mt. Vernon";s:5:"State";s:2:"VA";s:3:"Zip";s:5:"11457";s:10:"CardNumber";s:16:"xxxxxxxxxxxxxxxx";s:12:"ExpDateMonth";s:2:"00";s:11:"ExpDateYear";s:2:"00";s:3:"CVC";s:3:"000";s:12:"total_amount";s:1:"1";}
EOD;

// RECOVER THE DATA INTO A USEFUL FORMAT
$arr = unserialize($ser);

// SHOW THE DATA IN THE USEFUL FORMAT
print_r($arr);

// TAKE THE SUM OF SOME OF THE ARRAY ELEMENTS
$sum
= $arr['qtyOne']
+ $arr['qtyFive']
+ $arr['qtyTwenty']
+ $arr['qtyTotal']
+ $arr['total_amount']
;
var_dump($sum);

Open in new window

HTH, ~Ray
0
 

Author Comment

by:lthames
ID: 40237992
Ray - yes, that does help . . . although it's not exactly what I wanted (the sum of those 5 values).

But what I need is sums for EACH of those values of all of the records in a date range.  

So for example for Today, Week to date, and Month to date, I need to show the sums of
qtyOne, qtyFive, qtyTwenty, qtyTotal, and total_amount

so I have 5 totals for today,  5 totals for week to date, and 5 totals for month to date.

There are alot of records which is why I wanted to go the sql query route . . . but speed is not a huge factor so I'll go about it that way.,
0
 
LVL 58

Expert Comment

by:Gary
ID: 40237997
You haven't told us what the SQL is but it would be something like this
SELECT ..., ifnull(qtyOne, 0) + ifnull(qtyFive, 0) + ifnull(qtyTwenty, 0) + ifnull(qtyTotal, 0) + ifnull(total_amount, 0) as total

Open in new window

0
 
LVL 58

Expert Comment

by:Gary
ID: 40237999
Or do you mean the sum of the column values not the row values

SELECT ..., SUM(qtyOne), SUM(qtyFive), SUM(qtyTwenty), SUM(qtyTotal), SUM(total_amount)

Open in new window

0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 40238067
Please post the CREATE TABLE statement for the relevant table.  Then we will know the names of the columns and we can show you how to write the query that gets the rows. The code will make reference to line 7 in this comment.
0
 

Author Comment

by:lthames
ID: 40241237
I think writing code to unserialize (given by Ray) was the proper answer.

All of the numbers needed to sum() are in one field (field name is extradata).  
So I am going through each record where transaction_date between date1 and date2
and adding the values into the buckets and then displaying the values.

Thanks all!
0
 
LVL 58

Expert Comment

by:Gary
ID: 40271282
Actually Ray's comment was an exact copy of my code.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Originally, this post was published on Monitis Blog, you can check it here . In business circles, we sometimes hear that today is the “age of the customer.” And so it is. Thanks to the enormous advances over the past few years in consumer techno…
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
The viewer will learn how to dynamically set the form action using jQuery.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
Suggested Courses
Course of the Month16 days, 14 hours left to enroll

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question