If you are querying the Score >= 75 how are you ging to
take the "Consecutive" requirement into the account.
--- Martin Towell <[EMAIL PROTECTED]> wrote:
> could you change this
>
> $query2 = ("SELECT Month, Score FROM scores WHERE
> Username =
> '$Username'");
>
> to
>
> $query2 = ("SELECT count(*) FROM scores WHERE
> Username = '$Username' and
> Score >= 75 and Month in ('January', 'December',
> 'November', 'October')");
> $result2 = mysql_query($query2) or die ("Cannot
> execute query" .
> mysql_error ());
> $tally = mysql_fetch_row($result2);
>
> take out the while loop (the one with all the if's in it)
> and, if I haven't
> stuffed up somewhere, all will be okay - (btw, I haven't
> had experience
> using mysql, but I'm assuming that it's sql is compatable
> with interbase and
> oracle)
>
> Hope that helps
> Martin
>
> -----Original Message-----
> From: Brian Tully [mailto:[EMAIL PROTECTED]]
> Sent: Thursday, January 10, 2002 8:59 AM
> To: PHP DB; PHP
> Subject: [PHP] need help looping through each record with
> a query -
> stumped
>
>
> 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]
