Querying the Server using a Script

[whobutsb]

Hi All-

I have a database for a catering company that takes in survey results and I'm trying to display them within a HTML table. The survey database looks something like this:

Rate the following on a scale from 1-4 (4 being the best):

• Planning Process

• Menu

• Quality of Food

• Presentation of food

• etc.

I'm querying the MySQL Database like this to get the total number of rows for variables 1-4 and the total number of rows all together, for each of the above items.

mysql_select_db($database_results, $results);
$query_qualityFood_all = "SELECT rate_quality_of_food FROM survey";
$qualityFood_all = mysql_query($query_qualityFood_all, $results) or die(mysql_error());
$row_qualityFood_all = mysql_fetch_assoc($qualityFood_all);
$totalRows_qualityFood_all = mysql_num_rows($qualityFood_all);

mysql_select_db($database_results, $results);
$query_qualityFood_4 = "SELECT rate_quality_of_food FROM survey WHERE rate_quality_of_food = '4'";
$qualityFood_4 = mysql_query($query_qualityFood_4, $results) or die(mysql_error());
$row_qualityFood_4 = mysql_fetch_assoc($qualityFood_4);
$totalRows_qualityFood_4 = mysql_num_rows($qualityFood_4);

mysql_select_db($database_results, $results);
$query_qualityFood_3 = "SELECT rate_quality_of_food FROM survey WHERE rate_quality_of_food = '3'";
$qualityFood_3 = mysql_query($query_qualityFood_3, $results) or die(mysql_error());
$row_qualityFood_3 = mysql_fetch_assoc($qualityFood_3);
$totalRows_qualityFood_3 = mysql_num_rows($qualityFood_3);

mysql_select_db($database_results, $results);
$query_qualityFood_2 = "SELECT rate_quality_of_food FROM survey WHERE rate_quality_of_food = '2'";
$qualityFood_2 = mysql_query($query_qualityFood_2, $results) or die(mysql_error());
$row_qualityFood_2 = mysql_fetch_assoc($qualityFood_2);
$totalRows_qualityFood_2 = mysql_num_rows($qualityFood_2);

mysql_select_db($database_results, $results);
$query_qualityFood_1 = "SELECT rate_quality_of_food FROM survey WHERE rate_quality_of_food = '1'";
$qualityFood_1 = mysql_query($query_qualityFood_1, $results) or die(mysql_error());
$row_qualityFood_1 = mysql_fetch_assoc($qualityFood_1);
$totalRows_qualityFood_1 = mysql_num_rows($qualityFood_1);

My question to the community is there a way to automate this input process so that it will increment through each one of the 4 variables? It would be a lifesaver compared to copying and pasting over and over again.

Thanks for the help!

[Roger_Ramjet]

What you are after are the mysql aggregate functions that will output your totals from one query. Depending on your mysql version you can also get the group totals totalles by gropu by modifiers with roolup

I'm guessing at your table layout

$sql = "SELECT rate_quality_of_food,  COUNT(rate_quality_of_food) AS count FROM survey GROUP BY rate_quality_of_food ORDER BY rate_quality_of_food";

That will give you the count of rows for values 1,2,3,4 and with roolup you would also get the total count of all rows, but that is easy to do in php as you layout the results anyway.

Now what you are really after is a Pivot Table, sometimes called a Crosstab. Mysq; does not have a lot of support for that type of query and I don't think you are ready to takle writing it yourself. Nor are you ready for gettng all counts for all columns in one query: we could help you to write it but you would not be able to maintain it.

To code a query generator that will generate all the queries for al the columns try something like this

$cols = array('rate_planing_process','rate_menu','rate_quality_of_food');

foreach ($cols as $col) {
   $sql = "SELECT $col, COUNT($col) as count FROM survey GROUP BY $col ORDER BY $col";
   $result = mysql_query($sql);
// etc to deal with the results
}

As I said you can get total count by summing each row count as you display it.

[whobutsb]

That is absolutely brillant. The top MySQL query pulls up exactly what I'm looking for. But I'm unsure how to implement the PHP code at the bottom 😕

At the moment I have it setup like this to get percentages of each of the 4 values

<?php $percentqualityFood_1 = $totalRows_qualityFood_1/$totalRows_qualityFood_all;
								printf("%.2f",$percentqualityFood_1 * $percenthun); ?>
                                    % </div></td>
                                <td class="bodycopy"><div align="left"> 
                                    <?php $percentqualityFood_2 = $totalRows_qualityFood_2/$totalRows_qualityFood_all;
								printf("%.2f",$percentqualityFood_2 * $percenthun); ?>
                                    % </div></td>
                                <td class="bodycopy"><div align="left"> 
                                    <?php $percentqualityFood_3 = $totalRows_qualityFood_3/$totalRows_qualityFood_all;
								printf("%.2f",$percentqualityFood_3 * $percenthun); ?>
                                    % </div></td>
                                <td class="bodycopy"><div align="left"> 
                                    <?php $percentqualityFood_4 = $totalRows_qualityFood_4/$totalRows_qualityFood_all;
								printf("%.2f",$percentqualityFood_4 * $percenthun); ?>

I'm sorry for being bothersome with this post. This really is my first baby steps into MySQL and PHP.

[Roger_Ramjet]

What version of mysql are you using?

[whobutsb]

I believe I'm using MySQL - 4.0.24

[Roger_Ramjet]

OK so you can't use rollup.

Now my other code was to run the count query for all the table columns, ie all the questions in the survey.

To calculate the percentages from the counts then, yes, just do it in php

One thing, is your survey set up that respondants have to answer all questions? If it is and if the values are validated before storing them then doing a simple query to count the rows in the table will save a lot of effort.

$sql = "SELECT COUNT(*) FROM survey";
$result = mysql_query($sql);
$numans = mysql_result($result, 0);
// give total number of rows in the table

// so now we can do it all in the query
 $sql = "SELECT rate_quality_of_food,  COUNT(rate_quality_of_food) AS count, (COUNT(rate_quality_of_food)/$numans) * 100 AS percent FROM survey GROUP BY rate_quality_of_food ORDER BY rate_quality_of_food"; 

Now you have the count and the percentage in the result row, just makes your code shorter and simpler.

[whobutsb]

Thanks so much for your help.

No the survey is setup where respondant doesn't need to answer all the questions. But we can easily change that so that they have to be all answered.

One more question now. How can I display the results from that SQL script using PHP?

[Roger_Ramjet]

whobutsb wrote:

Thanks so much for your help.

No the survey is setup where respondant doesn't need to answer all the questions. But we can easily change that so that they have to be all answered.

Or change the count query like this

 $sql = "SELECT COUNT(rate_quality_of_food) FROM survey"

Will return count of non-null rows ie those with an answer. Make sure the db is setup to allow nulls for answer columns with no answer.

To display it

while ($row // etc 
   echo '<tr><td>' . row['rate_quality_of_food'] . '</td><td>' . $row['count'] . '</td><td>' . $row['percent'] . '</td></tr>';
}