Page 1 of 1
find out the no of days??
Posted: Sat Jan 27, 2007 2:08 pm
by PHPycho
i have a table
daily_progress
Code: Select all
id | user_id | project_id | status | date | work_description |
table is used to record the daily activities of a project assigned to the programmer,
the
status has enum fields like just started , on progress,completed,on hold etc.
when the
status is completed...the project is assumed to be completed..
when the
status is completed then i have to find out the no of working days..but i am
unable to do that..
can anybody help me in finding out the no of working days from the yy-mm-dd format...
Thanks in advance to all of you..
Posted: Sat Jan 27, 2007 2:32 pm
by superdezign
Foreach entry in the table where the project_id == the project your looking at, increase a variable until the status == complete?
Posted: Sat Jan 27, 2007 2:53 pm
by louie35
you have a date field which i suppose is added when the project is inserted in the databae.
The add a new date field with date_finish and update it anytime the changes are made to the project, then compare the date to get the days.
Posted: Sat Jan 27, 2007 3:13 pm
by superdezign
Louie, I think his table is set up so that there are multiple date entries for the same project. Your method seems like it would be if he had one entry per project, he could give a date started and date finished.
PHPycho, I read your post once more and I realized I may have missed some of your details.
I think you want to search your table, still, for each date where the project is entered, but do not include your "on hold" dates, I guess. Although, I'm not too sure why you'd enter days that you didn't work on the project into the table in the first place.
Posted: Sun Jan 28, 2007 3:07 pm
by califdon
As is so often the case, the real problem is in the design of the database. You need to design it with appropriate fields in appropriate tables that allow you to do whatever you're trying to do. You won't get very far trying to solve a problem with PHP code when the tables are not structured appropriately for your task.
As pointed out by an earlier reply, it's not clear whether you need to maintain all the past history of status dates or whether you only need to know the current status and calculate the number of days when the project is completed. If it's the former, you should be using two relational tables, one for the project and one for the status and dates. If it's the latter, you should have another date field in the table.
Rigorous planning before structuring your database invariably pays off in avoiding dead-ends or at least troublesome problems that could have been avoided.
Posted: Sun Jan 28, 2007 3:14 pm
by superdezign
califdon wrote:As is so often the case, the real problem is in the design of the database. You need to design it with appropriate fields in appropriate tables that allow you to do whatever you're trying to do. You won't get very far trying to solve a problem with PHP code when the tables are not structured appropriately for your task.
As pointed out by an earlier reply, it's not clear whether you need to maintain all the past history of status dates or whether you only need to know the current status and calculate the number of days when the project is completed. If it's the former, you should be using two relational tables, one for the project and one for the status and dates. If it's the latter, you should have another date field in the table.
Rigorous planning before structuring your database invariably pays off in avoiding dead-ends or at least troublesome problems that could have been avoided.
Yeah, that'd make a hell of a lot more sense. If you really need your table t record each day of programmer's work, maybe you should also do what louie said with a table that records the start date and finish date per project. Probably simplify your process of calculating the days a lot.