mysql, How to sum a value in an array which stored as a field value?

Question

The user table is like this:

user_id  |  key     |   value
1          option      $array
2          other        abc
2         option      $array

I need to query all user's option field, the value is an array like this:

$array=array('item_id'=>123, 'key1'=>'abc', 'key2'=>'1', 'key3'=>'5',)

Then, to sum all value of key2 in this array.

The only way I can think of is to make a virtual table with each array as one row. Perhaps it's not possible. Then, how can I make the sum for all user's key2 in option field ?

Does it needs to be in the query itself, or is it no problem if some logic is used outside the query? — Green Black
– Green Black, Commented Aug 16, 2012 at 23:47
it doesn't matter how to do it, I just need the total value of key2,and I will need to do the SUM repeatedly when a user saved some new information. — Jenny
– Jenny, Commented Aug 16, 2012 at 23:53
Would you be able to post a snippet of data from the database? — Diego
– Diego, Commented Aug 16, 2012 at 23:58
When I query the option field, the $array is the result I get from the database. — Jenny
– Jenny, Commented Aug 17, 2012 at 0:10

Robbie · Accepted Answer · 2012-08-17 00:15:40Z

1

Apart from Cihan's answer (summing each row), or creating a view in the database (and the view will need to understand the serialisation of the array) then the fastest way would be to store the data "properly" and not in a munged field like that.

If you want fast and regular summing, create a second table:

user_id  |  key     |   array_key | value 
1          option      item_id        123
1          option      key1           abc 
1          option      key2           2
2          other       NULL           123
2          option      item_id        456

You could leave array_key blank (or NULL) if it's not an array.

Will require a little bit of rethinking and possibly restructuring some of the code, but it'll be cleaner, easier to debug and ultimately faster if you're summing a lot. The downside it more rows to update when creating a new entry.

You can use GROUP BY and GROUP_CONCAT if you want the single rows.

answered Aug 17, 2012 at 0:15

Robbie

17.7k4 gold badges40 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jenny Over a year ago

the reason I choose to save data as a field in user table is because the usertable is native wordpress table, caching and other related jobs will be take care of by wordpress itself. If I create a new table, I will do all the job by myself. Maybe I need to rethink.

Robbie Over a year ago

It's all good. Start with getting it to work simply first, then improve from there when you need to.

score 0 · Accepted Answer · 2012-08-17 00:21:15Z

0

$query = mysql_query("SELECT * FROM table WHERE key = 'option'");

while($row = mysql_fetch_array($query)){    
   $array_sum_values[] = $row['value']['key2']; //We are creating a new array. }

$result = array_sum($array_sum_values);

if i understood you exactly here is the solution.

And alternatively,

   $query = mysql_query("SELECT * FROM table WHERE key = 'option'");
 $array_sum = 0;
while($row = mysql_fetch_array($query)){    
   $array_sum += $row['value']['key2']; //We are creating a new array.}

edited Aug 17, 2012 at 0:21

answered Aug 17, 2012 at 0:05

user1329212

6 Comments

Robbie Over a year ago

Remove the [] from $array_sum_values? To give the total sum?

user1329212 Over a year ago

no. we created a new array $array_sum_values. And when we created our array. We sum the each values in array with array_sum

Robbie Over a year ago

Ah - missed the last line. Yes - your answer is right, but why not simply sum in the first step, and skip the second array? Cleaner and saves memory/time.

user1329212 Over a year ago

i added alternative Robbie. Thank you.

Jenny Over a year ago

This is exactly the formula I need.

|

Collectives™ on Stack Overflow

mysql, How to sum a value in an array which stored as a field value?

2 Answers 2

2 Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related