3

I have asked my question on Stackoverflow but have not really gotten any answers so I am trying over here now.

I have the MySQL query that is ran in a PHP script below.

What it does is Inserts a new Project Task record if one does not exist with that ID number.

If a record exist with the ID number, then it UPDATE the fields that it should update.

The problem I have is I need it to only update the date_modified column when one of these columns value changes... name, description, status, type, or priority

public function addOrUpdateTaskRecord($taskId, $projectId, $name, $description, $status, $priority, $type, $date_entered, $date_modified, $sort_order, $heading){

    $sql = "
        INSERT INTO
            $this->tasksDbTableName(task_id, project_id, name, description, status, priority, type, date_entered, date_modified, sort_order, heading)
        VALUES
            ('$taskId', '$projectId', '$name', '$description', '$status', '$priority', '$type', UTC_TIMESTAMP(), UTC_TIMESTAMP(), '$sort_order', '$heading')
        ON DUPLICATE KEY UPDATE
            name='$name',
            description='$description',
            status='$status',
            priority='$priority',
            type='$type',
            date_modified=UTC_TIMESTAMP(),
            sort_order='$sort_order',
            heading='$heading'";

     return $this->db->query($sql);

}

Another user on here Gordon Linoff had showed me that I might be able to change this date_modified=UTC_TIMESTAMP(), and use this in place...

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)

This looked really promising and produces no errors in MySQL. It still inserts new record when needed and updates when it is an existing record, but now it always updates the date_modified field even when other fields have not changed in my CASE statement.

I could really use some help in getting such advanced functionality working or an alternative method?

Any help from the Database experts out there?

My Latest code....

public function addOrUpdateTaskRecord($taskId, $projectId, $name, $description, $status, $priority, $type, $date_entered, $date_modified, $sort_order, $heading){

    $sql = "
        INSERT INTO
            $this->tasksDbTableName(task_id, project_id, name, description, status, priority, type, date_entered, date_modified, sort_order, heading)
        VALUES
            ('$taskId', '$projectId', '$name', '$description', '$status', '$priority', '$type', UTC_TIMESTAMP(), UTC_TIMESTAMP(), '$sort_order', '$heading')
        ON DUPLICATE KEY UPDATE
            name='$name',
            description='$description',
            status='$status',
            priority='$priority',
            type='$type',
            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),
            sort_order='$sort_order',
            heading='$heading'";

     return $this->db->query($sql);

}

My Database table looks like this...

CREATE TABLE IF NOT EXISTS apoll_web_projects_tasks (
            task_id bigint(20) NOT NULL default '0',
            project_id varchar(70) NOT NULL default '0',
            name varchar(255) NOT NULL,
            description varchar(36) default NULL,
            status varchar(20) DEFAULT NULL,
            priority varchar(20) DEFAULT NULL,
            type varchar(20) DEFAULT NULL,
            date_entered datetime DEFAULT NULL,
            date_modified datetime DEFAULT NULL,
            sort_order int(12) DEFAULT NULL,
            heading int(2) NOT NULL default '0',
            PRIMARY KEY  (task_id)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1
  • What is the datatype of date_modified? – ypercubeᵀᴹ Sep 19 '14 at 21:11
2

Below is my final working solution. It turns out that moving this...

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),

to the top of the list, above these fields...

name='$name',
description='$description',
status='$status',
priority='$priority',
type='$type',

So the final solution looks like this below...

$sql = "
    INSERT INTO
        $this->tasksDbTableName(task_id, project_id, name, description, status, priority, type, date_entered, date_modified, sort_order, heading)
    VALUES
        ('$taskId', '$projectId', '$name', '$description', '$status', '$priority', '$type', UTC_TIMESTAMP(), UTC_TIMESTAMP(), '$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),
        name='$name',
        description='$description',
        status='$status',
        priority='$priority',
        type='$type',
        sort_order='$sort_order',
        heading='$heading'";

Just moving my CASE Statement to the top above the other set fields, made it start working correctly!

It almost seems as if these DB column fields name description status priority type were getting Updated before my Case statement could run which resulted in them always appearing to be the same as the DB fields and never any different so my Case statement was about useless. Moving it to the top, it now works 100% correctly.

Hopefully this will help someone with a similar problem someday as many people across 3 sites tried to fix it without success. Just moving it to the top did the trick though so the Order in this case very much matters! ] Thanks to all that have contributed their time to try and find a solution, I always appreciate the help I receive on the StackExchange network sites!

0
0

I would suggest pulling out the in-line sql. Then, create a procedure / trigger that you can apply the proper IF blocks to catch the absolute values you are looking to UPDATE. For instance:

IF (date_modified = _this_ ) THEN
  SET _new_var_for_date_ := _this_desired_timestamp_;
ELSEIF (date_modified = _this_ ) THEN
  SET _new_var_for_date := _this_desired_timestamp_;
...
ELSE
  SET _new_var_for_date := date_modified
END IF;

This will a.) get rid of your in-line sql b.) allow for data checks for integrity c.) give you a cleaner INSERT statement in a programmatic manner.

In turn this will allow for you to walk through the logic you need to accomplish for the INSERT / UPDATE;

2
  • Hello, I really don't know much about this more advanced SQL stuff but I finally got my code working by moving the date_modified portion of the UPDATE above all the other column names,so it is above the name and description, etc and for whatever reason it works perfectly now – JasonDavis Sep 20 '14 at 19:22
  • Glad you found a work around. Now that you mention your fix, I was thinking, you would be able to keep date_modified where it was originally and perform a full inner sub-select. i.e. Instead of just date_modified = (CASE...END); use, date_modified = (SELECT CASE...END CASE FROM..); Only caveat I can think of is MySQL will yell about selecting from a table while updating. Cheers. – D8AGOD Sep 21 '14 at 12:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.