Sunday, September 25, 2011

Design a editable webpage and keep ten revisions using PHP MySQL


Purpose
Design a editable webpage and keep ten revisions.
Procedure using PHP/MySQL
1) Create two tables.
First table  web_content store current content of webpage
CREATE TABLE web_content (
  `content_id` mediumint(8) unsigned NOT NULL auto_increment,
  `revision` mediumint(8) unsigned NOT NULL default '0',
  `title` varchar(255) NOT NULL,
  `text` text NOT NULL,
  PRIMARY KEY  (`content_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;
Another table web_content_rev to store 10 revison
CREATE TABLE web_content_rev (
  `content_id` mediumint(8) unsigned NOT NULL auto_increment,
  `revision` mediumint(8) unsigned NOT NULL default '0',
  `title` varchar(255) NOT NULL,
  `text` text NOT NULL,
  PRIMARY KEY  (`content_id`, `revision`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;
2) page_id as a dynamic varable, create save, cancel,  restore revision buttons, load external editor, display page content and the content in the editor at the same time.
<?php

  //page_id from web site for example index.php?page_id=1
  if(isset($_GET['content_id'])){
      $content_id=$_GET['content_id'];}
   else {
    // The "i" after the pattern delimiter indicates a case-insensitive search
       if (preg_match("/index.php/i",$_SERVER['PHP_SELF']))  $content_id=1;
    }   
 ?>


<?php
//Jiansen Lu, Sept 23, 2011
     global $db;
    $sql = "SELECT text, revision  FROM web_content WHERE content_id=$content_id";
    $result = mysql_query($sql, $db);

    while ($row = mysql_fetch_assoc($result)) {
        $content = $row['text'];
        $revision = $row['revision'];
    }
    $sql_3 = "SELECT revision  FROM web_content_rev WHERE content_id=$content_id";
    $result_3 = mysql_query($sql_3, $db);
    $i=0;
    while ($row = mysql_fetch_assoc($result_3)) {   
        $revision_1[$i] = $row['revision'];
        $i=$i+1;
     }
       
        //if you are admin and the page editable ie edit=1, index.php?edit=1&page_id=1
    if($_GET['edit']==1&&isset($_SESSION['portal_status']) && $_SESSION['portal_status']['admin'] == STATUS_ADMIN){
        if ($_POST['save']=="Save") {
           $sql_1 = "INSERT INTO web_content_rev SELECT * FROM web_content  WHERE content_id=$content_id";
           $result0 = mysql_query($sql_1, $db);
           //delete revisions,investigaor and references older than 10 ago
           $sql_2 = "DELETE FROM  web_content_rev WHERE content_id=$content_id AND revision < ($revision-9)";
           @mysql_query($sql_2,$db);
//prevent MySQL injection using  mysql_real_escape_string
           $sql0 = "UPDATE web_content set text='".mysql_real_escape_string($_POST['doc_body'])." ', revision=revision+1 WHERE content_id=1";
           $result0 = mysql_query($sql0, $db);
        }
        else if ($_POST['cancel']=="Cancel") {
         if($content_id==1)
           header('Location: /index.php');
           exit();
         }
   
    }
   
     if ($_POST['restore']=="Restore Revision") {
     
        if(!isset($_POST['rid'])){
            $msg->addError('You must select a revision first.');
        }else{
            $sql_5 = "SELECT text  FROM web_content_rev WHERE content_id=1 AND revision=".$_POST['rid'];
            $result_5 = mysql_query($sql_5, $db);
            while ($row = mysql_fetch_assoc($result_5)) {
            $content = $row['text'];
            }
        }
    }
    echo ($content);
    if($_GET['edit']==1&&isset($_SESSION['portal_status']) && $_SESSION['portal_status']['cesei'] == STATUS_ADMIN){
?>
<form action="<?php echo $_SERVER['PHP_SELF'].'?edit=1&content_id='.$content_id; ?>" method="post" name="form_1_global">
<input type="submit" class="input-button" value="Save" name="save" class="input-button"/>
<input type="submit" name="cancel" value="Cancel" class="input-button" />
<input type="submit" name="restore" value="Restore Revision" class="input-button" />
<table>
<tbody>
<?php foreach($revision_1 as $revision1): ?>
   
         <tr valign="middle"><input type="radio" name="rid" value="<?php echo $revision1; ?>" id="p_<?php echo $revision1; ?>" /></tr>
         <tr><?php echo $revision1."&nbsp&nbsp&nbsp&nbsp"; ?></tr>
     
<?php endforeach; ?>
 </tbody>
 </table>
<textarea name="doc_body" id="doc_body" rows="5" class="mce-editor" style="width:100%; height:700px;"><?php echo (htmlspecialchars($content)); ?></textarea>
 
  <?php
//local external mce editor
 load_editor();

 ?>
 </form>
 <?php
 }
 ?>

No comments:

Post a Comment