Page 1 of 1

doing calculations from values inserted into database

Posted: Tue Jun 13, 2006 11:55 pm
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. =)

Posted: Wed Jun 14, 2006 12:08 am
by feyd
You've got the numbers when the submission happens, do it then.

Posted: Wed Jun 14, 2006 12:08 am
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.

Posted: Wed Jun 14, 2006 1:58 pm
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.

Posted: Wed Jun 14, 2006 4:17 pm
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.

Posted: Wed Jun 14, 2006 10:36 pm
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?