0

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

hackerboey
  • 19
  • 5
  • Please include the actual and expected data here, linked content can disappear overtime. Also, a table definition would be useful. – gaborsch Jul 04 '19 at 10:35
  • Possible duplicate of [Turning a Comma Separated string into individual rows](https://stackoverflow.com/questions/5493510/turning-a-comma-separated-string-into-individual-rows) – gaborsch Jul 04 '19 at 10:39
  • @gaborsch the links above has the actual and expected outputs and am sory am unable to share the full table here but this column is varchar type – hackerboey Jul 04 '19 at 10:53
  • @gaborsch this is not a duplicate question.I searched everywhere but no answer anywhere and also i want count of these values and individually – hackerboey Jul 04 '19 at 10:54
  • Fix your data model! Don't store duplicated values in a string. Use a junction/association table instead. – Gordon Linoff Jul 04 '19 at 11:26

3 Answers3

1

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.

hackerboey
  • 19
  • 5
0

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;
Tajinder
  • 2,248
  • 4
  • 33
  • 54
B-shan
  • 404
  • 1
  • 5
  • 14
  • I don't want total count of the data in that column. What I want is count of individual values in the attack_type column for the whole table. Your query will output 32 as there are 32 values inserted in the column of this table. But I want like how many times some value occurred for eg website defacement occurred for 4 times. – hackerboey Jul 04 '19 at 11:07
0

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

hackerboey
  • 19
  • 5