php - How do I Count Field Values from a Single Row? -

i'm new php , mysql , struggling this... collecting form data php insert page. survey questionnaire. there 20 questions, , answers either a, b , c. table has id column, , column each question (q1, q2, q3... q20). data may this;

+------+-----+ |  id  |  1  | +------+-----+ |  q1  |   | |  q2  |  b  | |  q3  |   | |  .   |  .  | |  .   |  .  | |  .   |  .  | |  q20 |  c  | +------+-----+ 

now, trying count how many values of a, b , c appear in single row (with id=1 example).

i've found lots of ways of counting values in multiple columns, far have not found way count/group values in single row.

here's how can mysql query:

select id,   sum(case when `1` = 'a' 1 else 0 end) counta,   sum(case when `1` = 'b' 1 else 0 end) countb,   sum(case when `1` = 'c' 1 else 0 end) countc surveytable group id order id; 

here's sql fiddle limited test data.

---

addendum. carlos posted updated structure, leading following answers. hope these close :)

this give 1 wide row grand totals:

select   sum(case when q1 = 'a' 1 else 0 end) q1counta,   sum(case when q1 = 'b' 1 else 0 end) q1countb,   sum(case when q1 = 'c' 1 else 0 end) q1countc,   sum(case when q2 = 'a' 1 else 0 end) q2counta,   sum(case when q2 = 'b' 1 else 0 end) q2countb,   sum(case when q2 = 'c' 1 else 0 end) q2countc,   sum(case when q3 = 'a' 1 else 0 end) q3counta,   sum(case when q3 = 'b' 1 else 0 end) q3countb,   sum(case when q3 = 'c' 1 else 0 end) q3countc,   sum(case when q4 = 'a' 1 else 0 end) q4counta,   sum(case when q4 = 'b' 1 else 0 end) q4countb,   sum(case when q4 = 'c' 1 else 0 end) q4countc,   sum(case when q5 = 'a' 1 else 0 end) q5counta,   sum(case when q5 = 'b' 1 else 0 end) q5countb,   sum(case when q5 = 'c' 1 else 0 end) q5countc surveytable; 

if want 1 row per question try this:

select   questionid,   sum(case when answer = 'a' 1 else 0 end) counta,   sum(case when answer = 'b' 1 else 0 end) countb,   sum(case when answer = 'c' 1 else 0 end) countc (     select 'question1' questionid, q1 answer surveytable     union select 'question2', q2 surveytable     union select 'question3', q3 surveytable     union select 'question4', q4 surveytable     union select 'question5', q5 surveytable) x group questionid 

there's fiddle here.

---

another addendum: counts needed id, , because there's 1 id per row there's no need sum.

this changes approach. first strings answers together:

concat(q1,q2,q3,q4,q5) -- result id=1 in test data: 'abcac' 

... sucks every occurrence of a string:

replace(concat(q1,q2,q3,q4,q5), 'a', '') -- result id=1: 'bcc' 

... first string (abcac) length 5, , second string (bcc) length 3. difference in length number of a answers: 2. that's can explain this. query:

select   id,   5 - length(replace(concat(q1,q2,q3,q4,q5), 'a', '')) counta,   5 - length(replace(concat(q1,q2,q3,q4,q5), 'b', '')) countb,   5 - length(replace(concat(q1,q2,q3,q4,q5), 'c', '')) countc surveytable; 

the updated fiddle here.

this gives raw data only. formatting bit tricky shouldn't bad, if front-end language. if must use mysql this, easier put above subquery , apply formatting in outer query:

select   id,   concat('you have chosen ' ...and miles of formatting logic using counta, etc) (   select     id,     5 - length(replace(concat(q1,q2,q3,q4,q5), 'a', '')) counta,     5 - length(replace(concat(q1,q2,q3,q4,q5), 'b', '')) countb,     5 - length(replace(concat(q1,q2,q3,q4,q5), 'c', '')) countc   surveytable) x