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

    General solution query: add date parameters to restrict for week month etc

    
$sql = "SELECT id, name, AVG(shots) FROM table
GROUP BY id, name";

    Now I know this looks like what you are doing, but yours overcomplicates the issue which is why you only get 1 persons figures. This query simply will return the average for every user in the table. Adding a where clause with start and end dates will limit it to a particular time peroid. Using date functions for month etc would do the same. If you added the date field into the select it would give the average for each user for each day, but I think there is only one entry for each archer for each day so that is no use.

      Write a Reply...