Jump to content

PHP Dreamweaver - repeat recordset issue


Recommended Posts

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

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.