changes done in an update query notification

[Weebs]

First let me say that I've been lurking here for awhile sponging up knowledge. Great site.

 

2nd, I apologize with my description of my issue... I don't blame you if you don't fully understand exactly what I'm asking.

 

Here goes. I have a html form that is initially filled in with values from a select query. The form is editable and when the form is submitted it updates the table to those values. What I'd like to do is be able to show through another query, or another form of output what was changed in red.

 

For those of you familiar with facebook think of the news feed. When someone changes a part of their profile a notification is sent saying "billy joe bob has removed 'Jaws' from their favorite movie" or "billy joe bob has added 'i like cheese on my sandwiches' from their hobbies"

 

Does anyone have an example of a code snippet that might get me moving in the right direction with this? A tutorial would be great!

 

Thanks for any and all help in advance.

 

~Weebs

[cooldude832]

This is a semi tricky problem because in mysql based storage you store elements in rows, not in an "event" based criteria".  What you almost what to do is store in a secondary table all your "Update events" saying This UserID add/deleted/updated ThisItem and store it with a timestamp.  Then when ever a users selected there update history you simply use time + user ids

[Weebs]

hmm, let me try to clarify. What I'm working with is essentially an edit. I want to show what was edited. So like if the field said "There here to travel over they're" and the form field is changed to "They're here to travel over there". I would be able to show an output similiar to this  "They'rehere to travel over there" where red indicates what exactly was changed.

[cooldude832]

you have to store a log simply because a state change is an irreversable process in mysql.  (it sounds like physical chemistry, but its not )

[Weebs]

I realize that I'd need to store what the new value is. But what would designate that the word They're changed? I might be working at something that is impossible (although nothing is impossible right?) How would I flag the values that variated from the initial?

[cooldude832]

you can't flag the rows like you want to you need a secondary table of "events" so to speak.  Like when you watch a baseball game on mlb.com and it gives you event status (pitcher throws to first) (called strike) etc.  Those are events i am talking about.

 

So you do this

1) User sets a new condition (moves movie 21 to favorites list)

2) Query the favorite movie table and add it to it

3) Update table events via"

<?php 
$q = "Update `events` Set (UserID, Time, Event_Type, ItemID, ActionType) VALUES('".$_SESSION['UserID']."', NOW(), '".$e_type."', '".mysql_insert_id()."', '".$a_type."')";
?>

You need to make e_type and a_type either enum or carry across their integer equivalence via php arrays

 

You can go complex like if Person A removes person B from friends list and probably build a php function to decode this table into a nice string, but i'm just throwing out the idea and stating you can't look back at a row you rewrite in mysql to say oh I  rewrote this so its in the "updated" category.  You need secondary storage methods