Re: Date operations.

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