dn - thanks SO much for your detailed reply. :)
yes there are tons of problems :( the "last 3 consecutive Months" issue is tricky enough - but the fact that the database is set up all wrong for this type of query is the real problem. unfortunately changing the structure and field formats then changes a LOT of other scripts that interact with with this table. So i need to figure out which is less time consuming - change the database or change the handful of scripts that use this part of the database. But as far as the loop I was trying to accomplish - any idea why it was not processed through completion.? I need to loop through each user's score and process each one. I can't figure out how to do this one user at a time, i.e.: <snip> while (list(Username, $Month, $Score) = mysql_fetch_row($result2)) { </snip> this will just return every record in the database and doesn't allow me to loop through each Username. so how would I do this? is this where i might use FOR or FOREACH? brian on 1/10/02 7:23 AM, DL Neil at [EMAIL PROTECTED] wrote: > Brian, > ... > > =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 -- 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]