Adding results of individual rows

daemoncel

Thanks for all who can help me and offer help.. it is really appreciated

I have to get this done tonight, and i'm just stuck.

I need to be able to display the results of a survey and display them in a well formed table. (i.e. pretty)

I'm relatively new to this.

I can submit the results to a db and i can read the results of each individual row and show them in a table, but i can add up the total of a certain word from an individual column/row

(i.e. column is called pub_priv and submissions included either 'private' or "public")

I need to know who to display the total amount of submissions that included public or private (or any other word in the survey and survey and display it as one would display a poll

(i.e. Public - 201 %50 Private - 201 %50

and then show them along with their question.

TO get an idea of what this means, here is a link to the survey

survey

Here is my code. (temporary password included)

this one reports that the entry was submitted (survey is not submitting everything yet

 <?php 
error_reporting(E_ALL ^ E_NOTICE);
$db = mysql_connect("localhost","reforumc_temp","temp1234"); 
mysql_select_db ("reforumc_email"); 
$query = "INSERT INTO equinoxsurvery(id, pub_priv, size, scope, position, entrepreneur, more_entrep, percieve, attracted, entrep_approach, met, screens, behave, rewarded, offers, function)
VALUES('".$_POST['id']."','".$_POST['pub_priv']."',
'".$_POST['size']."','".$_POST['scope']."',
'".$_POST['position']."',
'".$_POST['entrepreneur']."',
'".$_POST['more_entrep']."',
'".$_POST['percieve']."',
'".$_POST['attracted']."',
'".$_POST['entrep_approach']."',
'".$_POST['met']."',
'".$_POST['screens']."',
'".$_POST['behave']."',
'".$_POST['rewarded']."',
'".$_POST['offers']."',
'".$_POST['function']."')"; 
$result = mysql_query($query); 
echo "Thank you for taking our survey."; ?>

THis displays each row that has been submitted.

<?php
$connection = mysql_connect ("localhost","reforumc_temp","temp1234") or die('Database not available');
mysql_select_db('reforumc_email');

$query = "SELECT * FROM equinoxsurvery";
$result = mysql_query($query) or die(mysql_error());

if (mysql_num_rows($result) > 0) {
   // echo "<table border=1>";
    //echo "<tr><td><b>Full Name</b></td><td> <b>Email Address</b></td></tr>";    

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

	$question0 = $row["id"];
    $question1 = $row["pub_priv"];
    $question2 = $row["size"];
    $question3 = $row["scope"];

    echo "<table border=1>";  
    echo "<tr colspan=4><td>$question0</td>
<td>$question1</td>
<td bgcolor=#E8EFF7>$question2</td>
<td>$question3</td></tr>";
        echo "</table>";
    }
}
else {
    echo 'No rows';
}
mysql_close ();
?>

EDIT!!! this is the code that is supposed to give me the total

<?php
$sql = "SELECT count(*) FROM equinoxsurvery WHERE pub_priv ='private'";
$result = mysql_query($query) or die(mysql_error());
$private_comp = $result1[0];
$sql = "SELECT count(*) FROM equinoxsurvery WHERE pub_priv ='public'";
$result = mysql_query($query) or die(mysql_error());
$public_comp = $result[0]; 

?>

this was given to me by someone at the sitepoint forums. but i did not know how to pull the content.

daemoncel

Anyone whom can help me with this?

Roger_Ramjet

What you are after is the agregate functions of a GROUP BY clause :
http://dev.mysql.com/doc/mysql/en/GROUP-BY-Functions.html

This will give you the count of each different value in the specified column. Thus your code would look something like this

<?php
$sql = "SELECT pub_priv, count(pub_priv) as count FROM equinoxsurvery GROUP BY pub_priv";
$result = mysql_query($query) or die(mysql_error());
while ($row = mysql_fetch_array($result)) {
echo '<tr><td>' . $row['pub_priv'] . '</td><td>' . $row['count'] . '</td></td>';
}

?>

this would give you a table row for each value in pub_priv with the entry and the count of that entry: most usefull when you don't know beforehand what values will actually occur and so can't code for them.

If you also want to get the sum of all counts then use the rollup modifier
http://dev.mysql.com/doc/mysql/en/GROUP-BY-Modifiers.htm

daemoncel

Originally posted by Roger Ramjet
What you are after is the agregate functions of a GROUP BY clause :
http://dev.mysql.com/doc/mysql/en/GROUP-BY-Functions.html

This will give you the count of each different value in the specified column. Thus your code would look something like this
<?php
$sql = "SELECT pub_priv, count(pub_priv) as count FROM equinoxsurvery GROUP BY pub_priv";
$result = mysql_query($query) or die(mysql_error());
while ($row = mysql_fetch_array($result)) {
echo '<tr><td>' . $row['pub_priv'] . '</td><td>' . $row['count'] . '</td></td>';
}

?>
this would give you a table row for each value in pub_priv with the entry and the count of that entry: most usefull when you don't know beforehand what values will actually occur and so can't code for them.

If you also want to get the sum of all counts then use the rollup modifier
http://dev.mysql.com/doc/mysql/en/GROUP-BY-Modifiers.htm [/B]

I think i understand and in testing that code that you provided i am getting the results of of my columnd "pub_priv" now what i don't understand is how to count the amount of times public or private appears.

Youre second link does not work, and i'm sure that will give me the answer i was looking for.

EDIT::
Your code as yours was giving me

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in C:\Accounts\reforumc\wwwRoot\survey\submited.php on line 189

So i combined you're while statement with this code

<?php 
// counts the total
$connection = mysql_connect ("localhost","reforumc_temp","temp1234") or die('Database not available');
mysql_select_db('reforumc_email');
$query = "SELECT * FROM equinoxsurvery";
$result = mysql_query($query) or die(mysql_error());
$sql = "SELECT pub_priv, count(pub_priv) as count FROM equinoxsurvery GROUP BY pub_priv";
$result = mysql_query($query) or die(mysql_error());
$private_comp = $result[0];
$sql = "SELECT count(*) FROM equinoxsurvery WHERE pub_priv ='public'";
$result = mysql_query($query) or die(mysql_error());
$public_comp = $result[0]; 
while ($row = mysql_fetch_array($result)) {
echo '<tr><td>' . $row['pub_priv'] . '</td><td>' . $row['count'] . '</td></td>'; 
}
?>

And i get this

total

Roger_Ramjet

Sorry about that, missed the 'l' off the end. should be http://dev.mysql.com/doc/mysql/en/GROUP-BY-Modifiers.html