joemal Posted July 3, 2014 Share Posted July 3, 2014 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. Quote Link to comment https://forums.phpfreaks.com/topic/289426-add-up-different-data-and-posting-in-total/ Share on other sites More sharing options...
fastsol Posted July 4, 2014 Share Posted July 4, 2014 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. Quote Link to comment https://forums.phpfreaks.com/topic/289426-add-up-different-data-and-posting-in-total/#findComment-1483745 Share on other sites More sharing options...
joemal Posted July 4, 2014 Author Share Posted July 4, 2014 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"> </td> <td><input name="submit" type="submit" id="submit" value="Edit Competitor"></td> <td> </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. Quote Link to comment https://forums.phpfreaks.com/topic/289426-add-up-different-data-and-posting-in-total/#findComment-1483793 Share on other sites More sharing options...
Jacques1 Posted July 4, 2014 Share Posted July 4, 2014 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 ; Quote Link to comment https://forums.phpfreaks.com/topic/289426-add-up-different-data-and-posting-in-total/#findComment-1483801 Share on other sites More sharing options...
joemal Posted July 4, 2014 Author Share Posted July 4, 2014 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; --- so if I lose the 'total' colum, what code do i replace on the table? and what gets put there? Quote Link to comment https://forums.phpfreaks.com/topic/289426-add-up-different-data-and-posting-in-total/#findComment-1483804 Share on other sites More sharing options...
Jacques1 Posted July 4, 2014 Share Posted July 4, 2014 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. Quote Link to comment https://forums.phpfreaks.com/topic/289426-add-up-different-data-and-posting-in-total/#findComment-1483808 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.