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]
