i'm converting Brian's php code into sql - he's looking for scores in Oct, Nov, Dec and Jan, that's what I'm doing in the sql, and the if's all have "if score >= 75" which is what I'm doing in the sql too, Brian is getting a total "$tally++" so am I "count(*)"...
-----Original Message----- From: Mehmet Kamil ERISEN [mailto:[EMAIL PROTECTED]] Sent: Thursday, January 10, 2002 11:10 AM To: Martin Towell; 'Brian Tully'; PHP DB; PHP Subject: RE: [PHP] need help looping through each record with a query - st umped 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]
