0

I am showing data in html format with a php script from mysql_query.

to make the query statement, I have a correspondent table named 'questionnaire' which has 6 columns named 'id', 'town', 'zip', 'ans1', 'ans2', 'ans3'.

my php script is getting data for those 2 fields- zip and town from a html form and the code is as below-

$town = _POST['town'];
$zip = _POST['zip'];

include (dbconnect.php);

$query = mysql_query("select * from questionnair where town = '$town' && zip = '$zip' order by zip");
$data = mysql_fetch_array($query);

and now i am showing them as-

<html>
 <body>
  <table>
   <tr><th>ZIP</th><th>Ans 1</th><th>Ans 2</th><th>Ans 3</th></tr>
   <? do {
     echo "<tr><td>".$data['zip']."</td><td>".$data['ans1']."</td><td>".$data['ans2']."</td><td>".$data['ans3']."</td></tr>";
     } while ($data = mysql_fetch_array($query));
   ?>
  </table>
 </body>
</html>

Till this it's fine and working properly. Now I am in need of suggestions and help for the following step-

if there is multiple record of a single zip in the zip column then I need to detect that and have to show the average results from 'ans1', 'ans2' and 'ans3' columns for that single zip in a single row in my resulting html table.

For example-

------------------------------------------------
  town   |  zip  |  ans1 |  ans2  |  ans3
================================================
   NY    |  107  |   4   |   5    |   2
------------------------------------------------
   NY    |  107  |   1   |   4    |   3
------------------------------------------------
   NY    |  108  |   2   |   2    |   2
------------------------------------------------
   NY    |  109  |   5   |   3    |   1
------------------------------------------------
   NY    |  107  |   2   |   2    |   1
------------------------------------------------
   NY    |  107  |   1   |   4    |   3
------------------------------------------------
   NY    |  112  |   2   |   4    |   5
------------------------------------------------
   NY    |  113  |   5   |   1    |   1
------------------------------------------------
   NY    |  113  |   3   |   3    |   2
------------------------------------------------
   NY    |  113  |   2   |   3    |   4
------------------------------------------------
   NY    |  116  |   5   |   5    |   2
------------------------------------------------

But I intend to show the resulting table as-

------------------------------------------------
  town   |  zip  |  ans1 |  ans2  |  ans3
================================================
   NY    |  107  |   2   |  3.75  |  2.25
------------------------------------------------
   NY    |  108  |   2   |   2    |   2
------------------------------------------------
   NY    |  109  |   5   |   3    |   1
------------------------------------------------
   NY    |  112  |   2   |   4    |   5
------------------------------------------------
   NY    |  113  |  3.66 |  2.33  |  2.33
------------------------------------------------
   NY    |  116  |   5   |   5    |   2
------------------------------------------------

I have tried with these posts- How to detect duplicate values in PHP array? and PHP: Check for duplicate values in a multidimensional array

But none was appropriate for my case. I am really in need of suggestion or at least hints for how to reach my goal.

Community
  • 1
  • 1
Saquib
  • 3
  • 3

2 Answers2

1

You should do this in your mysql query using AVG() and GROUP BY

SELECT id, town, zip, AVG(ans1) as ans1, AVG(ans2) as ans2, AVG(ans3) as ans3
FROM questionnair
WHERE town = '$town' && zip = '$zip'
GROUP BY zip
ORDER by zip
Sean
  • 12,443
  • 3
  • 29
  • 47
1

Don't do this in PHP. It's much more efficient to do it in MySQL.

select AVG(ans1) AS ans1, AVG(ans2) AS ans2, AVG(ans3) AS ans3, town, zip
from questionnair
where town = '$town' AND zip = '$zip'
GROUP BY zip
order by zip

This will give you your distinct zips, and the average values for each answer.

Marcus Adams
  • 53,009
  • 9
  • 91
  • 143
osuddeth
  • 152
  • 9