Calculating a running total using database values

PHP
1

In building a registration form for the upcoming trade show, we gave our sales people a a 4 digit code, for when they register contractors/DIY(ers), etc for the shows. In giving the sales people a “code”, this allows our sales people to keep track of how many people they have a registered.

Currently when the sales people check their stats, they can see all the companies they registered and how many people from each company they’ve registered. However they can’t see a running a total of how many people/company(s) they’ve have registered, and this is what I’m currently struggling with.

Below is a screenshot of database:

Image removed as it contained customer data

Below is a screen shot of the employee stats

1663×908 127 KB

The code below, indicates to the employee of how many company(s)/people and how many people from the those companies they’ve registered.

   <table width="875px" border="1">
         <tr>
             <td width="245px">Edmonton - 0000</td>
             <td width="614px">
                   <?php
    
                  // Make a MySQL Connection
                  $query = "SELECT company, COUNT(company) FROM Registration WHERE code='0000' && event='Edmonton, AB - March 14 - (11 A.M. - 2 P.M)' GROUP BY company";
    	 
                  $result = mysql_query($query) or die(mysql_error());
    
                 // Print out result
                 while($row = mysql_fetch_array($result)){
    	                  echo stripslashes($row['COUNT(company)']) ." from ". stripslashes($row['company']) . "";
    	                  echo "<br />";
                 }
              ?>
         </td>
       </tr>
   </table>

In addition to this, I was trying to add up the running total of all the companies/people combined. This is where I’m struggling to get the companies to total.

<table width="875px" border="1">
    <tr>
      <td width="245px">Edmonton - 0000
        <br/>
         <?php 
              $company = $company;
              $code ='0000';
              $total = $code + $company;
        
              echo "total amound of registrations:" . $total; 
         ?>
      </td>
      <td width="614px">
          <?php
          
            // Make a MySQL Connection
            $query = "SELECT company, COUNT(company) FROM Registration WHERE code='0000' && event='Edmonton, AB - March 14 - (11 A.M. - 2 P.M)' GROUP BY company";
            	 
            $result = mysql_query($query) or die(mysql_error());

            // Print out result
            while($row = mysql_fetch_array($result)){
            	echo stripslashes($row['COUNT(company)']) ." from ". stripslashes($row['company']) . "";
            	echo "<br />";
            }

            ?>
        </td>
    </tr>
</table>

If someone could tell me where I’m going wrong in trying to find calculate a running total, or if there is a better way to do this rather than using PHP Arithmetic Operators. This running total doesn’t need to be stored into a database, it just needs to be displayed.

Thanks in advance!

2

I personally would rather use arithmetic operators, a single PDO queriy with bind input above html than multiple mysql within content.

<?php 
$host = "localhost"; 
//MySQL Database user name.    
$login = "";
//Password for MySQL.
$dbpass = "";
//MySQL Database name.
$dbname = ""; 
//Establish a connection
$db = new PDO("mysql:host=localhost;dbname=$dbname", "$login", "$dbpass"); 

$code = '0000';
$event = "Edmonton, AB - March 14 - (11 A.M. - 2 P.M)";

$sql = "SELECT company, COUNT(company) FROM Registration WHERE code = :code AND event = :event GROUP BY company";
    $query = $db->prepare($sql);
    $query->bindParam(":code", $code);
    $query->bindParam(":event", $event);
    $query->execute();
    $data = array();
    $datatotal = 0;
    while($row = $query->fetch(PDO::FETCH_ASSOC)){
        $data[$row['company']] = $row['COUNT(company)'];
        $datatotal += $row['COUNT(company)']; 
    }
        
?>
<html>
<body>
<table width="875px" border="1">
    <tr>
      <td width="245px"><?php echo $event . ' - ' . $code;?>
        <br/>
        <?php
        $total = str_pad($datatotal, 4, "0", STR_PAD_LEFT);;
        
        echo "Total amount of registrations: " . $total; 
        ?>
      </td>
      <td width="614px">
        <?php
        // Print out result
        foreach($data as $company => $count){
            echo stripslashes($count) ." from ". stripslashes($company) . "";
            echo "<br />";
        }
        ?>
        </td>
    </tr>
</table>
</body>
</html>
3

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.