Tracking changes like a wiki (not storing redundant data)

Here's an interesting database problem that I thought some of you might know how to solve... If you do, I'd appreciate the help!

I'm trying to set a system up where people can edit pages so that what they see is always the most recent version of a piece of text, but actually the database stores all previous versions as well, so that one can retrieve a copy of the text after each individual edit is submitted. Kind of like Wikipedia, although I don't need the ability to actually roll things back and forward, I just need the changes to be identifiable.

Now, obviously I could do this by just storing a new row everytime the text is edited. But this could take up a lot more space than I'd like. Can anybody think of a way of storing only the changes, and not the data that stays constant between two edits

PS: I mean besides relying on the binary logs .

Posted On: Monday 29th of October 2012 06:09:37 AM Total Views:  311
View Complete with Replies

Related Messages:

Save changes made to DB   (108 Views)
I'm working on a membership database (MySQL 4.1) with about 350 members. The client wants every change made to be recorded so that there is a record of what was changed. So they want what row was changed, what was changed within that row, when it was changed, and who made the changes. Is there a way to so this automatically in MySQL or do I need to set up a new table and alter all existing queries
how to make changes to an .mdb file   (125 Views)
I`m sorry if i am in the wrong place, my first time. I have a inventory program that uses a .mdb file which I want to make changes to. Can anybody help me, Please.
Best Way to track user activity and changes?   (106 Views)
Hi all, just got a theory question about tracking users. I want to be able to keep track of every change a user makes to the database. I can easily keep track of users making inserts in to the database, but then when they update it, it becomes a little trickier. Im trying to come up with a solution that is basically a function and can work for any change to any table. What do you do for this I was thinking maybe building an updates table like this acct_id---table---field---original_value---new_value---date But then the amount of updates going on this table can get fairly large. Any thoughts
Would like to avoid iterating through data and UPDATING multiple times   (116 Views)
I've got product data that belongs to a category table. The business requirement is for the user to be allowed to update the order in which products appear in that category. There is a field called product.sequence for that very purpose. I'm being given the product id's in order for each category. Do I have to iterate through the data in PHP and UPDATE the table w/ many SQL statements, or is there one SQL statement where I can provide the list of products to update in order I've used MySQL user variables before (e.g. SELECT @m:=0; ) but I don't see how I can use it here... Is what I want possible in one query I hope I explained this clearly.
Mysql doesnt like this query   (140 Views)
Code: INSERT INTO `news` SET `subject` = '{$_POST['subject']}', `content` = '{$_POST['message']}', `date` = '" . date('Y-m-d') . "', `owner` = '{$_SESSION['user_id']}' I see no problem
How to store american DATE formate( like 27-02-2007) in mysql table???????????   (168 Views)
hi, i need to know desperately the way of storing american DATE formate( like 27-02-2007) in mysql table.i searched on the web a lot,but didnt find anything.PLZ i need someone's help. , ok thanzzz to thing.......if i hv a ORACLE server and a MYSQL server connected to a single network,then how could i do data operations among them since they store date in two different formats , so finally ......i take it that (forget about retrieving or any other thing) a MYSQL db table the only format DATE type takes is YYYY-MM-DD....isn't it , I think you are getting to technical Rudy, Yes the only date format that MySQL supports is YYYY-MM-DD.
MySQL 4.0.24 doesnt like COUNT(*)?   (170 Views)
Seems my Host uses version 4.0.24 and it is fussy about using COUNT(*) if I am reading the error message correctly. Is there another way to make this work for this version Code: LEFT OUTER JOIN ( select Players_Player_ID , count(*) as player_goals from goals group by Players_Player_ID ) as G
Count() like columns without grouping   (175 Views)
Could someone tell me whether or not this is possible. I have a very long query and I want to count the resulting rows by a certain field, but I don not want to group by that field. Grouping will throw off the results I am shooting for. here is an example: Code: +----------------------------------------+ | the_table | +--------+----------------+--------------+ | id | animal | name | +--------+----------------+--------------+ | 1 | dog | jocko | | 2 | dog | max | | 3 | cat | mr pipper | | 4 | rabbit | cottontail | | 5 | cat | hank | | 6 | dog | spot | | 7 | snake | monty | +--------+----------------+--------------+ SQL: SELECT *, count(animal) AS total FROM the_table +-------------------------------------------------+ | the_results | +--------+----------------+--------------+--------+ | id | animal | name | total | +--------+----------------+--------------+--------+ | 1 | dog | jocko | 3 | | 2 | dog | max | 3 | | 3 | cat | mr pipper | 2 | | 4 | rabbit | cottontail | 1 | | 5 | cat | hank | 2 | | 6 | dog | spot | 3 | | 7 | snake | monty | 1 | +--------+----------------+--------------+--------+
Mysql %like% question   (127 Views)
gday i have a list of keywords i want checked against a field in my db, and rather than doing repetitive sql's with %%, i was wondering if there was a way to, for example, check one field against 5 keywords, seperate by a comma, and only return it if it exists eg. a,b,c,d,e select subject from table where (a,b,c,d,e) IN %subject% any thoughts
Showing events like Monday - Friday: 5AM - 10AM   (108 Views)
I am currently working on a project which has got schedualed shows e.g Monday - Friday: 5AM - 10AM . how can i do this dynamically What i did so far is created a mysql table called datesTimes with : startDatTime, endDateTime and days (from Monday-Sunday). To enter a show: I enter start and end dates and times and then the name of the days e.g Monday, Thursday, Sat... (all in one field) if i want to select current show I go: Code: $sql = " SELECT * FROM shows, datesTimes WHERE shows.show_id = datesTimes.show_id " . " AND NOW() BETWEEN start_dateTime AND end_dateTime" ; My main problem is that there are a wide ragne of shows: e.g only Mondays, How can i arrange the days field to give me a comprehensive answere. e.g Monday-Friday or only Thursdays or ... any help would be appreciated
Visitor tracking and display results query   (149 Views)
Hi , I have a bit of a problem here (I know I'm being stupid, but cannot for the life of me figure out how to do this) I have a stats table for page visits (for a community website I am making for fun) and a graph to display the data, but at the moment am doing a query for each day of the current month for that particular user, then printing a comma (for the graph to work properly) Instead of having 31 different queries running on the page what is the best way to loop through the table and printing the data in thiformat: 0,5,6,3,0,4,3,6, etc (obviously 30 times and days with no visits printing 0 and always a comma in between) I apologize for my awful exaplanation but would greatly appreciate it if someone could give me a bit of advice (Im a real amateur at SQL and am struggling to understand what Im doing!) Here is a snippet of the code I am using currently: PHP Code:
Need report like below   (126 Views)
i have table like this, |-------------------------------------------------------------------| CREATE TABLE `products` ( `id` int(11) NOT NULL auto_increment, `merchant` varchar(255) NOT NULL default '', `name` varchar(255) NOT NULL default '', `description` text NOT NULL, `image_url` varchar(255) NOT NULL default '', `buy_url` text NOT NULL, `price` decimal(10,2) NOT NULL default '0.00', `search_name` varchar(255) NOT NULL default '', `category` varchar(255) NOT NULL default '', `brand` varchar(255) NOT NULL default '', `dupe_hash` varchar(32) NOT NULL default '', `rating` int(11) NOT NULL default '0', `reviews` int(11) NOT NULL default '0', PRIMARY KEY (`id`), UNIQUE KEY `dupe_filter` (`dupe_hash`), KEY `merchant` (`merchant`), KEY `name` (`name`), KEY `search_name` (`search_name`), KEY `category` (`category`), KEY `brand` (`brand`), FULLTEXT KEY `name_2` (`name`) ) ENGINE=MyISAM AUTO_INCREMENT=32185 DEFAULT CHARSET=utf8 AUTO_INCREMENT=32185 ; |-------------------------------------------------------------------| i need to display report as follows,( i need first sort by merchant & second brand) Merchant | Brand | No. Of products( related to merchant & brand) please reply soon,
any better way to select like this?   (132 Views)
I have a category_table with the fields like below: id category_name parent To get the Category Name and its parent name, we can use the simple but bad way like below: $query=mysql_query("Select * from category_table where category_name like '%tech%'"); while($row=mysql_fetch_array($query)){ $query2=mysql_query(""Select * from category_table where parent ='".$row['id']."'"); } May I know how to get the category name with its parent name in one query
Keeping track of record changes   (135 Views)
Hi all! Okay, I am new to PHP and MySQL, and here is an embarrassingly simple question since I am very rusty on database stuff... I want to keep track of when a record was created and updated. I thought I read somewhere that if you use a certain Data-Type that MySQL will automatically put a TimeStamp in a row anytime something changes. Is this true What is the best way to set things up in PHP (i.e. code) and MySQL (i.e. Data-Types and SQL) to keep track of Row Created and Row Changed info
deploy new changes to existing database   (205 Views)
, Several days ago, I needed to upgrade one application by deploying some changes to one SQL server database. I tried to use ApexSQL diff tool but I got many error messages due to the dependencies (PKs and FKs). What's the best way to generate SQL scripts that upgrades my old database schema to match the new schema without data loss.
How to search like   (162 Views)
I need to make a control that allow user to search a string on multiple tables in our database. User can type something such as: -How to find a English book -How much is that furniture Our customer want something like search control but it might be simpler. Can you tell me where I should start from. Thank in advance.
what is the diff. b/w match and like   (120 Views)
, I try to write this query Code: SELECT SQL_CALC_FOUND_ROWS as id, Ch.name_arabic as ChName, Ch.logo as pic , Lang.name_arabic as Lang , Class.name_arabic as Class, Grp.grpname_arabic as 'Group', Owner.ownername_arabic as Owner FROM channels AS Ch , classifications AS Class , languages AS Lang , ownership AS Owner , groups AS Grp WHERE ( = Ch.language_id) AND ( = Ch.classification_id) AND ( = Ch.ownership_id) AND ( = Ch.group_id) AND (MATCH (Ch.name_arabic) AGAINST ('%2M%')) ORDER BY LIMIT 0,5 it's not working , but when write it using like Code: SELECT SQL_CALC_FOUND_ROWS as id, Ch.name_arabic as ChName, Ch.logo as pic , Lang.name_arabic as Lang , Class.name_arabic as Class, Grp.grpname_arabic as 'Group', Owner.ownername_arabic as Owner FROM channels AS Ch , classifications AS Class , languages AS Lang , ownership AS Owner , groups AS Grp WHERE ( = Ch.language_id) AND ( = Ch.classification_id) AND ( = Ch.ownership_id) AND ( = Ch.group_id) AND (Ch.name_arabic like '%2M%') ORDER BY LIMIT 0,5 every thing goes ok , and there is data, I don't know the reson, where my table collection is "cp1256_general_ci" what is the problem
Why is this reporting like this   (166 Views)
Would like some help with Joins query   (141 Views)
Hi , I find Join's hard. Especially involving several tables. In this case I have a simple ACL setup in my PHP app, essentially, a user is assigned to a group and in turn each group is assigned a set of permissions. Ultimately i need to be able to query what permissions (i.e. get a list of permission names) a particular user has, but I cannot seem to get my query to return a deed result acl_ I have the following table structure ; Code: acl_users: uid | username acl_groups gid | groupname acl_permissions pid | permissionname acl_users_to_groups uid | gid acl_groups_to_permissions gid | pid And here's my query: PHP Code: $user_permissions=mysql_query('SELECTacl_users.uid,acl_groups.groupname,acl_permissions.permissionnameFROMuser LEFTJOINacl_users_to_groupsONuser_to_groups.uid=acl_users.uid LEFTJOINacl_groupsONacl_groups.gid=acl_users_to_groups.gid LEFTJOINacl_groups_to_permissionsONacl_groups_to_permissions.gid=acl_groups.gid WHEREacl_users.uid=1'); while($permissions=mysql_fetch_array($user_permissions)){ print_r($permissions); echo''; } Code: ['uid'] => 1, ['groupname']=>webmaster, ['permissionsname']=>NULL ['uid'] => 1, ['groupname']=>support,['permissionsname']=>NULL
If you like this, you'll like this Mysql query??   (133 Views)
, I'm trying to implement a feature on my master product detail product page that basically lists say 12-15 random records based on the same "Genre" of the main product displayed, I'm passing a productID from another page in a url string to my master detail page and i'm stumped on how to match the genres. For example if the main product's genre on the master detail page is "Heavy metal" then i want to randomly display other Cd's on the page for the genre "heavy metal" In my products table i have a "genreID" linked to the "genre table" so i'm wondering what kind of table joins and statements would i need to use to do this and make it random and match the genres based on that url parameter any insight on an approach would be greatly appreciated