CODEWITHSUNDEEP
phpmysqlsqldatabase
hackerboey
hackerboey

Reputation: 19

How to separate SQL column data separated by comma to individual values and then count these values

Am using a SQL command in PHP to count the no of values inserted in a column named attack_type. I want to count the occurrence of individual values like website defacement in the whole column of the table. But here the column attack_type contain different values, separated by a comma and the count is treating whole column data as a string. Below is my current SQL statement with its output

I tried explode print_r in PHP

SELECT attack_type, 
       count(*) as number 
FROM data_input_test 
GROUP BY attack_type

Here is the output of the above statement generated:

https://drive.google.com/open?id=1TyRL_Mh0OOJWaCpFczxmBr34No9LUpzH

But what I want is :

https://drive.google.com/open?id=1eeA_1TCER0WMpZwSkBDMzRtRa8xihbZd

and so on. The above desired output is edited to show what I exactly want.

Other answer on stackoverflow and on other forums are either irrelevant or are using regrex or a new table creation in one or the other way. That I don't want as my hosting has some limitations. My hosting doesnt provide creation of triggers, regrex or creation of temp tables

Upvotes: 0

Views: 124

Answers (3)

B-shan
B-shan

Reputation: 404

If you just want to count comma-separated values in rows, you can use:

SELECT SUM(LENGTH(attack_type) - LENGTH(replace(attack_type, ',', '')) +1) AS TotalCount
FROM table_name;

Upvotes: 0

hackerboey
hackerboey

Reputation: 19

So I finally worked around to get my work done using the select only. This only works if you have a finite set of data or specifically less than 64 values.

  1. Change your column datatype to 'set' type. And enter your set values.
  2. Now use select, count, find_in_set and union functions of sql.
  3. Example: 
    union select 'Un-patched Vulnerable Software Exploitaion'as type, count(*) as number from data_input_test where find_in_set('Un-patched Vulnerable Software Exploitaion',attack_type)```

and so on for all your values

I know this is not how you should do but as the legends say this works 😎😎

Upvotes: 0

hackerboey
hackerboey

Reputation: 19

I may have a solution for this but don't know how to apply here. Possible here: https://www.periscopedata.com/blog/splitting-comma-separated-values-in-mysql

Please someone explain me how to apply the same here.

Upvotes: 1

Related Questions