doing calculations from values inserted into database

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
thania
Forum Newbie
Posts: 11
Joined: Sun Mar 12, 2006 9:19 pm

doing calculations from values inserted into database

Post by thania »

Hi all.

I am using radio buttons to capture inputs from user in my HTML form. Every radio button holds an integer value which will be inserted into MySQL database. I actually want to sum all of the values captured from user inputs and put it as TOTAL in my database. For this time, I just finished inserting the values into the database and dont have any idea of how to do the summation of the values captured. Another thing is that can I do the summation right after the user clicks the Submit button after they have fill in all the radio button, and insert the values of the summation into the database together with each value of the radio buttons. Or, should I insert each value of radio button first, and do the summation after that, which means I have to call the values of each radio button from the database before doing the summation.


Any idea of how to do it? I really appreciate any help and guidance on how to do it. Thank you so much. =)
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

You've got the numbers when the submission happens, do it then.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Post by Christopher »

You can get the total of all of the numbers in a column in a data table using the SUM() function in SQL. Something like:

Code: Select all

SELECT SUM(myfield) AS myfield_total FROM mytable
You really don't need to story the total if you can calculate it when needed. The problem with storing totals is that it is very possible for two queries to overlap and make the number incorrect because there is a read and a write -- between which another query can do a read or a write.
(#10850)
thania
Forum Newbie
Posts: 11
Joined: Sun Mar 12, 2006 9:19 pm

Post by thania »

Thank you for both of you who respond to me. I have successfully done the summation before the values are inserted into the database. And now I am facing with another problem which is how to track the user who answered the questions before. In my project, when a user log in into the system, then they will have to give some inputs through the radio buttons. From there, the summation of integer values they collect from the inputs submitted is counted and recorded into the database. I then have to track who is the user who has given the input, based on their username used when they log in. Do I have to record the username into the table which I use to store the total values, or there is any other way to do it? I also used SESSION in my login page. But how can I used the value for the session to be stored into the database?

Thank you for any help. I really appreciate it.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

If you have a users' table, store the user's ID number, often the table's primary key, in a field along with their answers.
thania
Forum Newbie
Posts: 11
Joined: Sun Mar 12, 2006 9:19 pm

Post by thania »

I have already inserted the username into the useraccount table, which is when they register with the system. And the table that contains the total marks is inserted into the second table. Then, how am I going to insert the username too into the second table? This is the piece of codes which I use to enter the value of the user inputs into the second table, namely "evaluation", and the php file is "evaluation2.php"

Code: Select all

<?php

include ("connect2.php"); 

if (isset($_POST['Submit'])) {
submitAnswer();
}

function submitAnswer(){
	global $database; 
  	global $table;
	global $errCon;

mysql_select_db($database) or die($errCon . mysql_error()); 


$A1 = $_POST['Q1'];
$A2 = $_POST['Q2'];
$A3 = $_POST['Q3'];
$A4 = $_POST['Q4'];
$A5 = $_POST['Q5'];
$A6 = $_POST['Q6'];
$A7 = $_POST['Q7'];
$A8 = $_POST['Q8'];
$A9 = $_POST['Q9'];
$A10 = $_POST['Q10'];
$A11 = $_POST['Q11'];
$A12 = $_POST['Q12'];
$A13 = $_POST['Q13'];
$A14 = $_POST['Q14'];
$A15 = $_POST['Q15'];
$A16 = $_POST['Q16'];

$ATotal = $A1 + $A2 + $A3 + $A4 + $A5 + $A6 + $A7 + $A8 + $A9 + $A10 + $A11 + $A12 + $A13 + $A14 + $A15 + $A16;

//Insert answers into database


$query = "INSERT INTO $table (Event,Position,Dept,Task,SemBreak, Duration, OtherTask, TeamWork, Interpersonal, Purpose, Responsibility, Development, Success, Disturb, Satisfaction, Interest, Total) VALUES ('$A1', '$A2','$A3','$A4', '$A5', '$A6','$A7', '$A8', '$A9', '$A10','$A11','$A12','$A13', '$A14', '$A15', '$A16', '$ATotal') ";
$result = mysql_query($query); 

if (!mysql_query($query)){
		print (mysql_error());
		}
	else {
		echo  "Data Inserted!"; 
		
		}
	
}
	
		
?>
Is this where we use session? but I still don't understand on how to implement session, to update other table, which in my case, is the table "evaluation". How to make it 'bring' the username into the second page and store the username along in the second table?
Post Reply