[dandiud]

Hi!

I would appreciate any ideas or thoughts on the following:

I'm trying to create a PHP Survey using Dreamweaver 8 and its built in PHP MySQL functions.

I have 15 questions were one is supposed to grade on a scale of 1 to 5. The idea of this is to figure out averages for every question. I used radiobuttons for this, but I haven't quite figured out a way of doing this other than creating individual Recordsets and then Displaying Total Records for each (...annoying...)

There should be a better way to do this, I'm guessing it might involve PHP and probably arrays.

Thanks!

[ruteckycs]

You want to know the average response value? Read them all into a counter then divide by the number of records. Im not sure what the question is .... do you need code? Whats the field name in your DB.

[dandiud]

Thanks for replying!

Yeah. I'm looking for the average value and I guess you're right about using a counter. My coding is far from being any good, and Id' rather not tamper with any of the code inserted by Dreamweaver.

When I say 15 questions, I really mean 15 variables, one variable per question

This is an example of the code:

PHP Code:

 <tr>
      <td>b) El proceso de definicion de arquitectura y dise&ntilde;o de cada local </td>
      <td><div align="center"><?php echo $totalRows_Tot3bNot1 ?> </div></td>
      <td><div align="center"><?php echo $totalRows_Tot3bNot2 ?> </div></td>
      <td><div align="center"><?php echo $totalRows_Tot3bNot3 ?> </div></td>
      <td><div align="center"><?php echo $totalRows_Tot3bNot4 ?> </div></td>
      <td><div align="center"><?php echo $totalRows_Tot3bNot5 ?> </div></td>
      <td><div align="center"><?php echo (($totalRows_Tot3bNot1*1+$totalRows_Tot3bNot2*2+$totalRows_Tot3bNot3*3+$totalRows_Tot3bNot4*4+$totalRows_Tot3aNot5*5)/$totalRows_Total); ?></div></td>
    </tr>
    <tr>
      <td>c) Capacitacion inicial </td>
      <td><div align="center"><?php echo $totalRows_Tot3cNot1 ?> </div></td>
      <td><div align="center"><?php echo $totalRows_Tot3cNot2 ?> </div></td>
      <td><div align="center"><?php echo $totalRows_Tot3cNot3 ?> </div></td>
      <td><div align="center"><?php echo $totalRows_Tot3cNot4 ?> </div></td>
      <td><div align="center"><?php echo $totalRows_Tot3cNot5 ?> </div></td>
      <td><div align="center"><?php echo (($totalRows_Tot3cNot1*1+$totalRows_Tot3cNot2*2+$totalRows_Tot3cNot3*3+$totalRows_Tot3cNot4*4+$totalRows_Tot3cNot5*5)/$totalRows_Total); ?></div></td>
    </tr>

I'm creating one query per value, and thn using the built in function to Display Total Records matching the query (yeah, I know, it sounds insane) --- Dreamweaver is beginning to slow dow.


THANKS

[wirehopper]

I think you need to use an array, and you shouldn't try to use DreamWeaver for complex code.

[job0107]

I am assuming that you have 15 columns in the table in the database that store the responses for each question.
Response for each question will be a number from 1 to 5.
And there is one record for each person that contains all 15 questions.

After the survey is complete, you want to get an average for each question.
So the formula for the average wound be "the value of the question in each record added together / number of records".
The formula would be applied to each question.
So you would come up with 15 averages, one average for each question.

The simplest method to get an average of each question would be to do it in the query.

Example:

PHP Code:

<?php
$table = ""; // Enter the table name here. //
$sql = "SELECT round(AVG(column1)) as c1, round(AVG(column2)) as c2, round(AVG(column3)) as c3, round(AVG(column4)) as c4, round(AVG(column5)) as c5, round(AVG(column6)) as c6, round(AVG(column7)) as c7, round(AVG(column8)) as c8, round(AVG(column9)) as c9, round(AVG(column10)) as c10, round(AVG(column11)) as c11, round(AVG(column12)) as c12, round(AVG(column13)) as c13, round(AVG(column14)) as c14, round(AVG(column15)) as c15 FROM $table";
$results = mysql_query($sql);
$row = mysql_fetch_assoc($results);
echo $row["c1"]."<br />".
     $row["c2"]."<br />".
     $row["c3"]."<br />".
     $row["c4"]."<br />".
     $row["c5"]."<br />".
     $row["c6"]."<br />".
     $row["c7"]."<br />".
     $row["c8"]."<br />".
     $row["c9"]."<br />".
     $row["c10"]."<br />".
     $row["c11"]."<br />".
     $row["c12"]."<br />".
     $row["c13"]."<br />".
     $row["c14"]."<br />".
     $row["c15"]."<br />";
?>

[dandiud]

Thanks! That code sounds about right.

I'l try that, and repost after I try.

[dandiud]

THANKS!!!! job0107

Worked fine!!!

One question though, would there be anyway to retrieve total sum of values (i.e. Total number of values equal to 1, total number of values equal to 2, etc... total number of values equal to 5.

Thanks again!!!!,

[dandiud]

I'm guessing that might involve using MySQL:

SELECT COUNT (column1) WHERE column1 ="1" as c1, ......COUNT(column1) WHERE column1="5" as c5

...and so on....

???

[job0107]

Originally Posted by dandiud

I'm guessing that might involve using MySQL:

SELECT COUNT (column1) WHERE column1 ="1" as c1, ......COUNT(column1) WHERE column1="5" as c5

...and so on....

???

You won't be able to construct your query that way.
There is only one WHERE clause allowed in each SELECT statement.

Here is a program that runs a query that will fetch the column names from the mysql table and store them in an array ($columns).
Then another query is performed to fetch the average of each column.
Then a third query is performed to fetch the value from each column in each record and use a series of switches to total all like values in each column.
And get the count of total records.

Then all the data is neatly displayed in a table.

These are the things you need to do to setup the program:
1. Enter the data that you need to setup the mysql connection.
2. The 15 column names that you use in your mysql table need to be entered into the $ColumnNames array.

After you do that, the program will take care of the rest.

PHP Code:

<html>
<head>
<style>
td{text-align:center;}
</style>
</head>
<body>
<?php
// Replace the 15 column names in the $ColumnNames array with the column names that you use in your mysql table. //
$ColumnNames=array("column1","column2","column3","column4","column5","column6","column7","column8","column9","column10","column11","column12","column13","column14","column15");

$host="";     // Enter mysql host address here. //
$user="";     // Enter mysql username here. //
$password=""; // Enter mysql password here. //
$db="";       // Enter mysql database name here. //
$table="";    // Enter mysql table name here. //

mysql_connect($host,$user,$password);
mysql_select_db($db);

$sql="SHOW COLUMNS FROM $table";
$results=mysql_query($sql);
while($row=mysql_fetch_row($results)){if(in_array($row[0],$ColumnNames)){$columns[] = $row[0];}}
mysql_free_result($results);

$sql = "SELECT round(AVG($columns[0]),1) as c1, round(AVG($columns[1]),1) as c2, round(AVG($columns[2]),1) as c3, round(AVG($columns[3]),1) as c4, round(AVG($columns[4]),1) as c5, round(AVG($columns[5]),1) as c6, round(AVG($columns[6]),1) as c7, round(AVG($columns[7]),1) as c8, round(AVG($columns[8]),1) as c9, round(AVG($columns[9]),1) as c10, round(AVG($columns[10]),1) as c11, round(AVG($columns[11]),1) as c12, round(AVG($columns[12]),1) as c13, round(AVG($columns[13]),1) as c14, round(AVG($columns[14]),1) as c15 FROM $table";
$results = mysql_query($sql);
$row = mysql_fetch_assoc($results);
for($i=1;$i<=count($row);$i++){${"c".$i} = $row["c".$i];}
mysql_free_result($results);

$sql = "SELECT * FROM $table";
$results = mysql_query($sql);
$record_count = mysql_num_rows($results);
while($row = mysql_fetch_assoc($results)){
  for($i=1;$i<=count($columns);$i++)
  {
   switch ($row[$columns[$i-1]])
   {
    case 1:
     ${"c".$i."a"}++;
     break;
    case 2:
     ${"c".$i."b"}++;
     break;
    case 3:
     ${"c".$i."c"}++;
     break;
    case 4:
     ${"c".$i."d"}++;
     break;
    case 5:
     ${"c".$i."e"}++;
     break;
    }
   }
}
mysql_free_result($results);

echo "<table border=1>
       <tr><th>Total # Records</th><td colspan=6>".$record_count."</td></tr>
       <tr height=20><td colspan=7></td></tr>
       <tr><th>Column Name</th><th>AVGERAGE</th><th># 1's</th><th># 2's</th><th># 3's</th><th># 4's</th><th># 5's</th></tr>";
       For($i=1;$i<=count($columns);$i++){echo "<tr><td>".$columns[$i-1]."</td><td>".${"c".$i}."</td><td>".${"c".$i."a"}."</td><td>".${"c".$i."b"}."</td><td>".${"c".$i."c"}."</td><td>".${"c".$i."d"}."</td><td>".${"c".$i."e"}."</td></tr>";}
echo "</table>";
?>
</body>
</html>

[dandiud]

THANKS!!!!!!

That worked!!! Now I'm going to try to buid another table where I can store question names so the code can show questions instead of column names!!!!