Database mysql PHP PHP Code

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.

No Comments

    Leave a Reply

    This site uses Akismet to reduce spam. Learn how your comment data is processed.