Garry,
I regret to say that I can't see an immediate answer.
> CREATE TABLE breeding (
> rec SMALLINT(4) UNSIGNED AUTO_INCREMENT,
> sire SMALLINT(4) UNSIGNED,
> dam SMALLINT(4) UNSIGNED,
> paired DATE,
> split DATE,
> num_offspring TINYINT(1) ZEROFILL,
> PRIMARY KEY (rec)
> );
>
> >
> First, we define if a pair was breeding for a given date by seeing if that
> date lies between the paired date and the split date. In mysql:
>
> SELECT FROM breeding WHERE TO_DAYS(paired) <= TO_DAYS('$given_date') AND
> TO_DAYS('$given_date') <= TO_DAYS(split);
=There is no need to use TO_DAYS() because paired and split are both dates so you can
operate on them directly.
I recommend that you take a look at the manual's section on Time and Date Functions.
> Where it gets messy is this has to be put into a loop, between the given
> start and end dates (year and month only). For each day in the loop, I
> have to do a query and count the number returned to php. Then average that
> figure for the month.
=So if I understand correctly, you are thinking in terms of a 'result table' which
would (ideally) have the days
1-28/29/30/31 in the left-hand column, and the number of pairs who were put together
on that day (paired <=
day/date <= split). Then at the bottom you want to sum the right-hand column and
divide it by the number of days
in the month to give some sort of average mating possibilities per day. Does that
sound right?
> Normally start and end dates would be input via a form.
=I don't understand, at first you talked of "$given_date and now we have two dates!
Are you inputting a month -
asking for the average for January for example. Or are you putting in two dates
representing a period of
arbitrary length and asking for an average of that, eg 42 days? Please clarify.
> I was wondering if anyone had done that sort of thing, or whether there
> was a more efficient way of doing it than stepping through the database
> one day at a time.
=hey Captain Kirk didn't bother with that 'has it been done before' thinking!
=If I have followed your explanation, and repeated your logic, then we would indeed
need to step through the
days. However this is not the 'relational way' - there may be another way to achieve
the 'numbers'. Do you
actually want/use the daily figures or is it only the bottom/end-of-month average that
is important?
=Trouble is it's after midnight here. Apologies. Please would you respond to the
questions above, and if no one
else steps in meantime, I'll endeavor to return with a more lucid analysis in the
morning...
=Regards,
=dn
--
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]
