# Calculating a running total using database values

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

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.

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.
\$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

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>
``````

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