PHP和MySQL Web开发从新手到高手,第7天-创建author管理页面

1. 创建author管理页面

  主要包含以下几个页面:

  A. index.php, 准备各种变量数据.供展示页面使用.

  B. authors.html.php, 显示author.

  C. form.html.php, 用于编缉或添加作者的页面.

页面郊果:

 

2. author页面的主要流程

2.1 是否已登录

if (!user_is_login()){
      include '../login.html.php';
      exit();
  }

2.2 是否有权限

if (!user_has_role('Content Editor')){
      $error = 'only Content Editor may access this page...';
      include '../access.denied.html.php';
      exit();
  }

2.3 添加作者

// add authors
  if (isset($_GET['add'])) {
      include $_SERVER['DOCUMENT_ROOT'].'/PHP_MySQL_Web/includes/db.inc.php';
      
      $pageTitle = 'Add author';
      $action = 'addForm';
      $author_name = '';
      $author_email = '';
      $id = '';
      $author_password = '';
      $button = 'Add author';
      
      try {  
          $result = $pdo->query('SELECT id, description FROM role');
          
          foreach ($result as $row){
              $roles[] = array(
                  'id'=>$row['id'],
                  'description'=>$row['description'],
                  'selected'=>false
              );
          }
      } catch (PDOException $e) {
          $error = 'Error getting list of jokes to edit...'.$e->getMessage();
          include $_SERVER['DOCUMENT_ROOT'].'/PHP_MySQL_Web/includes/error.html.php';
          exit();
      }      
      
      include 'form.html.php';
      exit();
  }
  
  // add authors
  if (isset($_GET['addForm']) && $_POST['author_name'] != '') {
      include $_SERVER['DOCUMENT_ROOT'].'/PHP_MySQL_Web/includes/db.inc.php';
       
      // add author
      try {
          $sql = 'INSERT INTO author(name,email) VALUES(:name,:email);';
          $s = $pdo->prepare($sql);              
          $s->bindValue(':name', $_POST['author_name']);
          $s->bindValue(':email', $_POST['author_email']);  
          $s->execute();
      } catch (PDOException $e) {
          $error = 'Error adding author...'.$e->getMessage();
          include $_SERVER['DOCUMENT_ROOT'].'/PHP_MySQL_Web/includes/error.html.php';
          exit();
      }
      
      $authorid = $pdo->lastInsertId();
      
      // add password
      if ($_POST['author_password'] != ''){          
          try {
              $sql = 'UPDATE author SET password = :password where id = :authorid;';
              $s = $pdo->prepare($sql);
              $password = get_md5_password($_POST['author_password']);
              $s->bindValue(':password', $password); 
              $s->bindValue(':authorid', $authorid); 
              $s->execute(); 
          } catch (PDOException $e) {
              $error = 'Error updating password...'.$e->getMessage();
              include $_SERVER['DOCUMENT_ROOT'].'/PHP_MySQL_Web/includes/error.html.php';
              exit();
          } 
      }
      
      // insert roles
      if (isset($_POST['roles'])){
          foreach ($_POST['roles'] as $role){
              try {
                  $role_role .= $role;
                  $sql = 'INSERT INTO authorrole(authorid, roleid) VALUES (:authorid,:roleid);';
                  $s = $pdo->prepare($sql);
                  $s->bindValue(':authorid', $authorid);
                  $s->bindValue(':roleid', $role);
                  $s->execute();
              } catch (PDOException $e) {
                  $error = 'Error insertting role...'.$e->getMessage();
                  include $_SERVER['DOCUMENT_ROOT'].'/PHP_MySQL_Web/includes/error.html.php';
                  exit();
              }
          }
      }
      
      header('Location: .');
      exit();
  }

2.4 编辑作者

// edit authors
  if (isset($_POST['action']) and $_POST['action'] == 'Edit') {
      include $_SERVER['DOCUMENT_ROOT'].'/PHP_MySQL_Web/includes/db.inc.php';
      
      // get list of authors
      try {
          $sql = 'SELECT name, email FROM author WHERE id = :id';
          $s = $pdo->prepare($sql);
          $s->bindValue(':id', $_POST['id']);
          $s->execute();
      } catch (PDOException $e) {
          $error = 'Error getting list of roles to edit...'.$e->getMessage();
          include $_SERVER['DOCUMENT_ROOT'].'/PHP_MySQL_Web/includes/error.html.php';
          exit();
      }
      
      // get list of roles
    try {
          $sql = 'SELECT authorid, roleid FROM authorrole WHERE authorid = :authorid';
          $s = $pdo->prepare($sql);
          $s->bindValue(':authorid', $_POST['id']);
          $s->execute(); 
          $result = $s->fetchAll();     
      } catch (PDOException $e) {
          $error = 'Error getting list of roles to edit...'.$e->getMessage();
          include $_SERVER['DOCUMENT_ROOT'].'/PHP_MySQL_Web/includes/error.html.php';
          exit();
      }    
      
      $roles_valid[] = array();
      foreach ($result as $row){
          $roles_valid[] = $row['roleid'];
      }
      
      // get roles belong to this author.  
      try {
          $result = $pdo->query('SELECT id, description FROM role');
      
          foreach ($result as $row){
              $roles[] = array(
                      'id'=>$row['id'],
                      'description'=>$row['description'],
                      'selected'=>in_array($row['id'], $roles_valid)
              );
          }
      } catch (PDOException $e) {
          $error = 'Error getting list of jokes to edit...'.$e->getMessage();
          include $_SERVER['DOCUMENT_ROOT'].'/PHP_MySQL_Web/includes/error.html.php';
          exit();
      }
      // get author info.
      try {
          $sql = 'SELECT name, email FROM author WHERE id = :id';
          $s = $pdo->prepare($sql);
          $s->bindValue(':id', $_POST['id']);
          $s->execute();
  
          $row = $s->fetch();
  
          $pageTitle = 'Edit author';
          $action = 'editForm';
          $author_name = $row['name'];
          $author_email = $row['email'];
          $id = $_POST['id'];
          $button = 'Update author';
              
          include 'form.html.php';
          exit(); 
  
      } catch (PDOException $e) {
          $error = 'Error getting list of jokes to edit...'.$e->getMessage();
          include $_SERVER['DOCUMENT_ROOT'].'/PHP_MySQL_Web/includes/error.html.php';
          exit();
      }
  }
  
  // edit authors
  if (isset($_GET['editForm'])) {
      include $_SERVER['DOCUMENT_ROOT'].'/PHP_MySQL_Web/includes/db.inc.php';
      // update author info
      try {
          $sql = 'UPDATE author SET
              name = :name,
              email = :email
              WHERE id = :id;';
          $s = $pdo->prepare($sql);
           
          $s->bindValue(':name', $_POST['author_name']);
          $s->bindValue(':email', $_POST['author_email']);
          $s->bindValue(':id', $_POST['id']);
          
          $s->execute();
      } catch (PDOException $e) {
          $error = 'Error getting list of jokes to delete...'.$e->getMessage();
          include $_SERVER['DOCUMENT_ROOT'].'/PHP_MySQL_Web/includes/error.html.php';
          exit();
      }
      
      // add password
      if ($_POST['author_password'] != ''){
          try {
              $sql = 'UPDATE author SET password = :password where id = :authorid;';
              $s = $pdo->prepare($sql);
              $password = get_md5_password($_POST['author_password']);
              $s->bindValue(':password', $password);
              $s->bindValue(':authorid', $_POST['id']);
              $s->execute();
          } catch (PDOException $e) {
              $error = 'Error updating password...'.$e->getMessage();
              include $_SERVER['DOCUMENT_ROOT'].'/PHP_MySQL_Web/includes/error.html.php';
              exit();
          }
      }
      
      // delete old value
      try {
          $sql = 'DELETE FROM authorrole WHERE authorid = :authorid;';
          $s = $pdo->prepare($sql);          
          $s->bindValue(':authorid', $_POST['id']);      
          $s->execute();
      } catch (PDOException $e) {
          $error = 'Error deleting authorrole...'.$e->getMessage();
          include $_SERVER['DOCUMENT_ROOT'].'/PHP_MySQL_Web/includes/error.html.php';
          exit();
      }
      
      // insert new value
      try {
      // insert roles
          if (isset($_POST['roles'])){
              foreach ($_POST['roles'] as $role){
                  try {
                      $sql = 'INSERT INTO authorrole(authorid, roleid) VALUES (:authorid,:roleid);';
                      $s = $pdo->prepare($sql);
                      $s->bindValue(':authorid', $_POST['id']);
                      $s->bindValue(':roleid', $role);
                      $s->execute();
                  } catch (PDOException $e) {
                      $error = 'Error insertting role...'.$e->getMessage();
                      include $_SERVER['DOCUMENT_ROOT'].'/PHP_MySQL_Web/includes/error.html.php';
                      exit();
                  }
              }
          }
      } catch (PDOException $e) {
          $error = 'Error insertting authorrole...'.$e->getMessage();
          include $_SERVER['DOCUMENT_ROOT'].'/PHP_MySQL_Web/includes/error.html.php';
          exit();
      }

      header('Location: .');
      exit();
  }

2.6 删除作者

if (isset($_POST['action']) and $_POST['action'] == 'Delete') {      

      include $_SERVER['DOCUMENT_ROOT'].'/PHP_MySQL_Web/includes/db.inc.php';
      // get jokes belonging to author
      try {
          $sql = 'SELECT id FROM joke WHERE authorid = :id';
          $s = $pdo->prepare($sql);
          $s->bindValue(':id', $_POST['id']);
          $s->execute();          
      } catch (PDOException $e) {
          $error = 'Error getting list of jokes to delete...'.$e->getMessage();
          include $_SERVER['DOCUMENT_ROOT'].'/PHP_MySQL_Web/includes/error.html.php';
          exit();
      }
      
      $result = $s->fetchAll();
      
      // delete joke category entries
      try {
          $sql = 'DELETE FROM jokecategory WHERE jokeid = :jokeid';
          $s = $pdo->prepare($sql);
          // delete the joke by id
          foreach ($result as $row) {
              $jokeId = $row['id'];
              $s->bindValue(':jokeid', $jokeId);
              $s->execute();
          }
      } catch (PDOException $e) {
          $error = 'Error deleting jokecategory...'.$e->getMessage();
          include $_SERVER['DOCUMENT_ROOT'].'/PHP_MySQL_Web/includes/error.html.php';
          exit();
      }
      
      // delete jokes belongto author
      try {
          $sql = 'DELETE FROM joke WHERE authorid = :id';
          $s = $pdo->prepare($sql);
          $s->bindValue(':id', $_POST['id']);
          $s->execute();
      } catch (PDOException $e) {
          $error = 'Error deleting joke...';
          include $_SERVER['DOCUMENT_ROOT'].'/PHP_MySQL_Web/includes/error.html.php';
          exit();
      }
      
      // delete author roles
      try {
          $sql = 'DELETE FROM authorrole WHERE authorid = :authorid';
          $s = $pdo->prepare($sql);
          $s->bindValue(':authorid', $_POST['id']);
          $s->execute();
      } catch (PDOException $e) {
          $error = 'Error deleting authorrole...'.$e->getMessage();
          include $_SERVER['DOCUMENT_ROOT'].'/PHP_MySQL_Web/includes/error.html.php';
          exit();
      }
      
      // delete author
      try {
          $sql = 'DELETE FROM author WHERE id = :id';
          $s = $pdo->prepare($sql);
          $s->bindValue(':id', $_POST['id']);
          $s->execute();
      } catch (PDOException $e) {
          $error = 'Error deleting author...'.$e->getMessage();
          include $_SERVER['DOCUMENT_ROOT'].'/PHP_MySQL_Web/includes/error.html.php';
          exit();
      }

      header('Location: .');
      exit();
  }

2.7 显示作者列表

// select all authors.
  try {

      include $_SERVER['DOCUMENT_ROOT'].'/PHP_MySQL_Web/includes/db.inc.php';
      $querySql = "SELECT id, name, email FROM author;";
      $result = $pdo->query($querySql);      

      foreach ($result as $row) {
          $authors[] = array('id'=>$row['id'], 'name'=>$row['name'], 'email'=>$row['email']);          
      }          
      
      if (count($authors) > 0) {
          $sql = "SELECT table_rows AS RCT FROM information_schema.tables WHERE table_name = 'author';";
          $s = $pdo->prepare($sql);
          $s->execute();
          $row = $s->fetch();
          $rowCount = $row['RCT'];
          include $_SERVER['DOCUMENT_ROOT'].'/PHP_MySQL_Web/admin/authors/authors.html.php';
      } else {
          
      }    
  } catch (PDOException $e) {
      $error = "query author failed...".$e->getMessage();
      
      include $_SERVER['DOCUMENT_ROOT'].'/PHP_MySQL_Web/includes/error.html.php';
      exit();
  }