thanks guys, I really appreciate it.

first off, Martin your suggestion looks great but I don't think its SQL
syntax is supported by MySQL. It's a good start though - gives me something
to work with :)

I guess I need to clarify a bit more about what needs to happen...

in the MySQL database, there are two tables involved - "users" and "scores"

Both tables have a "Username" field so Username can be used as a key for
joins.

Unfortunately, since this database is ancient and far from
normalized/optimized, neither table uses unique id's. So looking at the
scores table the fields are Username, Month, and Score.

Another big dud is the fact that the Month field is varchar, not a date
field. So this makes it difficult to sort, as Mehmet suggested.

And this also makes it difficult to write a function to automate the 3
consecutive month aspect that's required. That's why I had to code to look
for specific months - this month (January) counting back 3 (December,
November, October). Students have until the end of the month to take the
monthly test - so they can maintain certification if they have passed Dec.,
Nov., and Oct.'s tests. Or, if they have taken and passed January's test,
they can be certified if they've also passed December and November. Make
sense? 4 months total: if the first 3 are passed and/or last 3 are passed
the student is "certified".

So...

I tried to grab everyone's Username with the first query (which should
result in an array of about 2,000 records). Then using the "while" function
I thought I could perform a query for each Username.

So for each Username in the first result set, I would query the database and
select that user's test scores. I would then process these results by
looking for specific months and checking to see if those  scores were 75 or
better.

The problem lies in the loop - it doesn't go through each Username in the
system like it's supposed to. Are you not supposed to have a 'while' loop
inside another 'while' loop?

I've really been fighting to restructure the whole database, as it really
was poorly planned. But the system is old and there are many, many
applications that rely on it. So it'll take time and caution to make that
move.

In the meantime I'm just trying to figure out how to get this certification
bug resolved. I need to finish writing this script so that the database can
accurately reflect who is certified.

Hope that clarifies things. Any help would be greatly appreciated. In the
meantime I'm going to play around with Martin's sql statement - seems like a
good start.

thanks!
brian


On 1/9/02 7:18 PM, "Martin Towell" <[EMAIL PROTECTED]> wrote:

> 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 Database 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]

Reply via email to