[SOLVED] Find out percent of columns filled in

[marksie1988]

hi,

 

i have a database which is basically a questionaire that people can save and then finish filling in later on but what i want to do is let them see a %tage of what they have filled in so for e.g.

 

Questionaire 1 - 50% complete

Questionaire 2 - 10% complete

 

the only thing that im stuck on is how to find out the %tage that they have filled out, here is the table

CREATE TABLE IF NOT EXISTS `mod1pae` (
  `id` int(4) NOT NULL auto_increment,
  `userid` int(4) NOT NULL,
  `q1` text NOT NULL,
  `q2` text NOT NULL,
  `q3` text NOT NULL,
  `q4` text NOT NULL,
  `q5` text NOT NULL,
  `q6` text NOT NULL,
  `q7` text NOT NULL,
  `q8` text NOT NULL,
  `q9` text NOT NULL,
  `q10` text NOT NULL,
  `q11` text NOT NULL,
  `q12` text NOT NULL,
  `q13` text NOT NULL,
  `q14` text NOT NULL,
  `q15` text NOT NULL,
  `perc` varchar(3) default NULL,
  PRIMARY KEY  (`id`)
)

 

the perc column is for the percentage to be stored if that is easier then creating it on the fly, i did think about doing if !empty $perc= 6.6% for each question and then add it up at the end but this is a lengthy piece of code wondered if there is an easier way?

 

Regards

Steve

 

[twm]

(Answered Questions / Total Questions) x 100

[Maq]

$filled = 0;
$query = "SELECT * FROM modlpae WHERE userid = '$user'";
$result = mysql_query($query) or die('Error : ' . mysql_error());
$total = mysql_num_rows($result);
$track = $total;
while ($row = mysql_fetch_assoc($result)) {
$q = "q".$track;
if($row[$q] != '') {
$filled++;
$track--;
}
else {
$track--;
}
}
$percentage = $filled/$track*100;
echo $percentage;
?>

[marksie1988]

Thanks Alot

 

That works perfectly

[marksie1988]

Seems that i was a bit too fast with my reply,

 

that code you gave me always says 0 for the percentage :S  not sure what is causing it

 

EDIT: looks like the script you gave me only tries to select the database and then once the data is pulled through it finds 1 row and then puts a q before it making q1 and obviously this wont return the percentage of filled in columns :S

 

so looks like your way would only work if each question was put into the database as a row but it isnt each user gets one line which has the question in it, or would it be easier to have a row for each question and then a user id column that linked that question to a user?

[twm]

<?php 

$query = "SELECT * FROM modlpae WHERE userid = '$user'";
$result = mysql_query($query) or die('Error : ' . mysql_error());

$total_questions = 15;
$qcounter = 1;
$filled = 0;

while ($row = mysql_fetch_assoc($result)) {

if(!is_null($row["q".$qcounter]) || $row["q".$qcounter] != '') {
   $filled++;
}

$qcounter++;

}
$percentage = ($filled/$total_questions)*100;
echo $percentage;
?>

[marksie1988]

wow thats great thanks, i always think i know alot about php then you guys just pwn my ass with great solutions fast aswell cheers

[marksie1988]

ok well looks like i got over excited again this still only counts  the information in each row rather than each question on a single row

 

 

[sasa]

try

<?php 
$query = "SELECT * FROM modlpae WHERE userid = '$user'";
$result = mysql_query($query) or die('Error : ' . mysql_error());
$total_questions = 15;
while ($row = mysql_fetch_assoc($result)) {
$filled = 0;
for ($qcounter = 1; $qcounter <= $total_questions; $qcounter++){
	if(!is_null($row["q".$qcounter]) || $row["q".$qcounter] != '') $filled++;
}
echo $percentage = ($filled/$total_questions)*100,"%\n";
}
?>

[marksie1988]

unfortunatly no that one just says 100% all of the time

[sasa]

ups

<?php 
$query = "SELECT * FROM modlpae WHERE userid = '$user'";
$result = mysql_query($query) or die('Error : ' . mysql_error());
$total_questions = 15;
while ($row = mysql_fetch_assoc($result)) {
$filled = 0;
for ($qcounter = 1; $qcounter <= $total_questions; $qcounter++){
	if(!is_null($row["q".$qcounter]) and $row["q".$qcounter] != '') $filled++;
}
echo $percentage = ($filled/$total_questions)*100,"%\n";
}
?>

change or to and