Jump to content


Photo

Add up different data and posting in total


  • Please log in to reply
5 replies to this topic

#1 joemal

joemal

    Member

  • New Members
  • PipPip
  • 11 posts

Posted 03 July 2014 - 03:52 PM

Hi,

 

I have a database which holds results

 

 

Name | Car | etc \ etc | Round 1  |  round 2   | round 3 // etc  | Total

 

the points are in the format of;

e.g.

100.5  // 25.6  // 35.8  // 50.0

 

I input the data manually using a webpage, clicking on the relevant driver, update scores, click submit.  I have to manually work out the total, is there a way I can it it automatically add the values from round 1,2,3, etc.



#2 fastsol

fastsol

    Advanced Member

  • Members
  • PipPipPip
  • 491 posts
  • LocationMinnesota

Posted 03 July 2014 - 07:14 PM

Well PHP is very good a doing math, but you're going to have to give us a bit more to go on than what you have.  How about an example of what you're currently doing and the result you expect to get from it.


You never know everything, I learn everday!

http://amecms.com

http://amewebdesigns.com


#3 joemal

joemal

    Member

  • New Members
  • PipPip
  • 11 posts

Posted 04 July 2014 - 05:26 AM

cap1.png  

This is the form i fill out to add the data to the database.

 

The code looks like;

  <form action="execs/edit_cautotest.php" method="post" name="edit_cautotest" id="edit_cautotest">
  <?php
		$id = $_GET['id'];
		$result = mysql_query("SELECT * FROM cautotest WHERE id = '$id'");
		

		if(mysql_num_rows($result) == 0) { echo 'No results founds'; } else {
		
		while($row = mysql_fetch_array($result))
		
			{
			
			$db_id = $row['id'];
			$db_competitor = $row['competitor'];
			$db_sex = $row['sex'];
			$db_memberno = $row['memberno'];
			$db_skill = $row['skill'];
			$db_class = $row['class'];
			$db_vehicle = $row['vehicle'];
			$db_bracket = $row['bracket'];
			$db_r1 = $row['r1'];
			$db_r2 = $row['r2'];
			$db_r3 = $row['r3'];
			$db_r4 = $row['r4'];
			$db_r5 = $row['r5'];
			$db_total = $row['total'];
			
			
			echo <<<END
    <table width="674" border="0" align="center" cellpadding="0" cellspacing="0">
<tr>
            <td width="217" class="col_right">REF ID :</td>
            <td width="205" class="col_left"><input name="id" type="text" id="id" value="$db_id" readonly="readonly"></td>
            <td width="252" class="col_left"></td>
          </tr>
<tr>
            <td width="217" class="col_right">Competitor :</td>
            <td width="205" class="col_left"><input name="competitor" type="text" id="competitor" value="$db_competitor"></td>
            <td width="252" class="col_left"></td>
          </tr>
          <tr>
            <td width="217" class="col_right">Sex :</td>
            <td width="205" class="col_left"><input name="sex" type="text" id="sex" value="$db_sex"></td>
            <td width="252" class="col_left"></td>
          </tr>
          <tr>
            <td width="217" class="col_right">Membership No :</td>
            <td width="205" class="col_left"><input name="memberno" type="text" id="memberno" value="$db_memberno"></td>
            <td width="252" class="col_left"></td>
          </tr>
          <tr>
            <td width="217" class="col_right">Skill :</td>
            <td width="205" class="col_left"><input name="skill" type="text" id="skill" value="$db_skill"></td>
            <td width="252" class="col_left"></td>
          </tr>
          <tr>
            <td class="col_right">Class  :</td>
            <td class="col_left"><input name="class" type="text" id="class" value="$db_class"></td>
            <td class="col_left"></td>
          </tr>
          <tr>
            <td class="col_right"><label>Vehicle : </label></td>
            <td class="col_left"><input name="vehicle" type="text" id="vehicle" value="$db_vehicle"></td>
            <td class="col_left"></td>
          </tr>
          <tr>
            <td class="col_right"><label>Bracket : </label></td>
            <td class="col_left"><input name="bracket" type="text" id="bracket" value="$db_bracket"></td>
            <td class="col_left"></td>
          </tr>
         <tr>
            <td class="col_right"><label>Round 1 : </label></td>
            <td class="col_left"><input name="r1" id="r1" value="$db_r1"></td>
            <td class="col_left"></td>
          </tr>
          <tr>
            <td class="col_right"><label>Round 2 : </label></td>
            <td class="col_left"><input name="r2" id="r2" value="$db_r2"></td>
            <td class="col_left"></td>
          </tr>
          <tr>
            <td class="col_right"><label>Round 3 : </label></td>
            <td class="col_left"><input name="r3" id="r3" value="$db_r3"></td>
            <td class="col_left"></td>
          </tr>
          <tr>
            <td class="col_right"><label>Round 4 : </label></td>
            <td class="col_left"><input name="r4" id="r4" value="$db_r4"></td>
            <td class="col_left"></td>
          </tr>
          <tr>
            <td class="col_right"><label>Round 5 : </label></td>
            <td class="col_left"><input name="r5" id="r5" value="$db_r5"></td>
            <td class="col_left"></td>
          </tr>
          <tr>
            <td class="col_right"><label>Total : </label></td>
            <td class="col_left"><input name="total" id="total" value="$db_total"></td>
            <td class="col_left"></td>
          </tr>
          <tr>
            <td class="col_right">&nbsp;</td>
            <td><input name="submit" type="submit" id="submit" value="Edit Competitor"></td>
            <td>&nbsp;</td>
         </tr>
        </table>
END;
} }?>

I would like the total field to automatically calculate the values from 'round 1, 2, 3, 4, 5'

 

Thanks - if you need more info just ask.



#4 Jacques1

Jacques1

    Advanced Member

  • Members
  • PipPipPip
  • 950 posts

Posted 04 July 2014 - 05:53 AM

And why exactly do you think you need a column to hold the total? Just calculate it with MySQL when needed:

SELECT
	r1 + r2 + r3 + r4 + r5 AS total    -- this is bad design
FROM
	cautotest
;

However, the poor database design already shows in this simple query: When you have a group of values, you do not create a column for each one. This violates the First Normal Form. A dead giveaway for this problem is that you start numbering your columns.

 

Instead, you create an extra table to store the values as rows. In your case, you'd have something like this:

ref_id | round | points
-------+-------+-------
2      | 1     | 90.0
2      | 2     | 412.0

The huge advantage is that doing calculations accross the rows becomes trivial, and you can change the number of rounds at any time.

 

Now the query is as simple as this:

SELECT
	ref_id
	, SUM(points) AS total
FROM
	cautotest
GROUP BY
	ref_id
;


#5 joemal

joemal

    Member

  • New Members
  • PipPip
  • 11 posts

Posted 04 July 2014 - 06:04 AM

The coding of the page that displays the results to the public looks like this;

<?php
$cautotest_results = mysql_query("SELECT * FROM cautotest");
if(mysql_num_rows($cautotest_results) == 0) { echo "<p>No Results Available."; } else {

echo "<table width=\"800\" border=\"0\" cellpadding=\"2\" cellspacing=\"2\" class=\"greywritinglight\" align=\"center\">
<tr align=\"center\">
<td>Competitor</td>
<td>Vehicle</td>
<td>Class</td>
<td>Skipton</td>
<td>Weston</td>
<td>Normans</td>
<td>Norwood</td>
<td>Uniroyal</td>
<td>Total</td>
</tr>";

$x=1;
while($results_row = mysql_fetch_array($cautotest_results))
{
			if($x%2): $rowbgcolor = "#FFFFFF"; else: $rowbgcolor = "#EEEEEE"; endif;
echo "<tr align=\"center\" bgcolor=\"" .$rowbgcolor. "\">";
echo "<td>" . $results_row['competitor'] . "</td>";
echo "<td>" . $results_row['vehicle'] .  "</td>";
echo "<td>" . $results_row['class'] . "</td>";
echo "<td>" . $results_row['r1'] . "</td>";
echo "<td>" . $results_row['r2'] . "</td>";
echo "<td>" . $results_row['r3'] . "</td>";
echo "<td>" . $results_row['r4'] . "</td>";
echo "<td>" . $results_row['r5'] . "</td>";
echo "<td>" . $results_row['total'] . "</td>";
echo "</tr>";
$x++;
}
echo "</table>";
}
?>

which is;

resultpage.png

 

---

 

so if I lose the 'total' colum, what code do i replace on the table? and what gets put there?



#6 Jacques1

Jacques1

    Advanced Member

  • Members
  • PipPipPip
  • 950 posts

Posted 04 July 2014 - 06:18 AM

Like I said, you just calculate the total when needed. There's absolutely no reason for physically storing it.

 

You do realize that MySQL can do more than just “SELECT *”, right? You can do all kinds of calculations and data aggregation within the query.






0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

Cheap Linux VPS from $5
SSD Storage, 30 day Guarantee
1 TB of BW, 100% Network Uptime

AlphaBit.com