PHP question

[Gary Holbrook]

Either PHP or MySQL (I forget which) has a conversion function that will 
describe a date as purely numeric data.  I think MySQL.  Then your SQL 
statement could take advantage of the MySQL date functions.

They are listed on Page 219 of the MySQL manual.  There are a slew of goodies 
in there.  I would recommend doing this whole thing with a MySQL query and 
cutting PHP out of the picture except to display the results.

You can download the MySQL Manual at 
http://www.mysql.org/download3.php?file_id=315

Gary

On Wednesday 31 October 2001 10:27 am, Mike Rambo wrote:
> I have a question for any/all PHPer's out there.
>
> Our shop database system is up to a couple thousand entries now, and
> we'd like to start getting some statistics out of it. We're tracking the
> repairs we perform and the data includes, among other things, receive
> and completion dates for each piece of equipment. We'd like to generate
> a report that would show the average time it has taken over the last 30,
> 60, and 90 days to turn around each piece of equipment (ie. fix it and
> get it out the door). The dates are stored in Mysql in the form
> YYYY-MM-DD. I'm having some difficulty determining which is the best way
> to select the data from Mysql in the first place, and then how to
> manipulate it (mathematically) with PHP thereafter. I've looked at
> several different things I've found on phpbuilder.com and devshed.com
> but they've left me mildly confused as to what the best way might be to
> accomplish this. It seems the facilities for manipulating dates in PHP
> are not real refined. ASP, from what I'm told, has a diffdate() function
> that would appear to help a great deal but an equivalent does not exist
> in PHP4.
>
> What I think I need:
>
> *count number of records completed in the last X number of days
> *select all records completed in the last X number of days
> *determine days it took to complete each machine
> *sum number of days for all machines
> *divide total days by number of machines in time period
>
> I've got code that will iterate through the records once they're
> selected. My difficulty is in _how_ to do the selection and how to do
> the date mathematics thereafter. It appears I can strip the '-' out of
> the date once I have it out of PHP into a variable, and then ostensibly
> perform some arithmetic operations on it - is this correct? Also, I'm
> not sure how to tell Mysql how to select records that have a
> complete_date > (current_date - 30) when the date is not stored as a
> purely numeric entry.
>
> Any insight?