History of fields changes - memo field
Moderator: General Moderators
History of fields changes - memo field
Hy!
I was wondering how to code history of fields changes (memo field), so if I have a table Users with id, name, surname, address, telephone number and history, if I change for example a telephone number multiple times, then in history I would be able to see all the previous telephone numbers (the same for name, surname, address and telephone number, all in field history)!
I can't find memo field in phpMyAdmin...
Thanks for any suggestions!
I was wondering how to code history of fields changes (memo field), so if I have a table Users with id, name, surname, address, telephone number and history, if I change for example a telephone number multiple times, then in history I would be able to see all the previous telephone numbers (the same for name, surname, address and telephone number, all in field history)!
I can't find memo field in phpMyAdmin...
Thanks for any suggestions!
Re: History of fields changes - memo field
Two ways immediately come to mind.
1. Create a text field in the table in question, use UPDATE statements to append changes to the end of the field as they're made.
2. Create a new table called changes, INSERT each change into said table listing the change made as well as the account number to which the change applies.
1. Create a text field in the table in question, use UPDATE statements to append changes to the end of the field as they're made.
2. Create a new table called changes, INSERT each change into said table listing the change made as well as the account number to which the change applies.
Re: History of fields changes - memo field
How can I append changes to the end of the field (in the same table, for example history), so the previous changes won't be deleted/overwritten?
Re: History of fields changes - memo field
Read them into a variable, append to the variable, write them back.
Code: Select all
while ($row = mysql_fetch_array($result))
{
...
$changes = $row['changes'];
...
}
$changes .= "New changes here.\n\n";
Last edited by Celauran on Mon Nov 22, 2010 6:59 am, edited 1 time in total.
Re: History of fields changes - memo field
OK, I'll try that, thanks again!
Re: History of fields changes - memo field
I'm lost
If I have table users with id, name, surname and history, how can I code, that after updating/editing user, all fields, that have been changed, would write to the field history, and if I update/change that user again, previous changes would stay in history field and those new would also be written in that history field?
If I have table users with id, name, surname and history, how can I code, that after updating/editing user, all fields, that have been changed, would write to the field history, and if I update/change that user again, previous changes would stay in history field and those new would also be written in that history field?
Re: History of fields changes - memo field
I'm guessing id won't be changing, so something like this:
Code: Select all
$sql = "SELECT id, name, surname, history FROM users WHERE $where";
$res = mysql_query($sql);
// I'm assuming we're dealing with a single user here
$user = mysql_fetch_assoc($res);
$changes = '';
if ($_POST['name'] != $user['name'])
{
$changes .= "Previously: {$user['name']}\n";
}
if ($_POST['surname'] != $user['surname'])
{
$changes .= "Previously: {$user['surname']}\n";
}
$user['history'] .= $changes;
$sql = "UPDATE users
SET name = '{$_POST['name']}',
surname = '{$_POST['surname']}',
history = '{$user['history']}'
WHERE $where";Re: History of fields changes - memo field
It writes me: Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in C:\...\update.php on line 42
Line 42:
$user = mysql_fetch_assoc($res);
Line 42:
$user = mysql_fetch_assoc($res);
Re: History of fields changes - memo field
Could it be the WHERE clause? I always use WHERE $where in pseudocode, but if you haven't defined $where then the query will fail.
Re: History of fields changes - memo field
No, it's not problem in WHERE clause, because WHERE clause is the same as in UPDATE - UPDATE without history works fine.
Re: History of fields changes - memo field
OK, I figured out what was wrong - DB connection! Thanks!
How can I get only first 3 (last 3) history values to be visible? History is a field in table users and is populated with other fields of table users (name, surname) on their change.
How can I get only first 3 (last 3) history values to be visible? History is a field in table users and is populated with other fields of table users (name, surname) on their change.
Re: History of fields changes - memo field
Not sure I follow. Isn't history a column like any other? Doesn't "SELECT * blah blah blah WHERE user_id = foo" return all history for that user?ursl40 wrote:How can I get only first 3 (last 3) history values to be visible?
Re: History of fields changes - memo field
I'll throw something in here that is on a completely different path than the current discussion.
I suggest creating a separate table to track the updates. The new table would have the same fields as your original users table, but would have an additional "revision number" field. The primary key of the new table would be a combination of the key for the original table (probably "id") plus the revision number.
Every time you update the users table, add a row to the history table with the old data and an incremented revision number. The original table always has the most recent data, and the history table remembers the old data. To save space, you could insert NULLs for the fields that did not change. The id, of course, would never be NULL because it is part of the primary key.
The easiest way to implement this is with an ON UPDATE trigger in MySQL.
I suggest creating a separate table to track the updates. The new table would have the same fields as your original users table, but would have an additional "revision number" field. The primary key of the new table would be a combination of the key for the original table (probably "id") plus the revision number.
Every time you update the users table, add a row to the history table with the old data and an incremented revision number. The original table always has the most recent data, and the history table remembers the old data. To save space, you could insert NULLs for the fields that did not change. The id, of course, would never be NULL because it is part of the primary key.
The easiest way to implement this is with an ON UPDATE trigger in MySQL.
Re: History of fields changes - memo field
I suggested something similar earlier. I agree it's probably the better way to go.