Page 1 of 1

Selecting Weekly Averages from MySQL table

Posted: Sun Dec 21, 2003 11:11 am
by Klaus2003
Hi Guys,

I need some help for a piece of PHP code.

I have a MySQL table (let's call it myweight) where I have the following columns: userid, signupdate, weight, currentdate.

What I need is a code that would calculate the weekly average weight of each user based on the signup date. I know I how to calculate the average weight for a user using the AVG() function in MySQL, but I'm stuck with finding a way to select, for each week since somebody has been a member, only the weight values from that week.

Thanks a lot for your help. :D

Klaus.

Posted: Sun Dec 21, 2003 12:17 pm
by Derfel Cadarn
I assume each user has a weight-value entered each week. In that case the SQL-query would be:

Code: Select all

$userid = $_POSTї'$userid']; // I assume you get the userid from a loginform
SELECT AVG(weight) FROM myweight WHERE userid=$userid

Posted: Sun Dec 21, 2003 1:23 pm
by Pyrite
Well there is 604800 seconds in a week. So you could select (supposing you're date is a timestamp) between the timestamp they signed up and 604800. And then just make it in intervals of that.

Selecting Weekly Averages from MySQL table

Posted: Sun Dec 21, 2003 4:59 pm
by Klaus2003
Thanks guys!

I like your idea, Pyrite. Could you be more specific as to how the select with the timestamp difference could be written? I am a beginner, so please bear with me.

The reason I need these weight averages is to plot them on a graph.

Thanks again.

Klaus