How to make revisions of your content using PHP

here 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;

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;

Now we switch to PHP code:

Connect to database:

$host='';
$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;

Update the content id into revisions table

$updatequery = 'UPDATE revision_contents SET content_id='.$content_id.' WHERE id='.$revision_id;
$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;

Update the latest revision id into contents table

$updatequery = 'UPDATE contents SET revision_id='.$revision_id.' WHERE id='.$content_id;
$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']);

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.