Hi,
If I am not missing anything, you are not sorting
SELECT Month, Score
FROM scores
WHERE Username = '$Username'
Also, the requirement is not three consecutive test in a
month, so I would not break the loop into months.
once you have it ordered it by the date :
while (list($Month, $Score) = mysql_fetch_row($result2))
{
/*
here we initialize a counter which we'll use to tally how
many tests the
user has passed over the past 3 months
*/
$tally = 0;
if ($Score >= 75) {
$tally++ ;
} else {
$tally = 0;
// here you are bringing the tally back to 0 if they missed
one.
}
if ($tally=3) { $certified=1 } ;
This should do it. In this loop, if there are three consc.
over 75 scores, then $certified will equal to 1. Even
$tally goes back to 0, if the student misses a test after
being certified, $certified will not change.
At the end, instead of using $tally>=3 use $certified=='1'
I hope this helps.
I would sort this by date or test_id or whatever.
--- Brian Tully <[EMAIL PROTECTED]> wrote:
> hey folks -
>
> i'm stumped. been working on what i thought would be a
> simple script to
> cycle through users' records and perform a query. But I
> can't get the loops
> right, it seems like only one or two records get updated.
>
> Using MySQL, we have about 2000 students enrolled. each
> month they take a
> test. if they pass the most recent 3 tests
> (consecutively) they earn
> "Certified" status and get a few perks.
>
> Somehow the certification status' got messed up. So I'm
> trying to create a
> script that will look through each user's test records to
> see if they've
> passed the last 3 tests, and if so, change their
> certification status in the
> database.
>
> Not very elegant since there are so many records in the
> database (2000
> users), but I don't think i have a choice.
>
> Anyways, I've tried to loop through each user, and for
> each user loop
> through their scores. But the loops aren't working. I'm
> sure it's something
> simple and obvious I'm missing but my brain is fried!
>
> I'd appreciate it if someone could take a look and offer
> any advice. It's
> about 65 lines of code (with comments).
>
> thanks in advance!
> brian
>
> ________________________________________
>
> <?php
>
> include("/home/includes/.connect.inc");
>
> // Select each Username and start a loop of queries for
> each user
>
> $query = ("SELECT Username, Certification FROM users");
>
> $result = mysql_query($query)
> or die ("Cannot execute query" . mysql_error ());
>
> /*
> here starts the loop - for each user in the database we
> look at their test
> records and try to establish if they have passed the last
> 3 tests
> consecutively
> */
>
> while (list($Username, $Certification) =
> mysql_fetch_row($result)) {
>
> $query2 = ("SELECT Month, Score FROM scores WHERE
> Username =
> '$Username'");
>
> $result2 = mysql_query($query2) or die ("Cannot
> execute query" .
> mysql_error ());
>
>
> /*
> for each score record the user has, check to see if
> they've passed the last
> 3 tests
> */
>
> while (list($Month, $Score) =
> mysql_fetch_row($result2)) {
>
>
> /*
> here we initialize a counter which we'll use to tally how
> many tests the
> user has passed over the past 3 months
> */
>
> $tally = 0;
>
> if (($Month == 'January') && ($Score >= 75)) {
>
> $tally++;
> }
>
> if (($Month == 'December') && ($Score >= 75)) {
>
> $tally++;
> }
>
> if (($Month == 'November') && ($Score >= 75)) {
>
> $tally++;
> }
>
> if (($Month == 'October') && ($Score >= 75)) {
>
> $tally++;
> }
>
> }
>
> /*
> the concept is that if a user has taken and passed the
> last 3 tests, they
> become certified. so we look at the current month first
> and go back 3
> months, since it may be the beginning of the month and
> they may not have
> taken the current months test yet. Since we increase the
> counter for each
> passed test, if the counter equals 3 or more this means
> they have passed 3
> tests and are thereby certified. So we update their
> certification status in
> the database.
> */
>
>
> if ($tally >= 3) {
>
> $query1 = ("UPDATE users SET Certification = 'Y'
> WHERE Username =
> '$Username'");
>
> $result1 = mysql_query($query1) or die ("Cannot
> update user to
> Certified" . mysql_error ());
>
> }
> }
>
> print ("Update complete!");
>
> ?>
>
>
> --
> PHP General Mailing List (http://www.php.net/)
> To unsubscribe, e-mail:
> [EMAIL PROTECTED]
> For additional commands, e-mail:
> [EMAIL PROTECTED]
> To contact the list administrators, e-mail:
> [EMAIL PROTECTED]
>
=====
Mehmet Erisen
http://www.erisen.com
__________________________________________________
Do You Yahoo!?
Send FREE video emails in Yahoo! Mail!
http://promo.yahoo.com/videomail/
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]
