php - How to get count from associative array in MariaDB table? - Stack Overflow

admin管理员组

文章数量:1406308

I have these entries in a MariaDB Wordpress postmeta table:

meta_key   -------------              meta_value
profile_star_rating     ----------------a:1:{s:25:"skills";s:1:"4";}
profile_star_rating  ----------------a:1:{s:25:"skills";s:1:"3";}

and so on...

I need to count all rows which have the meta_key "profile_star_rating" plus sum up the value from meta_value field. The meta_value is written as an associative array.

How to handle associative arrays in a SQL statement? My programming knowledge is very basic.

EDIT: To make it more clear: Screenshot DB

In this case I need to get 2 for the occurence of the same meta key "profile_star_rating" and 7 for the total count of values 3 and 4.

I can get the value from one cell like this, but how to wrap that up in an SQL statement?

add_action('wp_trash_post', 'my_deleted_post');
function my_deleted_post($post_id) {
global $post;
$author_id=$post->post_author;
$rating=$post->profile_star_rating;
$value=$rating['profile_star_rating']

I have these entries in a MariaDB Wordpress postmeta table:

meta_key   -------------              meta_value
profile_star_rating     ----------------a:1:{s:25:"skills";s:1:"4";}
profile_star_rating  ----------------a:1:{s:25:"skills";s:1:"3";}

and so on...

I need to count all rows which have the meta_key "profile_star_rating" plus sum up the value from meta_value field. The meta_value is written as an associative array.

How to handle associative arrays in a SQL statement? My programming knowledge is very basic.

EDIT: To make it more clear: Screenshot DB

In this case I need to get 2 for the occurence of the same meta key "profile_star_rating" and 7 for the total count of values 3 and 4.

I can get the value from one cell like this, but how to wrap that up in an SQL statement?

add_action('wp_trash_post', 'my_deleted_post');
function my_deleted_post($post_id) {
global $post;
$author_id=$post->post_author;
$rating=$post->profile_star_rating;
$value=$rating['profile_star_rating']

• php
• mariadb

Share Improve this question Follow edited Mar 6 at 1:54 user27734429 asked Mar 5 at 21:23 user27734429user27734429 755 bronze badges 5

• 4 Those values are in PHP serialize() format. There's no easy way to process them directly in SQL, you'll need to extract them from the DB and use unserialize() to parse them. – Barmar Commented Mar 5 at 21:32
• 1 Ideally you should normalize your database, using another table to hold associative data (one row per key). If you really need to keep the associative array, you could convert it to JSON, as MySQL has built-in JSON functions. But lots of things still aren't easy, like searching arrays. – Barmar Commented Mar 5 at 21:35
• Like Barmar wrote, PHP serialized data, a database inside the database. You have to mount the inner data-base and then query the data, so it's a two-phase operation. – hakre Commented Mar 5 at 21:40
• 1 shouldn't it be s:6:"skills"? it may be possible to extract the data using sql, but you'd have to provide a whole lot more examples to show what all kind of values you are dealing with – ysth Commented Mar 5 at 21:44
• I posted a screenshot and the output I expect. I hope this clarifies it. As you see it is s:25:"array_index"... – user27734429 Commented Mar 6 at 1:24

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 1

Check meta_value (a:1:{s:25:"skills";s:1:"4";}) accordance to pattern

'a:[0-9]:{s:[0-9]{1,3}:"skills";s:[0-9]:"[0-9]{1,3}";}'

Then take part ({s:25:"skills";s:1:"4";}) with pattern

'{s:[0-9]{1,3}:"skills";s:[0-9]:"[0-9]{1,3}";}'

Then part (:"4";) with pattern

':"[0-9]{1,3}";'

Then part(4) with pattern

'[0-9]{1,3}'

See example

id	meta_key	meta_value
1	rating	a:1:{s:25:"skills";s:1:"4";}
2	rating	a:1:{s:25:"skills";s:1:"3";}
3	rating	a:1:{s:9:"skills";s:1:"123";}
4	rating	a:1:{s:9:"weight";s:1:"70";}
5	hobby	a:1:{s:9:"skills";s:1:"100";}
6	rating	a:1:{s:9:"skills";s:13:"200";}

select meta_key
  ,sum(
   regexp_substr(
     regexp_substr(
       regexp_substr(meta_value
          ,'{s:[0-9]{1,3}:"skills";s:[0-9]:"[0-9]{1,3}";}')
       ,':"[0-9]{1,3}";')
    ,'[0-9]{1,3}'
   ) -- s_skill_value
  )totSum
  ,count(*) as cnt
from test
where meta_key='rating' 
  and length(regexp_substr(meta_value,'a:[0-9]:{s:[0-9]{1,3}:"skills";s:[0-9]:"[0-9]{1,3}";}'))>0

meta_key	totSum	cnt
rating	130	3

Last row a:1:{s:9:"skills";s:13:"200";} do not match pattern with s:13. There should be 1 digit.

fiddle

Solved this question by myself. I am using get_post_meta() which automatically unserialize the data and then array_sum() to get the number. Thanks for the answers.

本文标签： phpHow to get count from associative array in MariaDB tableStack Overflow