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]
