0

I am creating an activity feed using PHP and MYSQL. I would like to save the activity history in a table (activity_history) that lists who modified the entry last, when and what they modified.

I am able to insert the first two fine, but was wondering if there is a query that can return what changed in the record in the last executed query.

For instance, I would like history_details to be either (updated due date, updated activity name or changed status):

$update_activity_history = "INSERT INTO activity_history (activity_id, history_created_by, history_details) 
            VALUES ('$activity_id','$activity_created_by','$history_details')
                  "or die("Error: ".mysqli_error($connectio
    ));
Adrian Cid Almaguer
  • 7,815
  • 13
  • 41
  • 63
EDarrien
  • 117
  • 10
  • 1
    There's no easy way to obtain this information from MySQL, no. But your application should already "know" what it has just updated when it issues this `INSERT` command, so what's the problem? – eggyal Mar 11 '15 at 03:05
  • @eggyal I am trying to have a history feed in the application so other users can tell who last updated, when and what was updated.. – EDarrien Mar 11 '15 at 03:06
  • Yes, I understand that. – eggyal Mar 11 '15 at 03:08
  • you can use mysql query log –  Mar 11 '15 at 03:08
  • @Dagon: How would you obtain the latest command issued by the current connection from the general query log? – eggyal Mar 11 '15 at 03:10
  • @Dagon I was hoping for something similar to $connection->insert_id; Something that checks if a value changed in the record, display what has changed.for example "user1 changed (the activity due date)" – EDarrien Mar 11 '15 at 03:12

1 Answers1

0

There is no built-in functionality quite like this, no. You would have to either manually copy the current data to the log table before making the modification, or use MySQL triggers.

Community
  • 1
  • 1
Erik Johansson
  • 1,646
  • 1
  • 14
  • 19
  • Triggers can't help, because they can't be furnished with the additional state information that is to be inserted into the log (namely, who performed the operation). – eggyal Mar 11 '15 at 03:12
  • That could be solved with a 'last_edited_by' field, but yes implementing the logic around that is a bit messy. I would personally implement log functionality like this on the PHP side. – Erik Johansson Mar 11 '15 at 03:21