Auto Sum an integer field column to another field

Hello,
I have a MySQL database with 2 tables the second has 5 fields username password id(primary key) payment(int) sum(int), what I want in the field sum to auto insert the sum of the field payment when a new record is inserted eg recordcount 1 username=kjjk etc payment=100 sum=(100), 2 payment=100 sum=200(100+(recordcount2)100), I have found about the select and sum but it has nothing to do with the second field and I don't know hwere to use it in a php form or in MySQL.

Kyriakos
kyriakos70Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Roger BaklundCommented:
Two options:

1) Calculate it every time you need it.

2) Use triggers to update the column each time you add, delete or update the other table.

Option 1 is easiest, option 2 requires mysql5

http://dev.mysql.com/doc/refman/5.0/en/triggers.html

For option 1, you would use table strucure and queries like this:
# user: userid, username, password
# payments: userid, paydate, amount
 
select userid, username, sum(paymeny.amount) 
from user
left join payment on 
  payment.userid = user.userid
group by userid, username

Open in new window

0
Roger BaklundCommented:
Sorry, the query is wrong, because two columns are named 'userid' they must be prefixed with the alias:
select user.userid, username, sum(paymeny.amount) 
from user
left join payment on 
  payment.userid = user.userid
group by user.userid, username

Open in new window

0
kyriakos70Author Commented:
Can you give me an example code of the trigger, on how to sum a column to another column of the same table? I get an error on trigger. Or give me a hint on a query from the trigger below the <sum_player_pay> and <Player_pay> are fields of the same table or shall I have to create a new Table and add the sum to a field on the new table?
CREATE DEFINER = 'root'@'localhost' TRIGGER `user_payments_after_ins_tr` AFTER INSERT ON `user_payments`
FOR EACH ROW
BEGIN
INSERT INTO `user_payments` SET Sum_Player_pay = SUM(Player_pay);
END;

Open in new window

0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

Roger BaklundCommented:
Please read more about triggers. It is important that you understand how they work, if you are going to use them. If you are a new to databases, you should probably go for option 1...

To be able to help you with details, you have to show your database schema. Execute

SHOW CREATE TABLE user_payments;

and the same for the other table you have, and post the output here.
0
kyriakos70Author Commented:
OK,
I will go to option1, thank you :
# Structure for the `user_details` table :
#

CREATE TABLE `user_details` (
  `id` INTEGER(6) NOT NULL AUTO_INCREMENT,
  `Name` CHAR(12) COLLATE greek_general_ci DEFAULT NULL,
  `Surname` CHAR(20) COLLATE greek_general_ci DEFAULT NULL,
  `E-mail` CHAR(20) COLLATE greek_general_ci DEFAULT NULL,
  `Username` CHAR(20) COLLATE greek_general_ci DEFAULT NULL,
  `Password` INTEGER(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)
)ENGINE=InnoDB
AUTO_INCREMENT=2 CHARACTER SET 'greek' COLLATE 'greek_general_ci'

Structure for the `user_payments` table :
CREATE TABLE `user_payments` (
  `id_2` INTEGER(11) NOT NULL AUTO_INCREMENT,
  `id` INTEGER(11) NOT NULL,
  `date` DATE DEFAULT NULL,
  `Player_pay` INTEGER(6) NOT NULL,
  `Sum_Player_pay` INTEGER(11) DEFAULT NULL,
  PRIMARY KEY (`id_2`),
  UNIQUE KEY `id_2` (`id_2`),
  UNIQUE KEY `id` (`id`),
  UNIQUE KEY `id_3` (`id`),
  CONSTRAINT `id` FOREIGN KEY (`id`) REFERENCES `user_details` (`id`)
)ENGINE=InnoDB
AUTO_INCREMENT=1 CHARACTER SET 'latin1' COLLATE 'latin1_swedish_ci'
0
Roger BaklundCommented:
When you need a list of users with the sum of their payments, use a query like below. I added first payment, last payment, payment average, smallest/largest payment and number of payments. Just remove those you do not need.

You can add a where clause. For instance, to get this information for only one user, add WHERE id=<id>

The Sum_Player_pay column is not needed.
SELECT u.id,Name,Surname,`E-mail`,Username,Password,
  SUM(p.Player_pay) AS 'Total_payment',
  MIN(p.date) AS 'First_payment',
  MAX(p.date) AS 'Last_payment',
  AVG(p.Player_pay) AS 'Average_payment',
  MIN(p.Player_pay) AS 'Smallest_payment',
  MAX(p.Player_pay) AS 'Largest_payment',
  count(p.id_2) AS 'Payment_count'
FROM user_details u 
LEFT JOIN user_payments p ON
  p.id=u.id
GROUP BY 1,2,3,4,5,6 # refers to the 6 'normal' fields in the select

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kyriakos70Author Commented:
I will accept your solution thanks this is what I am looking for only one thing where do I put this query in a new query of the database? or in a php code, I want it to be executed automaticly.

Kyriakos
0
kyriakos70Author Commented:
Thank you what I needed
0
Roger BaklundCommented:
Put it where you need it...? What do you mean you want it executed automatically? It does not update anything, it calculates the current total payment for each player.

You could put it in the database, storing it as a view. That would make it easy to use when you need it:

http://dev.mysql.com/doc/refman/5.1/en/create-view.html
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.