formatting poll results

[jrobles]

I have a table that collects the data from a 15 question poll. Each question has 3 options 1,2 or 3. i.e.

 

                                    yes                no                maybe

Do you like water?

Do you like soda?

etc.

 

My table has a row for each poll. the row has a column for each question i.e.

DATE,Q1,Q2,Q3,Q4...

 

I need to output the results in a table where i have the questions and the totals for each option. i.e.

 

<table width="406" border="1">
  <tr>
    <td width="42"> </td>
    <td width="98">1<br />
      No Interest</td>
    <td width="95">2<br />
      Some Interest</td>
    <td width="143">3<br />
      High Interest</td>
  </tr>
  <tr>
    <td>1</td>
    <td>9</td>
    <td>0</td>
    <td>1</td>
  </tr>
  <tr>
    <td>2</td>
    <td>10</td>
    <td>0</td>
    <td>0</td>
  </tr>
  <tr>
    <td height="23"><strong>3</strong></td>
    <td>5</td>
    <td>5</td>
    <td>0</td>
  </tr>
  <tr>
    <td><strong>4</strong></td>
    <td>3</td>
    <td>3</td>
    <td>4</td>
  </tr>
  <tr>
    <td><strong>5</strong></td>
    <td>9</td>
    <td>1</td>
    <td>0</td>
  </tr>
  <tr>
    <td><strong>6</strong></td>
    <td>6</td>
    <td>2</td>
    <td>2</td>
  </tr>
  <tr>
    <td><strong>7</strong></td>
    <td>8</td>
    <td>1</td>
    <td>1</td>
  </tr>
  <tr>
    <td><strong>8</strong></td>
    <td>7</td>
    <td>2</td>
    <td>1</td>
  </tr>
  <tr>
    <td><strong>9</strong></td>
    <td>5</td>
    <td>4</td>
    <td>1</td>
  </tr>
  <tr>
    <td><strong>10</strong></td>
    <td>5</td>
    <td>5</td>
    <td>0</td>
  </tr>
  <tr>
    <td><strong>11</strong></td>
    <td>3</td>
    <td>4</td>
    <td>3</td>
  </tr>
  <tr>
    <td><strong>12</strong></td>
    <td>1</td>
    <td>1</td>
    <td>8</td>
  </tr>
  <tr>
    <td><strong>13</strong></td>
    <td>2</td>
    <td>8</td>
    <td>0</td>
  </tr>
  <tr>
    <td><strong>14</strong></td>
    <td>7</td>
    <td>2</td>
    <td>1</td>
  </tr>
  <tr>
    <td><strong>15</strong></td>
    <td>3</td>
    <td>3</td>
    <td>4</td>
  </tr>
</table>

 

 

any suggestions?

[Psycho]

You haven't provided enough information. You state there is a table that holds each poll as a single record with separate fields for each question. I assume that will have the text of the question. And, hopefully, each "poll" record has a unique id. But, you have not explained how the results are stored and how those records relate back to the poll records.

 

However, you really should have a separate tables for 1) the polls, 2) the questions, and 3) the results.

[jrobles]

sorry for not posting enough info. The pool is verry simple and I assumed that one table would work. Here is the script for the table I have created

 

CREATE TABLE IF NOT EXISTS `questionnaire` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `source` char(1) COLLATE utf8_unicode_ci NOT NULL,
  `date` datetime NOT NULL,
  `q1` tinyint(4) NOT NULL,
  `q2` tinyint(4) NOT NULL,
  `q3` tinyint(4) NOT NULL,
  `q4` tinyint(4) NOT NULL,
  `q5` tinyint(4) NOT NULL,
  `q6` tinyint(4) NOT NULL,
  `q7` tinyint(4) NOT NULL,
  `q8` tinyint(4) NOT NULL,
  `q9` tinyint(4) NOT NULL,
  `q10` tinyint(4) NOT NULL,
  `q11` tinyint(4) NOT NULL,
  `q12` tinyint(4) NOT NULL,
  `q13` tinyint(4) NOT NULL,
  `q14` tinyint(4) NOT NULL,
  `q15` tinyint(4) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=12 ;

[Psycho]

OK, your first post threw me off. What I understand now is that there is ONE poll and the table in question stores one record for each user who responds to the poll. If that is correct, then the database structure is still terrible and will make getting the data harder than it needs to be, but anyway...

 

I absolutely abhore looping queries, as they are terribly inefficient, but I can't think of a better solution at the moment because of the design. I wrote all of this "freehand" so if you find syntax errors go ahead and fix them

 

<?php

//Gather results into array

$results = array();

//Query for each question
for ($qIdx=0; $qIdx<15; $qIdx++)
{
    $results[$qIdx][1] = 0;
    $results[$qIdx][2] = 0;
    $results[$qIdx][3] = 0;

    $qField = "q" . ($qIdx+1);

    $query = "SELECT {$qField}, COUNT({$qField}) as count
              FROM `questionnaire`
              GROUP BY {$qField}";
    $result = mysql_query($query);

    while($row = mysql_fetch_assoc($result)
    {
        $results[$qIdx][$result[$qField]] = $result["count"];
    }
}

//Create array of the questions
$questions = array(
    'Do you like water?',
    'Do you like soda?',
// ..etc.
);

//Create the output
$output = '';
foreach ($questions as $idx => $question)
{
    $output .= "<tr>\n";
    $output .= "<td>{$question}</td>\n";
    $output .= "<td>{$results[$idx][1]}</td>\n";
    $output .= "<td>{$results[$idx][2]}</td>\n";
    $output .= "<td>{$results[$idx][3]}</td>\n";
    $output .= "</tr>\n";
}

?>

<table>
  <tr>
    <th> </th>
    <th>yes</th>
    <th>no</th>
    <th>maybe</th>
  </tr>
  <?php echo $output; ?>
</table>