How to make revisions of your content using PHP

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

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.

Anil Kumar Panigrahi

With more than 10 years experience in PHP and Founder of Anil Labs, a blog for PHP and related posts. contributed posts regarding CodeIgniter, CakePHP and Learn PHP online ... Contact : [email protected]

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *