I have the MySQL SQL below that is ran on my Project Management application I am building every time Project Tasks are Saved.
A save can consist of saving new tasks records as well as updating existing records. For this reason I have this fancy SQL below which Inserts a New Tasks record if one doesn't exist yet with that ID, if it does already exist then it updates certain fields. It is smart enough that it does NOT update the date_modified
column unless name
,description
,status
, type
, priority
fields have changed from their previous value. The reason for this is because the sort_order
column will change on every record update but should not constitute a "change" in the date_modified
field if only the sort_order
column is updated.
The reason it works this way is because there is 1 form on a page that loads all the Tasks records for a project, so you can mass edit all the records at one time as well as add new Tasks records at the bottom. So this SQL below gets called in a WHILE loop for every Tasks record being saved.
It works amazing so far, just as I had hoped but now I am at another challenge.
I would like to create a new Database table for Updates which will basically insert a new record every time a NEW Tasks record is added and every time a Task record is UPDATED.
An example would be when I update a page with 20 Project Tasks and I change the values on 5 of the Tasks records, it would then insert 5 new records into my updates table. Something like...
JasonDavis Updated Task Record #1
JasonDavis Updated Task Record #2
JasonDavis Updated Task Record #3
JasonDavis Updated Task Record #4
JasonDavis Updated Task Record #5
JasonDavis ADDED NEW Task Record #4534
With the way my SQL is ran below, I am not able to know in my PHP which records were actually Updated as it updated every single record, I need to insert into my new table, only records where the date_modified
is Updated.
QUESTION 1): Does anyone have any ideas how I might achieve this goal? Modifying my SQL below or another method to make sure I insert a new record into an UPDATES
DB Table when a New Tasks record is created, OR when a Task record has ANY of these columns Updated: name
,description
,status
, type
, priority
, or date_modified
is UPDATED. This means if a Task record updates only a field like sort_order
then it will NOT insert a new record into the UPDATES table for that updated Task record.
I have seen some posts on StackOverflow that do somewhat similar but still far less complex items using TRIGGERS
which I have never used a Trigger or even heard of them until recently so I have no idea if that is how I would HAVE to do it and if it is, how I would make it do such a complex conditional insert into my new UPDATES table.
My ultimate goal is to simply create a new UPDATES table which will act as a stream showing which tasks are created and modified and by which user and the DATETIME the event happened. What complicates it is my existing INSERT or UPDATE SQL below has to continue working how it does now but somehow get the correct Tasks that are UPDATED accordingly to the conditions mentioned above to constitute a new insert record into the new UPDATES table. Please help experts!?
$sql = "
INSERT INTO
project_tasks(task_id, project_id, created_by_user_id, modified_user_id, name, description, status, priority, type, date_entered, date_modified, date_started, date_completed, sort_order, heading)
VALUES
('$taskId', '$projectId', '$created_by_user_id', '$modified_user_id', '$name', '$description', '$status', '$priority', '$type', UTC_TIMESTAMP(), UTC_TIMESTAMP(), '$date_started', '$date_completed', '$sort_order', '$heading')
ON DUPLICATE KEY UPDATE
date_modified = (CASE
WHEN name <> values(name)
OR description <> values(description)
OR status <> values(status)
OR type <> values(type)
OR priority <> values(priority)
THEN UTC_TIMESTAMP()
ELSE date_modified
END),
modified_user_id='$modified_user_id',
name='$name',
description='$description',
status='$status',
priority='$priority',
type='$type',
date_started='$date_started',
date_completed='$date_completed',
sort_order='$sort_order',
heading='$heading'";
return $this->db->query($sql);