Wiki Type editing

Original content is lost!

[kkonline]

I am working on an article manager. The table has 2 field by name of modified and trusted.

modified trusted meaning
0 0 Submission of form (by user) having content
1 0 User has performed some editing
0 1 By Admin. Contents having this configurations are only displayed on the website

Everything is working fine. However I have one issue in mind.

Whenever a user edits the content then modified=1 and trusted=0 and that content will not be displayed. Now the modified content is available in the database. But i loose the original content as i am using UPDATE command in mysql!

So is there any solution (simple one) so that i donot loose the original content and also get the modifications.

I have a solution but i don't think it's good enough. If i have title,name and content as fields then now i can have another set of field as modifiedtitle, modifiedname and modifiedcontent/. So whatever the user modifies that will go in modified table(title, name and content)and not overwrite original data. Any better solution?

[Habtom]

So is there any solution (simple one) so that i donot loose the original content and also get the modifications.

The normal procedure is to maintain a history table. Moments before you update the data, you can SELECT and INSERT into a history table with timestamp of the time it was added.

That way you can add as many history modifications as you want, and reverse back to them when needed.

Habtom

[kkonline]

The below is the extract of article editing which i am using. Rather than having timestamp in database i use

$date=strtotime("now")

and then send $date in db. Is that ok?

please guide me how to make the changes using INSERT

if (isset($_POST['content'])) {
// data has been sent
 // get data and make it safe for query
 $id = mysql_real_escape_string($_POST['id']);
 $name = mysql_real_escape_string($_POST['name']);
 $title = mysql_real_escape_string($_POST['title']);
 $content = mysql_real_escape_string($_POST['content']);
 $mood = mysql_real_escape_string($_POST['mood']);
 $tags = mysql_real_escape_string($_POST['tags']);
 $date = strtotime("now");
 $ip = $_SERVER['REMOTE_ADDR'];
 // query to edit opening
 $edit = "UPDATE wow SET
 contributed_by = '$name',
 content = '$content',
 title = '$title',
 mood = '$mood',
 tags = '$tags',
 date = '$date',
 ip = '$ip',
 trusted='0',
 modified='1'
 WHERE id = $id";
 $result = mysql_query($edit) or die (mysql_error());
 
 // did it work?
 if ($result) {
 echo 'Thank you.<br /><br />';
 } else {
 echo 'Error! The values sent are below.<br /><br />';
 echo $content;
 }
}
 if (isset($_GET['id'])) {
 $edit = "SELECT * FROM wow WHERE id = {$_GET['id']}";
 $result = mysql_query($edit) or die (mysql_error());
 $row = mysql_fetch_array($result);
?>

[Habtom]

Something like the following should do it for you. Create a table wow_hisotry having the same exact fields as wow, so that the whole transfer would be easy for you.

Check this:

$add_history = "INSERT INTO wow_history SELECT * FROM wow WHERE id = $id"; ";
mysql_query($add_history)

// query to edit opening
$edit = "UPDATE wow SET
contributed_by = '$name',
content = '$content',
title = '$title',
mood = '$mood',
tags = '$tags',
date = '$date',
ip = '$ip',
trusted='0',
modified='1'
WHERE id = $id";
$result = mysql_query($edit) or die (mysql_error());

Habtom

[kkonline]

I am using date=strtotime("now"); and then send it to db rather than using timestamp(actually didn't know there was a data type in sql like that!)
so will that work fine?

Now there is some original data in wow table; ... ok

Next someone edits the data then the actual data is available in wow_history and new data in wow correct... ok

Now there is third person who edits the same article then his data goes to wow. Previous edited data goes to wow_history replacing/overwriting the the actual data with the first edited data... so i am still at square one from where i started.

I want all the data to remain in db without replacing the original data.

[Habtom]

Previous edited data goes to wow_history replacing/overwriting the the actual data with the first edited data...

The history will keep all your changes. There is no replacement there, only inserting values. History is not to be modified, it is to be kept.

[kkonline]

I now understand what you said. ;)

Next I have a variable $view for counting the number of views to a particular article having unique id. so how do i update it . I just know that i have to extract the current view from database , then view=$view+1; and then update the database. But in terms of php language how do i do this?

[Habtom]

You can do it all on the query similar to the following:

Update table SET view=view+1 WHERE id = $id;

This just increments the value of the data in the database.

Habtom

[kkonline]

Dear Hab,
The way you suggested (creating wow_history) it worked in following fashion.

Whatever changes i do those are reflected on wow but the wow_history table only has the original entry and the previous edits are not stored.

The wow_table and wow table both have id as auto increment.

if i write

$add_history = "INSERT INTO wow_history(contributed_by) SELECT (contributed_by) FROM wow WHERE id = $id";
mysql_query($add_history);

Then all the changes in contributed by field are logged (all the edits are noted)

So something should be done to id, so i can use something like

$add_history = "INSERT INTO wow_history SELECT * FROM wow WHERE id = $id";
mysql_query($add_history);

I want to use * as you suggested. There is some kind of logical problem with the id field(auto increment) for wow_history which i can't figure out. Help Hab Help!

[kkonline]

Hab, i solved the above problem by not using auto increment on id in wow_history.

I use

CREATE TABLE `wow_history` (
 `id` int(11) unsigned default NULL,
 `contributed_by` text,
 `title` tinytext,
 `content` text,
 `date` bigint(20) NOT NULL default '0',
 `mood` int(2) NOT NULL default '0',
 `tags` text,
 `trusted` tinyint(1) NOT NULL default '0',
 `modified` tinyint(1) NOT NULL default '0',
 `ip` varchar(80) NOT NULL default ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

But it says No index defined in the table structure is that ok?
Can i make timestamp/date as an index as i think it would be unique?

[Habtom]

It seems you need to create a link between the history and the main table, and the one link you have is the main table's id. Use auto-increment in the history, but store the id in the history table also as one more additional field. Were you able to create a link between those two tables. How do you know which history belongs to which row in the main table?

Hab

[kkonline]

Were you able to create a link between those two tables. How do you know which history belongs to which row in the main table?

Hab

-------
I was able to make a link using the following
Current status,
id in main table is autoincremented ... asit should do.
id in history table is not autoincremented

so when i write

$add_history = "INSERT INTO history SELECT * FROM wow WHERE id = $id";

before updating the main table, then the current content of main table is copied to history table

i mean when main table article having id 6 is edited then the history table has id 6 and the maintable content. If again the edited main table content is edited (having id 6) then the new edited contents go to maintable and the previous edits go to history table (history table has 2 row ... because two edits but both rows in history have same id as 6 as id 8 article of main table is edited)

I tried using uid as autoincrementing field but then no contents of main is copied to history.

The table i created was CREATE TABLE `wow_history` (
`uid` int(11) unsigned NOT NULL auto_increment,
`id` int(11) default NULL,
.
.
`ip` varchar(80) NOT NULL default '',
PRIMARY KEY (`uid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

That means the main and history tables have to be exactly same and in history i should not autoincrement id then it works perfect. But then How do i define an index. Is it necessary?
Can i make timestamp as primary index as it will always be unique?