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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
--
-- 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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
--
-- 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:

1
2
3
4
5
$host='';
$username='';
$password='';
$databasename='';
$mysqli = new mysqli($host, $username, $password, $password);

Insert query:

1
2
3
4
5
6
7
$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

1
2
$updatequery = 'UPDATE revision_contents SET content_id='.$content_id.' WHERE id='.$revision_id;
$result1 = $mysqli->query($query1);

Update query:

1
2
3
$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

1
2
$updatequery = 'UPDATE contents SET revision_id='.$revision_id.' WHERE id='.$content_id;
$result1 = $mysqli->query($query1);

View query:

1
2
3
4
$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.


1 Comment

File Extension Using PHP - Anil Labs · November 1, 2023 at 1:53 pm

[…] content. In this post, we’ll delve into two distinct methods for fetching file extensions in PHP, equipping you with the knowledge you need to efficiently work with different file types in your […]

Leave a Reply

Avatar placeholder

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