tallying information from database

[riffy]

Hi all,

I have a table in my database with about 64 columns. There are 27 questions, each with 4 radio buttons having the values 1,2,3,4. So 27 columns in the table have one of those values stored in them.
My question is whether there a way I can tally how many 1's there are, 2's there are, 3's there are and 4's there are in the table?

Can anyone help?

Thanks
Arif

😕 😕

[pipeten]

arrif, EDIT:"Arif" sorry 🙂
can you do a "describe mytable" within the mysql client and post the structure for us can you. Allways makes answering easier :-)

[riffy]

here you go pipeten,

-- MySQL dump 8.22

-- Host: localhost Database: elmet_survey

-- Server version 3.23.53

--

-- Table structure for table 'survey'

CREATE TABLE survey (
id int(10) NOT NULL auto_increment,
company_name varchar(100) default NULL,
title varchar(100) default NULL,
telephone varchar(100) default NULL,
email varchar(200) default NULL,
question1 int(5) default NULL,
question1y varchar(200) default NULL,
question2 int(5) default NULL,
question2y varchar(200) default NULL,
question3 int(5) default NULL,
question3y varchar(200) default NULL,
question4 int(5) default NULL,
question4y varchar(200) default NULL,
question5 int(5) default NULL,
question5y varchar(200) default NULL,
question6 int(5) default NULL,
question6y varchar(200) default NULL,
question7 int(5) default NULL,
question7y varchar(200) default NULL,
question8 int(5) default NULL,
question8y varchar(200) default NULL,
question9 int(5) default NULL,
question9y varchar(200) default NULL,
question10 int(5) default NULL,
question10y varchar(200) default NULL,
question11 int(5) default NULL,
question11y varchar(200) default NULL,
question12 int(5) default NULL,
question12y varchar(200) default NULL,
question13 int(5) default NULL,
question13y varchar(200) default NULL,
question14 int(5) default NULL,
question14y varchar(200) default NULL,
question15 int(5) default NULL,
question15y varchar(200) default NULL,
question16 int(5) default NULL,
question16y varchar(200) default NULL,
question17 int(5) default NULL,
question17y varchar(200) default NULL,
question18 int(5) default NULL,
question18y varchar(200) default NULL,
question19 int(5) default NULL,
question19y varchar(200) default NULL,
question20 int(5) default NULL,
question20y varchar(200) default NULL,
question21 int(5) default NULL,
question21y varchar(200) default NULL,
question22 int(5) default NULL,
question22y varchar(200) default NULL,
question23 int(5) default NULL,
question23y varchar(200) default NULL,
question24 int(5) default NULL,
question24y varchar(200) default NULL,
question25 int(5) default NULL,
question25y varchar(200) default NULL,
question26 int(5) default NULL,
question26y varchar(200) default NULL,
question27 int(5) default NULL,
question27y varchar(200) default NULL,
question28 varchar(20) default NULL,
question29 varchar(20) default NULL,
question30a varchar(200) default NULL,
question30b varchar(200) default NULL,
question30c varchar(200) default NULL,
PRIMARY KEY (id)
) TYPE=MyISAM;

the fields question1, question2 and so on have either 1,2,3 or 4 stored in them...i need to get how many of each number are stored in the fields...
any suggestions?

[riffy]

can anyone help?

i'm doing something like this...

$c = 1;
for ($c;$c<28;$c++) {
for ($j=1;$j<=4;$j++) {
$sql2= "select count(*) from survey where question.$c=$j";
$result2 = mysql_query ($sql2);
$result_row = mysql_fetch_row($result2);
$field = $result_row[0];
print "$field<br>";
}
}

is there some other way i can go thru each question field in the table because question.$c does not seem to be working???

Arif😕

[sfullman]

how many 1's, 2's, 3's etc.? in ONE FIELD or in all 27? it makes a difference in how I answer.

Sam

[[deleted]]

Perhaps it's a good idea to read-up on proper database design. Not only willit reduce the number of koloms to 1, it will also greatly simplify the queries that 'tally up' the results!

[riffy]

i got it to work guys...and vincente thanks for the suggestion, i'm thinking you would want me to use an array right?

[[deleted]]

An array? most definately NOT 🙂

In a 'proper' design you'd put the questions into a seperate table, and put the answers that people give in yet another table.

Then you'd have a table for people, a table for questions and a table for anwers:

people:
userid, name, etc

questions:
questionid, question-text

answers
userid, questionid, answertext

That way you can add an inifinate number of question just by adding a new row to the questions table.

The people who did not answer a newly created question also have no corresponding record in the answer table, so you know they have not answered that question.