I regret to say that I can't see an immediate answer.

> CREATE TABLE breeding (
>   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 


PHP Database Mailing List (
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
To contact the list administrators, e-mail: [EMAIL PROTECTED]

Reply via email to