php and mysql query
Posted on 2007-04-08
I am trying to write a query that will extract some 2 separate data from two tables, multiply them together , store it in another table and diplay it. This is the basic structure of my tables.
1. I have a table called REPMAX which contains
(so each member has a repmax weight for each exercise)
2. I have a table called WORKOUT which has
(basically this table contains the chart for what a member would be doing each day. the chart looks like this
level phase day percentage sets reps
1 1 1 50% 1 10
1 1 2 45% 1 8
1 1 3 45% 1 8
3. I have the table called HISTORY which will basically have sessionnumber, date, the level, phase, day, weight (the repmax multiplied by the percentage from the day), the reps and the sets.
So, that we can get the right percentage from the right day, i want a way to detect which day the member is currently on, query the percentage from that day multiply it by the repmax and store it in the WORKOUT table along with the sets and reps from that day and display it to the user. So when the user clicks a button with says "show me my workout for today" all this will take place behind the scenes and it will show them data from the workout table. And then maybe if they logout, that data for the day is then stored in the HISTORY table with a date, and sessionnumber(auto increment). I hope this makes sense. if you think this is not a good design, I am open to any suggestions which will simplify this. Thanks