PHP code for a mathematical operation

[tonbah]

I am new to php and mySQL. I have found alot of things online but am struggling with this. I am trying to create a record keeping system for my golf team. The players enter their scores on a webform and the data is stored in a mySQL database. The data while they are on the website is filtered by their user name. I can not figure out the next step.

I need to take their score, subtract the Course Rating, multiply by 113 and divide by course slope. (I can do this in the mysql admin program using:

UPDATE scores
SET diff = (Score-Rating)*113/Slope

Then I want to average all of the diff from the player and store it in the user database with their username. Then I want to take that number and mulitply it by.96 and store it in the user database as "handicp".

Each time I have tried to use AVG it either tells me that I am using grouping wrong or it outputs the info but I am not able to store it.

Is it possible to do this while the player is logged on to the website? Or could I do it as a series of commands in MySQL?

Thanks for any help,

Thomas

[XenoPhage]

[!--quoteo(post=352236:date=Mar 6 2006, 04:41 PM:name=tonbah)--][div class=\'quotetop\']QUOTE(tonbah @ Mar 6 2006, 04:41 PM) [snapback]352236[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Then I want to average all of the diff from the player and store it in the user database with their username. Then I want to take that number and mulitply it by.96 and store it in the user database as "handicp".

Each time I have tried to use AVG it either tells me that I am using grouping wrong or it outputs the info but I am not able to store it.

Is it possible to do this while the player is logged on to the website? Or could I do it as a series of commands in MySQL?
[/quote]

Code! I need code! :)

Seriously, post the code you're using so we can take a crack at fixing it.. :)

[tonbah]

[!--quoteo(post=352240:date=Mar 6 2006, 04:43 PM:name=XenoPhage)--][div class=\'quotetop\']QUOTE(XenoPhage @ Mar 6 2006, 04:43 PM) [snapback]352240[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Code! I need code! :)

Seriously, post the code you're using so we can take a crack at fixing it.. :)
[/quote]

Here is what I tried:

SELECT scores, users
UPDATE users SET avg_diff = AVG(scores.diff)
WHERE scores.ID =123456

MySQL said:

#1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'UPDATE users
SET avg_diff = AVG(scores.diff)
WHERE scores.ID=

OR



SQL query:

UPDATE users SET avg_diff = AVG(diff) WHERE ID =123456

MySQL said:

#1111 - Invalid use of group function

[XenoPhage]

[!--quoteo(post=352261:date=Mar 6 2006, 05:02 PM:name=tonbah)--][div class=\'quotetop\']QUOTE(tonbah @ Mar 6 2006, 05:02 PM) [snapback]352261[/snapback][/div][div class=\'quotemain\'][!--quotec--]
UPDATE users SET avg_diff = AVG(diff) WHERE ID =123456

MySQL said:

#1111 - Invalid use of group function
[/quote]

Yep, AVG() needs a GROUP BY ... Try this :

[code]
SELECT AVG(diff) FROM users WHERE ID=123456 GROUP BY ID;

and then

UPDATE users SET avg_diff = <answer from above> WHERE ID=123456;
[/code]

[tonbah]

[!--quoteo(post=352268:date=Mar 6 2006, 05:10 PM:name=XenoPhage)--][div class=\'quotetop\']QUOTE(XenoPhage @ Mar 6 2006, 05:10 PM) [snapback]352268[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Yep, AVG() needs a GROUP BY ... Try this :

[code]
SELECT AVG(diff) FROM users WHERE ID=123456 GROUP BY ID;

and then

UPDATE users SET avg_diff = <answer from above> WHERE ID=123456;
[/code]
[/quote]


Is there anyway to store the avg_diff and do both of these at the same time? or do I need to run one and then run the other for each user?

[XenoPhage]

[!--quoteo(post=352276:date=Mar 6 2006, 05:17 PM:name=tonbah)--][div class=\'quotetop\']QUOTE(tonbah @ Mar 6 2006, 05:17 PM) [snapback]352276[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Is there anyway to store the avg_diff and do both of these at the same time? or do I need to run one and then run the other for each user?
[/quote]

You might be able to do it with subselects, but I'm not positive. And for subselects you'll need at least MySQL 4.1... It's not that slow, so putting it in 2 queries shouldn't hurt much..

[wickning1]

Tell us more about your database. It sounds like your design may need work. How many tables do you have? What do they represent? How are you storing the score from a single round? How are you storing user information?

[tonbah]

[!--quoteo(post=352287:date=Mar 6 2006, 05:27 PM:name=wickning1)--][div class=\'quotetop\']QUOTE(wickning1 @ Mar 6 2006, 05:27 PM) [snapback]352287[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Tell us more about your database. It sounds like your design may need work. How many tables do you have? What do they represent? How are you storing the score from a single round? How are you storing user information?
[/quote]

I have two tables scores and users
the scores table has coulmns of auto_id, ID, First name, last name, date, course, rating, slope, score, and diff
the users table has columns of ID, First Name, Last name, username, password, avg_diff, and handicp

Each row in the scores table represnts one round by a player

[XenoPhage]

[!--quoteo(post=352366:date=Mar 6 2006, 10:02 PM:name=tonbah)--][div class=\'quotetop\']QUOTE(tonbah @ Mar 6 2006, 10:02 PM) [snapback]352366[/snapback][/div][div class=\'quotemain\'][!--quotec--]
I have two tables scores and users
the scores table has coulmns of auto_id, ID, First name, last name, date, course, rating, slope, score, and diff
the users table has columns of ID, First Name, Last name, username, password, avg_diff, and handicp

Each row in the scores table represnts one round by a player
[/quote]

Hrm.. the design sounds fairly solid. Although, I'd remove the first name and last name from the scores table as it's a duplication of data. If you need the name, just do a join when you select the data... Something like this :

SELECT u.firstname, u.lastname, s.date, s.score FROM scores AS s LEFT JOIN users AS u ON u.ID=s.ID;

Anyways, with the exception of that, the database looks pretty good. Give the dual SQL calls a shot and see if it's too slow. If it is, then you at least have something to start with, and you can make it better from there.. :)

[wickning1]

Assuming you are running MySQL 4.1 or greater, here's a query to update everyone's avg_diff (untested):

[code]UPDATE users u
LEFT JOIN (
   SELECT ID, AVG(diff) as avgdiff FROM scores GROUP BY ID
) s ON u.ID=s.ID
SET u.avg_diff=s.avgdiff[/code]