PinkFloyd

Hellow, I'm having trouble with the following : I'm creating an "average shot" site for archers. It runs on a database including a single table with a unique row-ID, person, date and # shots. Each member sees (using his login info) his own # shots per day/week/month/year, and for each of those categories, an average. Now I want to make an overview of all the members, including their name, average this week, average this month, total last month, total this month and total this year. So I have a general recordset getting me the existing users. Then, for one user, I have recordsets getting/calculating the different results. These, however, now display the same results for everyone (those of the first person on the list). How can I get these recordsets to display a result for each existing record in the first recordset ? ===================== Recordsets : <?php mysql_select_db($database_TIR_AVG_MEM, $TIR_AVG_MEM); $query_all_archers = sprintf("SELECT * FROM tir_avg_mem GROUP BY TPERSON ORDER BY TPERSON ASC, TDATE DESC", $colname_all_archers); $all_archers = mysql_query($query_all_archers, $TIR_AVG_MEM) or die(mysql_error()); $row_all_archers = mysql_fetch_assoc($all_archers); $totalRows_all_archers = mysql_num_rows($all_archers); mysql_select_db($database_TIR_AVG_MEM, $TIR_AVG_MEM); $query_all_archers_tot = sprintf("SELECT SUM(TSHOTS) AS TotalShots FROM tir_avg_mem WHERE TPERSON = '" . $row_all_archers['TPERSON'] . "'", $colname_all_archers_tot); $all_archers_tot = mysql_query($query_all_archers_tot, $TIR_AVG_MEM) or die(mysql_error()); $row_all_archers_tot = mysql_fetch_assoc($all_archers_tot); $totalRows_all_archers_tot = mysql_num_rows($all_archers_tot); mysql_select_db($database_TIR_AVG_MEM, $TIR_AVG_MEM); $query_all_archers__avg_w = sprintf("SELECT WEEK(TDATE, 5) AS TWEEK, MONTH(TDATE) AS TMONTH, YEAR(TDATE) AS TYEAR, SUM(TSHOTS) AS TSHOTS_W FROM tir_avg_mem WHERE TPERSON = '" . $row_all_archers['TPERSON'] . "' GROUP BY TWEEK ORDER BY TDATE DESC", $colname_all_archers_avg_w); $all_archers__avg_w = mysql_query($query_all_archers__avg_w, $TIR_AVG_MEM) or die(mysql_error()); $row_all_archers__avg_w = mysql_fetch_assoc($all_archers__avg_w); $totalRows_all_archers__avg_w = mysql_num_rows($all_archers__avg_w); mysql_select_db($database_TIR_AVG_MEM, $TIR_AVG_MEM); $query_all_archers__avg_m = sprintf("SELECT WEEK(TDATE, 5) AS TWEEK, MONTH(TDATE) AS TMONTH, YEAR(TDATE) AS TYEAR, SUM(TSHOTS) AS TSHOTS_M FROM tir_avg_mem WHERE TPERSON = '" . $row_all_archers['TPERSON'] . "' GROUP BY TMONTH ORDER BY TDATE DESC", $colname_all_archers_avg_m); $all_archers__avg_m = mysql_query($query_all_archers__avg_m, $TIR_AVG_MEM) or die(mysql_error()); $row_all_archers__avg_m = mysql_fetch_assoc($all_archers__avg_m); $totalRows_all_archers__avg_m = mysql_num_rows($all_archers__avg_m); $curdate = getdate(); if ($curdate['mon'] = 1) { $lastmonth = 12; } else { $lastmonth = ($curdate['mon'] - 1); } if ($curdate['mon'] = 1) { $lmyear = ($curdate['year'] - 1); } else { $lmyear = $curdate['year']; } $curmonth = $curdate['mon']; $curyear = $curdate['year']; mysql_select_db($database_TIR_AVG_MEM, $TIR_AVG_MEM); $query_all_archers__tot_lm = sprintf("SELECT SUM(TSHOTS) AS TSHOTS_LM FROM tir_avg_mem WHERE TPERSON = '" . $row_all_archers['TPERSON'] . "' AND YEAR(TDATE) = " . $lmyear . " AND MONTH(TDATE) = " . $lastmonth . " ORDER BY TDATE DESC", $colname_all_archers_tot_lm); $all_archers__tot_lm = mysql_query($query_all_archers__tot_lm, $TIR_AVG_MEM) or die(mysql_error()); $row_all_archers__tot_lm = mysql_fetch_assoc($all_archers__tot_lm); $totalRows_all_archers__tot_lm = mysql_num_rows($all_archers__tot_lm); mysql_select_db($database_TIR_AVG_MEM, $TIR_AVG_MEM); $query_all_archers__tot_tm = sprintf("SELECT SUM(TSHOTS) AS TSHOTS_TM FROM tir_avg_mem WHERE TPERSON = '" . $row_all_archers['TPERSON'] . "' AND YEAR(TDATE) = " . $curyear . " AND MONTH(TDATE) = " . $curmonth . " ORDER BY TDATE DESC", $colname_all_archers_tot_tm); $all_archers__tot_tm = mysql_query($query_all_archers__tot_tm, $TIR_AVG_MEM) or die(mysql_error()); $row_all_archers__tot_tm = mysql_fetch_assoc($all_archers__tot_tm); $totalRows_all_archers__tot_tm = mysql_num_rows($all_archers__tot_tm); mysql_select_db($database_TIR_AVG_MEM, $TIR_AVG_MEM); $query_all_archers__tot_ty = sprintf("SELECT SUM(TSHOTS) AS TSHOTS_TY FROM tir_avg_mem WHERE TPERSON = '" . $row_all_archers['TPERSON'] . "' AND YEAR(TDATE) = " . $curyear . " ORDER BY TDATE DESC", $colname_all_archers_tot_ty); $all_archers__tot_ty = mysql_query($query_all_archers__tot_ty, $TIR_AVG_MEM) or die(mysql_error()); $row_all_archers__tot_ty = mysql_fetch_assoc($all_archers__tot_ty); $totalRows_all_archers__tot_ty = mysql_num_rows($all_archers__tot_ty); $avg_week = ($row_all_archers_tot['TotalShots'] / $totalRows_all_archers__avg_w); $avg_month = ($row_all_archers_tot['TotalShots'] / $totalRows_all_archers__avg_m); $tot_lmonth = $row_all_archers__tot_lm['TSHOTS_LM']; $tot_tmonth = $row_all_archers__tot_tm['TSHOTS_TM']; $tot_tyear = $row_all_archers__tot_ty['TSHOTS_TY']; ?> And the code in the HTML part : <TR> <TD ALIGN=center><FONT SIZE="1"><B><?php print $lang['Shots_archer']; ?></B></FONT></TD> <TD ALIGN=center><FONT SIZE="1"><B><?php print $lang['Shots_archer_avg_w']; ?></B></FONT></TD> <TD ALIGN=center><FONT SIZE="1"><B><?php print $lang['Shots_archer_avg_m']; ?></B></FONT></TD> <TD ALIGN=center><FONT SIZE="1"><B><?php print $lang['Shots_archer_tot_lm']; ?></B></FONT></TD> <TD ALIGN=center><FONT SIZE="1"><B><?php print $lang['Shots_archer_tot_tm']; ?></B></FONT></TD> <TD ALIGN=center><FONT SIZE="1"><B><?php print $lang['Shots_archer_tot_ty']; ?></B></FONT></TD> </TR> <?php do { ?> <TR> <TD ALIGN=center><FONT SIZE="2"><?php echo $row_all_archers['TPERSON']; ?></FONT></TD> <TD ALIGN=center><FONT SIZE="2"><?php print round($avg_week, 0); ?></FONT></TD> <TD ALIGN=center><FONT SIZE="2"><?php print round($avg_month, 0); ?></FONT></TD> <TD ALIGN=center><FONT SIZE="2"><?php print round($tot_lmonth, 0); ?></FONT></TD> <TD ALIGN=center><FONT SIZE="2"><?php print round($tot_tmonth, 0); ?></FONT></TD> <TD ALIGN=center><FONT SIZE="2"><?php print round($tot_tyear, 0); ?></FONT></TD> </TR> <?php } while ($row_all_archers = mysql_fetch_assoc($all_archers)); ?> And, off course, closing the recordsets : <?php mysql_free_result($all_archers); mysql_free_result($all_archers_tot); mysql_free_result($all_archers__avg_w); mysql_free_result($all_archers__avg_m); mysql_free_result($all_archers__tot_lm); mysql_free_result($all_archers__tot_tm); mysql_free_result($all_archers__tot_ty); ?> Any help on this would be greatly appreciated! PF