Brian, > 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 :)
=until it is tried, you won't know! From an SQL point of view it looks ok. In fairness to Martin, from the 'further information' given since it may not do everything you want... > 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". ... =regardless of the age of the database, the data appears to be normalised (to the description given) however it is definitely NOT optimised. You will notice that each of the contributors has been attempting to work in MySQL, but you still have the other option - that of tidying up and debugging your torturous PHP code! The PHP effort can be used to attempt to 'recover' from the poor database structure, but as you have observed, at some cost - particularly if you ask yourself how you are going to get things to work in February... The problem with persisting in this is that you have a weak database structure AND you have some hairy (hard to understand) PHP code as a work-around (and may God bless all who sail in her)! =like the others, my recommendations consist of revisiting your business rules and then 'optimising' the database/data structure - best to fix the problem at its source! =the idea that the NAME of each month should be used for processing is causing major problems. The name of the month is a LABEL, ie is very useful at the tops of reports, letters, newspapers, etc. It is NOT a tool for calculations. If you'd like to take a look at the archives of the PHP discussion list, you'll find my contribution on this topic dated a few days ago entitled: "counting with dates (help!)" talking about the three primary date formats and their uses. =So if I sit a test this month (January), and the last month of last year (December), and the second-last month of 2000 (November) [and with blinding arrogance, let me assume that I would pass each with flying colors (cough, cough)], and further assume that I haven't sat any other tests [much more my speed!]; the current logic (apparently) credits me with a certification!? [thank you] Now before anyone following this labrythine logic starts to laugh, let me say that there are good reasons/procedures that might be in place to prevent such a stupidity happening - but they will almost certainly also make the process of keeping historical records more difficult. =how does the system keep track of people who do not sit a test during a particular month? =and the other way around: if I sit the test today and fail [please no jeers, I'm very sensitive] do the rules allow/is it possible for me to 'resit' during January. Thus is it possible for me to sit (and pass) single tests in Nov and Dec, but sit two tests in Jan - one a 'fail' and one a 'pass'. If so, are you looking at my last three tests with a view to certification, or are you looking at my last three passes (during the pertinent three months)? =further: can we assume that a record will be entered into the scores table regardless of whether the person passed or failed? (I have assumed so) =while on this theme, how is the system to react if tests are not held during a particular month, eg the certification center is closed for summer/Christmas vacation, or out of respect for September 11? =another question about 'rules': the problem description indicates that it is possible for a person to have sat January's test already/early in the month, but it is also possible that (s)he has not. Are the tests held on a particular date for all candidates, or does the system allow each candidate to pick his/her own test date? (which also implies that the January date for all candidates will either be the same, or is potentially very different) - this may make it easier/harder to define an SQL query. =finally, if I have understood correctly, certification is calculated by three 'month' units, not 90-days. Thus the rule is not three passes in the last 90 days, but is in fact (today) something like the last 100 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 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] > > -- 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]
