[PHP] Re: [PHP-DB] Re: [PHP] need help looping through each record with a query -stumped
days, ie
any time in November, not only since 10Nov2001. Correct?
=all of these questions will have a major impact on how the system can be implemented.
=Implementation comes down to two major issues: (1) how to work out the three month
rule, and (2) how to work
out the last three test scores. Indeed depending upon some of the answers above, we
may be able to reduce the
matter to counting 'passes' within issue (1) only!
=Certainly, depending upon your answers to the above, it should be possible to reduce
the logic to a single
MySQL call, and possibly only one PHP loop!
=1 the three month rule
This becomes trivial to solve (in either PHP or SQL). It is either today's date less
three months (either by
subtracting from 'months', or by rounding to 90 days), or by doing that and then
changing the 'day' number to
the first. Now the MySQL query becomes:
SELECT ... WHERE test_date = calc_3month_date
and immediately we can see that if fewer than three records are returned we have a
no-hoper [and 'bang' go my
chances of certification...]
=2 the last three test scores
Once the test-date datatype is chosen for 'functionality' this also becomes trivial,
because the SQL query
should first GROUP BY the scores table by username and then ORDER BY month/date
DESC(ending). However to say
more, depends upon answers to points raised above.
=if we can sort out the underlying structural issues, this thing can be licked quite
quickly!
=dn
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
[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]
Re: [PHP] need help looping through each record with a query - stumped
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!
RE: [PHP] need help looping through each record with a query - stumped
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]
Re: [PHP] need help looping through each record with a query -stumped
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
