php mysql select and count

Question

I have mysql table named 'tagtable' and it has text type column 'params' column like below;

id   tag                 params    //in text type 
1   #discount          {"activity":[105,106,107]}
2   #table             {"activity":[108,109,110,111]}
3   #pencil            {"activity":[112,113]}
4   #door              {"activity":[114]}
5   #phone             {"activity":[115]}
6   #wall              {"activity":[116,117,118,119,120]}
7   #radio             {"activity":[121,122,123]}

my expexted result is selecting top 4 tag with most activity number count from text type params column and counting them, like below:

#wall-5  , #table-4 , #discount-3, #radio-3

How can I achieve that with php ?

What is the reason you don't store normalized?

B001ᛦ
– B001ᛦ

2020-03-09 16:08:21 +00:00
Commented Mar 9, 2020 at 16:08 — B001ᛦ
– B001ᛦ, Commented Mar 9, 2020 at 16:08

Tushar · Accepted Answer · 2020-03-09 16:13:04Z

1

Try this code

SELECT 
ID, 
tag, 
(CHAR_LENGTH(params) - CHAR_LENGTH(REPLACE(params, ',', '')) + 1) as total 
FROM tagtable 
order by total desc 
limit 4

answered Mar 9, 2020 at 16:13

Tushar

5683 silver badges14 bronze badges

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

2 Comments

askerman Over a year ago

Hello,I tried it but gives error to me " Call to a member function query() on boolean "

Tushar Over a year ago

@askerman Please check this link. It's work perfectly. db-fiddle.com/f/oiHQEKQEQPHn8KXsPuRJme/2

Collectives™ on Stack Overflow

php mysql select and count

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related