[PHP Dreamweaver - repeat recordset issue]

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