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

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!
lthamesAsked:
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.

Ray PaseurCommented:
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
GaryCommented:
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
Ray PaseurCommented:
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
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Ray PaseurCommented:
Also, it looks like the sample data given here is invalid.  What is a "real" example, please?
0
lthamesAuthor Commented:
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
GaryCommented:
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

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
Ray PaseurCommented:
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
lthamesAuthor Commented:
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
GaryCommented:
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
GaryCommented:
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
Ray PaseurCommented:
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
lthamesAuthor Commented:
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
GaryCommented:
Actually Ray's comment was an exact copy of my code.
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
PHP

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.