There are many ways to maintain the revisions using PHP and MySQL. I am going to explain with a small example for how to make revisions of your content using PHP.
How to make revisions of your content using PHP by Anil Labs – an Anil Kumar Panigrahi’s tech blog
Take database tables as
contents table:
--
-- Table structure for table `contents`
--
CREATE TABLE `contents` (
`id` int(11) NOT NULL,
`title` varchar(255) NOT NULL,
`revision_id` int(11) NOT NULL,
`updated_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- Indexes for dumped tables
--
--
-- Indexes for table `contents`
--
ALTER TABLE `contents`
ADD PRIMARY KEY (`id`);
--
-- AUTO_INCREMENT for dumped tables
--
--
-- AUTO_INCREMENT for table `contents`
--
ALTER TABLE `contents`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
-- Table structure for table `contents`
--
CREATE TABLE `contents` (
`id` int(11) NOT NULL,
`title` varchar(255) NOT NULL,
`revision_id` int(11) NOT NULL,
`updated_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- Indexes for dumped tables
--
--
-- Indexes for table `contents`
--
ALTER TABLE `contents`
ADD PRIMARY KEY (`id`);
--
-- AUTO_INCREMENT for dumped tables
--
--
-- AUTO_INCREMENT for table `contents`
--
ALTER TABLE `contents`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
Revision contents Table:
--
-- Table structure for table `revision_contents`
--
CREATE TABLE `revision_contents` (
`id` int(11) NOT NULL,
`content` text NOT NULL,
`content_id` int(11) NOT NULL DEFAULT '0',
`modified_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- Indexes for dumped tables
--
--
-- Indexes for table `revision_contents`
--
ALTER TABLE `revision_contents`
ADD PRIMARY KEY (`id`);
--
-- AUTO_INCREMENT for dumped tables
--
--
-- AUTO_INCREMENT for table `revision_contents`
--
ALTER TABLE `revision_contents`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
-- Table structure for table `revision_contents`
--
CREATE TABLE `revision_contents` (
`id` int(11) NOT NULL,
`content` text NOT NULL,
`content_id` int(11) NOT NULL DEFAULT '0',
`modified_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- Indexes for dumped tables
--
--
-- Indexes for table `revision_contents`
--
ALTER TABLE `revision_contents`
ADD PRIMARY KEY (`id`);
--
-- AUTO_INCREMENT for dumped tables
--
--
-- AUTO_INCREMENT for table `revision_contents`
--
ALTER TABLE `revision_contents`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
Now we switch to PHP code:
$host='';
$username='';
$password='';
$databasename='';
$mysqli = new mysqli($host, $username, $password, $password);
$username='';
$password='';
$databasename='';
$mysqli = new mysqli($host, $username, $password, $password);
Insert query:
$query = "INSERT INTO revision_contents (content) values('".addslashes($content)."')";
$result = $mysqli->query($query);
$revision_id=$mysqli->insert_id;
$query1 = "INSERT INTO contents (title,revision_id) values('".addslashes($title)."',".$revision_id.")";
$result1 = $mysqli->query($query1);
$content_id=$mysqli->insert_id;
$result = $mysqli->query($query);
$revision_id=$mysqli->insert_id;
$query1 = "INSERT INTO contents (title,revision_id) values('".addslashes($title)."',".$revision_id.")";
$result1 = $mysqli->query($query1);
$content_id=$mysqli->insert_id;
Update the content id into revisions table
$updatequery = 'UPDATE revision_contents SET content_id='.$content_id.' WHERE id='.$revision_id;
$result1 = $mysqli->query($query1);
$result1 = $mysqli->query($query1);
Update query:
$insertquery = "INSERT INTO revision_contents (content,content_id) values('".addslashes($content)."',".$content_id.")";
$result = $mysqli->query($insertquery);
$revision_id=$mysqli->insert_id;
$result = $mysqli->query($insertquery);
$revision_id=$mysqli->insert_id;
Update the latest revision id into contents table
$updatequery = 'UPDATE contents SET revision_id='.$revision_id.' WHERE id='.$content_id;
$result1 = $mysqli->query($query1);
$result1 = $mysqli->query($query1);
View query:
$selectquery = 'SELECT c.id,c.title,c.revision_id,rc.id,rc.content,rc.content_id where c.revision_id=rc.id';
$result = $mysqli->query($selectquery);
$row = $result->fetch_array(MYSQLI_ASSOC);
echo stripslashes($row['title'])." ".stripslashes($row['content']);
$result = $mysqli->query($selectquery);
$row = $result->fetch_array(MYSQLI_ASSOC);
echo stripslashes($row['title'])." ".stripslashes($row['content']);
Hope it will useful for your development. One disadvantage of this process is we are inserting all the content each time. For simple revision system it will be useful.
