[PHP-DB] More Date operations.
Hi,
I am trying to access the MySQL function TO_DAYS() from PHP.
From MySQL I can type in: SELECT TO_DAYS('2000-1-1') AS bar;
and I get:
++
| bar|
++
| 730485 |
++
1 row in set (0.00 sec)
From PHP, I have tried:
$sql=SELECT TO_DAYS(2001-01-01) AS bar;
$result=mysql_query($sql) or die(Could not access database);
$days=mysql_fetch_array($result);
echo days: .$days[bar].br;
But I don't get the result coming through.
I have tried mysql_field_name and the name comes across as bar.
mysql_num_rows shows that there is one row in the result.
Am I missing something really really obvious?
Regards,
Garry.
--
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]
Re: [PHP-DB] Date operations.
On Wed, 30 Jan 2002, DL Neil wrote:
Can I request some db-speak please? You already have a tbl (or more).
So let's start by asking for the table definitions
The table definition is as follows:
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)
);
Do you already have some SQL as a 'first attempt'? Could you also post
that, together with a note about its short-comings/what needs to be
added? That would also assist (my, feeble) comprehension.
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);
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.
Normally start and end dates would be input via a form.
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.
--
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]
Re: [PHP-DB] Date operations.
On Thu, 31 Jan 2002, DL Neil wrote:
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.
You are right. I works without the TO_DAYS(). But I did get them from the
manual :-)
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?
Basically for statistical reasons I need average pairs per month, just a
single number. But the months/years to be queried on would be input via a
form. I was thinking of generating the data on-the-fly, but as you suggest
above and from something Frank Flynn suggested, I would be better off
having a separate table with monthly results. They could all be
pre-calculated except the current month.
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.
Input is two dates, granularity one month. For example start Jan 2000,
finish Dec 2001. Then for each month in that period, I want the avarage
for each month. For example:
2000 January 56.2
2000 February 34.4
2001 December 48.6
There will be a whole lot of other statistics as well, such as number of
eggs laid, number hatched, number of surviving chicks etc. But I can
calculate _them_ quite easily.
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?
End of month averages only.
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...
Sorry to keep you awake :-)
Thanks for your help,
Regards,
Garry.
--
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]
[PHP-DB] Date operations.
Hi All, I have a problem working out a suitable algorithm either in PHP or MySQL. Basically I have a DB that keeps track of breeding records. Each record has a paired data, and a split-up date. I need to generate some statistics to work out average numbers of pairs per month, averaged on a daily basis, for a given start and stop date, typically a year or year-to-date. All the algorithms I can think of are messy, where I have to loop through all the breeding records for every day of the year, and count how many pairs are breeding by seeing if the date is between the start and stop dates, and then average that on a monthly basis. I can't see that scaling very well, as there might be several hundred breeding records for a given year, multiplied by 365 days. Has anyone any hints/pointers for an efficient way to do this? Regards, Garry. -- 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]
