History of fields changes - memo field

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
ursl40
Forum Commoner
Posts: 37
Joined: Thu Nov 18, 2010 5:01 am

History of fields changes - memo field

Post by ursl40 »

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!
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: History of fields changes - memo field

Post by Celauran »

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.
ursl40
Forum Commoner
Posts: 37
Joined: Thu Nov 18, 2010 5:01 am

Re: History of fields changes - memo field

Post by ursl40 »

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?
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: History of fields changes - memo field

Post by Celauran »

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.
ursl40
Forum Commoner
Posts: 37
Joined: Thu Nov 18, 2010 5:01 am

Re: History of fields changes - memo field

Post by ursl40 »

OK, I'll try that, thanks again!
ursl40
Forum Commoner
Posts: 37
Joined: Thu Nov 18, 2010 5:01 am

Re: History of fields changes - memo field

Post by ursl40 »

I'm lost :oops:

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?
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: History of fields changes - memo field

Post by Celauran »

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";
ursl40
Forum Commoner
Posts: 37
Joined: Thu Nov 18, 2010 5:01 am

Re: History of fields changes - memo field

Post by ursl40 »

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);
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: History of fields changes - memo field

Post by Celauran »

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.
ursl40
Forum Commoner
Posts: 37
Joined: Thu Nov 18, 2010 5:01 am

Re: History of fields changes - memo field

Post by ursl40 »

No, it's not problem in WHERE clause, because WHERE clause is the same as in UPDATE - UPDATE without history works fine.
ursl40
Forum Commoner
Posts: 37
Joined: Thu Nov 18, 2010 5:01 am

Re: History of fields changes - memo field

Post by ursl40 »

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.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: History of fields changes - memo field

Post by Celauran »

ursl40 wrote:How can I get only first 3 (last 3) history values to be visible?
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?
User avatar
McInfo
DevNet Resident
Posts: 1532
Joined: Wed Apr 01, 2009 1:31 pm

Re: History of fields changes - memo field

Post by McInfo »

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.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: History of fields changes - memo field

Post by Celauran »

I suggested something similar earlier. I agree it's probably the better way to go.
Post Reply