I have a Project Management application I am building with PHP and MySQL and the function below is used to mass update all Tasks associated with a project, in some cases this could be 200-300 MySQL records being updated at once.
I just realized there is a flaw in my design here though. This function UPDATES my database columns status, date_modified, and date_completed
This is great most of the time but I just realized that often a user will forget to "start" a Task which sets the date_started column.
If the date_started column is never set, it will have a value of 0000-00-00 00:00:00 and with a date_started column set to 0000-00-00 00:00:00 and the date_completed column set to NOW...this breaks my Gantt chart functionality for these Tasks.
So I need to modify my SQL below so that when setting a status to completed it will somehow check to see if the date_stared column === 0000-00-00 00:00:00 and if it does, it will need to also update that column to NOW.
I believe this is possible using CASE in MySQL instead of having to query each and every record into PHP to do the check and then do a separate UPDATE when needed but my experience is very little in these more advanced SQL features.
Could someone help me with a solution?
public function completeAllTasks($projectId, $completed_date = true){
// Update Project Task STATUS and Date_MODIFIED
$sql = "UPDATE `project_tasks`
SET `status` = 'Completed',
`modified_user_id` = '$this->user_id',
`date_completed` = UTC_TIMESTAMP()
`date_modified` = UTC_TIMESTAMP()
WHERE `project_id` = '$projectId'
AND `status` != 'Completed'
AND `heading` != '1'";
return $this->db->query($sql);
}
UPDATE
I did some testing and this seems to do the trick...
`date_started` = (CASE
WHEN date_started = '0000-00-00 00:00:00'
THEN UTC_TIMESTAMP()
ELSE date_started
END)
$sql = "UPDATE `project_tasks`
SET `status` = 'Completed',
`modified_user_id` = '$this->user_id',
`date_completed` = UTC_TIMESTAMP(),
`date_modified` = UTC_TIMESTAMP(),
`date_started` = CASE
WHEN date_started = '0000-00-00 00:00:00' THEN NOW()
ELSE date_started
END
WHERE `project_id` = '$projectId'
AND `status` != 'Completed'
AND `heading` != '1'";
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments