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.
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:
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 […]