How to get a total of record values

[dmacman]

Hi All,

I have a loop setup to get al the records form my table, but I want to get a total (of all the values) of each field. This is for poll I designed.

Here are my table layouts.

1st table (for the names)
|----ID----|-------------Name------------|
|-----1----|--------Adam Sandler--------|
|-----2----|---Arnold Schwarzenegger--|



2nd table (for the votes)
|-----VotesID----|----NamesID---|---fav_count--|---vg_count---|---g_count---|---fp_count--|--nh_count--|
|---------1--------|-------1---------|--------1-------|-------0--------|------0--------|------0-------|------0-------|
|---------2--------|-------2---------|--------0-------|-------0--------|------0--------|------0-------|------1-------|
|---------3--------|-------1---------|--------0-------|-------1--------|------0--------|------0-------|------0-------|


So I wrote this to get all the names and votes, join them on the NamesID=ID and echo the rows out. But I get every vote, and what I want is to get a total for all the names.

IE, above, Adam Sandler got a total for all votes (2 total votes)...
|-----VotesID----|----NamesID---|---fav_count--|---vg_count---|---g_count---|---fp_count--|--nh_count--|
|---------1--------|-------1---------|--------1-------|-------1--------|------0--------|------0-------|------0-------|

Here is my code for the results...

[code]$localhost = 'localhost';
$username = 'x';
$password = 'x';
$database = 'x';
mysql_connect($localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");

echo '<table><tr>
<td width="150">Name</td>
<td width="100">Favorite</td>
<td width="100">Very Good</td>
<td width="100">Good</td>
<td width="100">Fair</td>
<td width="100">Never Heard</td>
<td width="100">Total Count</td>
<td width="100">QRating</td></tr>';

$query= "SELECT * FROM QRatingVotes2, QRatingNames WHERE QRatingVotes2.NamesID=QRatingNames.ID";
    $result=mysql_query($query);    
        while($celeb = mysql_fetch_array($result)) {
            $name = $celeb['Name'];
            $fav_count = $celeb['fav_count'];
            $vg_count = $celeb['vg_count'];
            $g_count = $celeb['g_count'];
            $fp_count = $celeb['fp_count'];
            $nh_count = $celeb['nh_count'];
            $total_count = $fav_count + $vg_count + $g_count + $fp_count;
            $qRating = round((($fav_count/$total_count)*100),2);

            echo '<tr>';
            echo '<td>'.$name.'</td>';
            echo '<td>'.$fav_count.'</td>';
            echo '<td>'.$vg_count.'</td>';
            echo '<td>'.$g_count.'</td>';
            echo '<td>'.$fp_count.'</td>';
            echo '<td>'.$nh_count.'</td>';
            echo '<td>'.$total_count.'</td>';
            echo '<td>'.$qRating.'%</td>';
            echo '</tr>';
                }
echo  '</table>';

mysql_close(); [/code]


I know I need a count(*), but I can't get it to work.

I appreciate the help,

Don

[SharkBait]

Try something like:

SELECT * , COUNT(*) FROM Votes GROUP BY NamesID

??

Of course then you would reference the other table to find out who the NamesID belongs to

[dmacman]

Hi SharkBait,

I tried what you suggested and GROUP BY Name and for both, I just get the first record, not the total for all the records.

I have tried about 4 or 5 variations of count..

[code]$fav_count = $celeb[count('fav_count')];[/code]

[code]$fav_count = count($celeb['fav_count']);[/code]

[code]$fav_count = $celeb['fav_count'];
$total_fav = count($fav_count[0]);[/code]

And non of them work.

Any more ideas?

Thanks,
Don

[jworisek]

the problem is you dont want to count, you want to SUM the data. Counting will increment for each row regardless of the data.

try this:

[code]
$query= "SELECT QRatingNames.NamesID, SUM(fav_count+vg_count+g_count+fp_count+nh_count) as total_count FROM QRatingVotes2, QRatingNames WHERE QRatingVotes2.NamesID=QRatingNames.ID group by QRatingNames.NamesID";
[/code]

[dmacman]

Hi jworisek.

Thanks for replying, and I think you are close.

I had to change your query, for QRatingNames.NamesID to QRatingNames.ID. But now I get a warning:division by zero.

So I think it is something small in the query that is not producing a result.

Also, this...
[!--quoteo--][div class=\'quotetop\']QUOTE[/div][div class=\'quotemain\'][!--quotec--]
SUM(fav_count+vg_count+g_count+fp_count+nh_count) as total_count[/quote]

Will that only give the me total for all fields?

I need the total for each field and the then I can total all of them. (I need individual field totals for stats and to do the math for the final QRating)

Thanks,

Don

[dmacman]

I think I resolved this.

Here is my new code that works.

[code]
$query= "SELECT QRatingNames.Name, QRatingNames.ID, SUM(fav_count) as total_fav_count, SUM(vg_count) as total_vg_count, SUM(g_count) as total_g_count, SUM(fp_count) as total_fp_count, SUM(nh_count) as total_nh_count FROM QRatingVotes2, QRatingNames WHERE QRatingVotes2.NamesID=QRatingNames.ID group by QRatingNames.ID";
    $result=mysql_query($query);    
        while($celeb = mysql_fetch_array($result)) {
            $name = $celeb['Name'];
            $fav_count = $celeb['total_fav_count'];
            $vg_count = $celeb['total_vg_count'];
            $g_count = $celeb['total_g_count'];
            $fp_count = $celeb['total_fp_count'];
            $nh_count = $celeb['total_nh_count'];
[/code]

The rest did not change. This gives me a total count for each field, ( I can then do the math required) and displays them row by row like I wanted.

Thanks everyone, hope this helps someone else out.

Don

[dmacman]

I resolved the form for this QRating and I resolved the Lookup of data, but I have not been able to take the data and insert it into my tables (see above tables).

Here is my form...

[code]if (!isset($_SESSION['names'])) {
    $db_server = 'localhost';
    $user      = '1';
    $password  = '1';
    $database = 1';
    $conn = mysql_connect ($db_server, $user, $password);
    @mysql_select_db($database) or die( "Unable to select database");
    if ($conn === false) {
        die('Connection to "' . $db_server . '" failed: ' . mysql_error() . '<br />');
    }
    $sql = "SELECT * FROM QRatingNames";
    $result = mysql_query($sql, $conn) or die(mysql_error());
    while ($row = mysql_fetch_array($result)) {
        $_SESSION['names'][] = $row['Name'];
        $_SESSION['checked'][$row['Name']] = 0;
    }
}

if (!isset($_POST['submit'])) {
    echo '<form action="' . $_SERVER['PHP_SELF'] . '" method="POST">' .
          '<table width="647" border="1" align="center" class="font"><br />
    <tr><td colspan="6"><div align="center" class="fontbold">TEACHER SURVEY <br />
      Take   the survey below to tell us which personalities you would like to see in our   future video productions for use in your classroom.  If you would like to ask   your students to participate, please direct them to the “Student” section of our   website, where they can access a survey set up especially for them by clicking   on “Vote for Your Favorite Celebrities.” <br />
      Thank you for your   participation! </div></td>
    </tr>
  <tr>
  <td width="147">Name</td>
    <td width="100"><div align="center">One of My Favorites!</div></td>
    <td width="100"><div align="center">Very Good Personality</div></td>
    <td width="100"><div align="center">Good Personality</div></td>
    <td width="100"><div align="center">Fair - Poor Personality</div></td>
    <td width="100"><div align="center">I\'ve Never Heard of this Person</div></td>
  </tr>';
    foreach ($_SESSION['names'] as $star) {
        echo '<tr><td width="147">' . $star . '</td>';
        for ($rating = 1; $rating <= 5; $rating++) {
            echo '<td width="118"><input name="N[' . $star . ']" type="radio" ';
            if ($rating == $_SESSION['checked'][$star]) {
            }
            echo 'value="' . $rating . '" /></td>';
        }
        echo '</tr>';
    }
    echo '<tr><td colspan="6">' .
    '<input type="submit" name="submit" value="Submit" />' .
    '<input name="Reset" type="reset" value="Reset" />' .
    '<input name="QRatingID" type="hidden" value="1" />' .
         '</td></tr></table></form>';
} else {
    if (!isset($_POST['N'])) {
        die ('<p>You did not vote for anyone, please go back and vote.</p>');
    } elseif (count($_POST['N']) !== count($_SESSION['names'])) {
        die ('<p>You did not vote for all the celebrities, please go back and finish voting.</p>');
        foreach ($_SESSION['names'] as $key => $value) {
            if (isset($_POST['N'][$value])) {
                $_SESSION['checked'][$value] = $_POST['N'][$value];
            } else {
                $_SESSION['checked'][$value] = 0;
            }
        }
    } else {
        echo '<meta http-equiv="Refresh" content="0; URL=processed.php">';
        // do_stuff();
    }
} [/code]

I am storing the data in my QRatingVotes2 table as such. (for each celebs name)...
[!--quoteo--][div class=\'quotetop\']QUOTE[/div][div class=\'quotemain\'][!--quotec--]
|-----VotesID----|----NamesID---|---fav_count--|---vg_count---|---g_count---|---fp_count--|--nh_count--|
|---------1--------|-------1---------|--------1-------|-------0--------|------0--------|------0-------|------0-------|
|---------2--------|-------2---------|--------0-------|-------0--------|------0--------|------0-------|------1-------|
|---------3--------|-------1---------|--------0-------|-------1--------|------0--------|------0-------|------0-------|
[/quote]

Does anyone have any guidance on this?

Thanks,
Don