hackalive Posted August 24, 2013 Share Posted August 24, 2013 Hi Guys, I have a database table and I want to be able to track all the changes to it, much like wikipedia you can see previous versions. I have many fields within the table, extract/example below. TBL id (PK) name address So I want to track any and all changes to any of the fields Whats the best way to achieve this? Let me know if you need any more info. Link to comment https://forums.phpfreaks.com/topic/281517-tracking-database-changes/ Share on other sites More sharing options...
ignace Posted August 24, 2013 Share Posted August 24, 2013 If you use Doctrine you can create your database schema in code. Schema updates can then be performed through code. Link to comment https://forums.phpfreaks.com/topic/281517-tracking-database-changes/#findComment-1446546 Share on other sites More sharing options...
hackalive Posted August 24, 2013 Author Share Posted August 24, 2013 I dont want to update the schema. I was to track changes of a recod/row. So if I update the name, I should be able to see previous names and revisions and by whom. Same as like with wikipedia articles (kind of). Link to comment https://forums.phpfreaks.com/topic/281517-tracking-database-changes/#findComment-1446558 Share on other sites More sharing options...
ignace Posted August 24, 2013 Share Posted August 24, 2013 table (.., version) table_versions (.., version)In table version is default 1 when you update the row you copy it to the *_versions table. You update the table, and increment the version on the 'table' table. You might wanna lock the row/table before doing this to avoid multiple people overriding each others changes. Link to comment https://forums.phpfreaks.com/topic/281517-tracking-database-changes/#findComment-1446564 Share on other sites More sharing options...
jcbones Posted August 24, 2013 Share Posted August 24, 2013 Or, you could never update, only insert. Returning the last row inserted, until you need to see what was revised. Link to comment https://forums.phpfreaks.com/topic/281517-tracking-database-changes/#findComment-1446573 Share on other sites More sharing options...
hackalive Posted August 25, 2013 Author Share Posted August 25, 2013 Ok, any other suggestions? Link to comment https://forums.phpfreaks.com/topic/281517-tracking-database-changes/#findComment-1446610 Share on other sites More sharing options...
trq Posted August 25, 2013 Share Posted August 25, 2013 Nope, that about covers it. Link to comment https://forums.phpfreaks.com/topic/281517-tracking-database-changes/#findComment-1446632 Share on other sites More sharing options...
AdrianBV Posted August 25, 2013 Share Posted August 25, 2013 not really ... you have one other option ... another table that will record every change. For example: your master table is: id, name, address, phone you could make a second table with the following structure: id, id_changed, changed_var, prev_value, current_value, changed_date, ip or user_id ( etc ) and for every update of a row you could insert a line in the second table like this: 2, address, Lake City, Lake City 3, 2013-03-03 12:32:23, 232.23.223.23 for every new row added in the master table you could do the same but leave empty the prev_value ( or maybe add a TINYINT in the second table to set as 1 or 0 if it's a new row or a update ) If there are a lot of changes the second table could get big but if you really want to track everything this is the ONLY way you can. With this implementation you can see every modification to each id or even the whole table at a precise moment in time. If you have any other questions feel free to ask. Link to comment https://forums.phpfreaks.com/topic/281517-tracking-database-changes/#findComment-1446673 Share on other sites More sharing options...
jazzman1 Posted August 25, 2013 Share Posted August 25, 2013 Basically, I would say the same as AdrianBV's post above.if these updates not happened every minute, hour or day, you could create a second table which may look as the master one or just replicate the columns name that you need to use for yourself.For example:CREATE TABLE replicate_master (id, author_name, author_text)Then, whatever you need to update the table, you could run the following query: REPLACE INTO `replicate_master` SELECT `author_name`, `author_text` FROM `master_table` GROUP BY `author_name` Later, create a cron job, which it runs on a schedule basis and execute this query from the shell using the command-line client with the "-e" flag.It would be something like: /usr/bin/mysql -h "db_server" -u "db_name" -p "db_pass" --database="db_name" -e "REPLACE INTO `replicate_master` SELECT `author_name`, `author_text` FROM `master_table` GROUP BY `author_name`" If these updates happen every minute or less you can execute this "REPLACE INTO" statement before someone try to update some values in this table. REPLACE INTO `replicate_master` SELECT `author_name`, `author_text` FROM `master_table` WHERE `author_name` = '$author_name' GROUP BY `author_name` Link to comment https://forums.phpfreaks.com/topic/281517-tracking-database-changes/#findComment-1446684 Share on other sites More sharing options...
ignace Posted August 25, 2013 Share Posted August 25, 2013 I think it's great to see two people decide to repeat what I said. table (.., version) table_versions (.., version)In table version is default 1 when you update the row you copy it to the *_versions table. You update the table, and increment the version on the 'table' table. You might wanna lock the row/table before doing this to avoid multiple people overriding each others changes. Link to comment https://forums.phpfreaks.com/topic/281517-tracking-database-changes/#findComment-1446692 Share on other sites More sharing options...
jazzman1 Posted August 25, 2013 Share Posted August 25, 2013 He-he.......yeah....but with examples Link to comment https://forums.phpfreaks.com/topic/281517-tracking-database-changes/#findComment-1446694 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.