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

1. 创建joke管理页面

  主要包含以下几个页面:

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

  B. jokes.html.php, 显示joke.

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

  D. search.form.html.php, 用于搜索笑话的面面.

页面郊果:

2. index.php的主要流程

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 search joke function
  if (isset($_GET['searchjoke']))
  {
      // build list of authors 
      try {
          $statement = 'select id, name from author';
          $result = $pdo->query($statement);

          foreach($result as $row) {
              $authors[] = array('id'=>$row['id'], 'name'=>$row['name']);
          }      
      } catch (PDOException $e) {
          $error = 'Query authors faild...'
                  .$e->getMessage();
          include $_SERVER['DOCUMENT_ROOT'].'/PHP_MySQL_Web/includes/error.html.php';
          exit();
      }
      
      // build list of categories
      try {
          $statement = 'select id,name from category;';
          $result = $pdo->query($statement);
           
          foreach($result as $row) {
              $categories[] = array('id'=>$row['id'], 'name'=>$row['name'], 'selected'=>false);
          }
      } catch (PDOException $e) {
          $error = 'Query categories faild...'
                  .$e->getMessage();
          include $_SERVER['DOCUMENT_ROOT'].'/PHP_MySQL_Web/includes/error.html.php';
          exit();
      }
      include 'search.form.html.php';
      exit();
  }
  
  // push search button 
  if (isset($_GET['action']) && $_GET['action'] == 'search'){      
      $select = ' select joke.id, joketext, name, email ';
      $from = ' from joke LEFT JOIN author ON author.id = joke.authorid  ';
      $where = ' where true ';
      
      $placeholders = array();
      
    if ($_GET['author'] != '') {
          $where .= ' and authorid = :authorid ';
          $placeholders[':authorid'] = $_GET['author'];
      }
      if ($_GET['category'] != '') {
          $from .= ' inner join jokecategory on joke.id = jokeid ';      
          $where .= ' and categoryid = :categoryid ';
          $placeholders[':categoryid'] = $_GET['category'];
      }
      if ($_GET['text'] != '') {
          $where .= ' and joketext like :joketext ';
          $placeholders[':joketext'] = '%'.$_GET['text'].'%';
      }
      
      try {
          $sql = $select.$from.$where;
          $s = $pdo->prepare($sql);
          $s->execute($placeholders);
          $rowCount = $s->rowCount();
      } catch (PDOException $e) {
          $error = 'Query joke faild...'
                  .$e->getMessage();
      
          include $_SERVER['DOCUMENT_ROOT'].'/PHP_MySQL_Web/includes/error.html.php';
          exit();
      }
      
      foreach ($s as $row) {
          //$jokes[] = array('id'=>$row['id'],'name'=>$row['joketext']);
          $jokes[] = array('id'=>$row['id'],'text'=>$row['joketext'],'name'=>$row['name'],'email'=>$row['email']);
      }
      
      include 'jokes.html.php';
      exit(); 
  }

2.4 添加笑话

// add joke link
  if (isset($_GET['addjoke'])) {
      $pageTitle = 'Add joke';
      
      $jokeText='';
      
      $action = 'addJoke';
      $id = '';
      $button = 'Add joke';
      $authorid = '';
      
      // build list of authors
      try {
          $statement = 'select id, name from author';
          $result = $pdo->query($statement);
          
          foreach($result as $row) {
              $authors[] = array('id'=>$row['id'], 'name'=>$row['name']);
          }
          
      } catch (PDOException $e) {
          $error = 'Query authors faild...'
                  .$e->getMessage();
          include $_SERVER['DOCUMENT_ROOT'].'/PHP_MySQL_Web/includes/error.html.php';
          exit();
      }
      
      // build list of categories
      try {
          $statement = 'select id,name from category;';
          $result = $pdo->query($statement);
      
          foreach($result as $row) {
              $categories[] = array('id'=>$row['id'], 'name'=>$row['name'], 'selected'=>false);
          }          
      } catch (PDOException $e) {
          $error = 'Query categories faild...'
                  .$e->getMessage();
          include $_SERVER['DOCUMENT_ROOT'].'/PHP_MySQL_Web/includes/error.html.php';
          exit();
      }
      
      include 'form.html.php';
      exit();
  }

  // add joke button
  if (isset($_GET['addJoke'])) { 
      // error tips...
      if($_POST['jokeText'] == ''){
          $error = 'jokeText is empty...';
          include $_SERVER['DOCUMENT_ROOT'].'/PHP_MySQL_Web/includes/error.html.php';
          exit();
      }      
      if($_POST['author'] == ''){
          $error = 'author is empty...';
          include $_SERVER['DOCUMENT_ROOT'].'/PHP_MySQL_Web/includes/error.html.php';
          exit();
      }
      if(!isset($_POST['categories'])){
          $error = 'categories is empty...';
          include $_SERVER['DOCUMENT_ROOT'].'/PHP_MySQL_Web/includes/error.html.php';
          exit();
      }
      
      try {
          $statement = 'INSERT INTO joke SET joketext = :joketext, authorid=:authorid, jokedate = CURDATE()';
          $s = $GLOBALS['pdo']->prepare($statement);
          $s->bindValue(':joketext', $_POST['jokeText']);
          $s->bindValue(':authorid', $_POST['author']);
          $s->execute();          
      } catch (PDOException $e) {
          $error = 'Error adding joke to databases...'
                  .$e->getMessage();
          include $_SERVER['DOCUMENT_ROOT'].'/PHP_MySQL_Web/includes/error.html.php';
          exit();
      }      
      
      $jokeid = $pdo->lastInsertId();      
      
      // insert joke jokecategory , step 1 insert new items.
      // step 1 insert new items.
      try {
          $statement = 'insert into jokecategory(jokeid,categoryid) values(:jokeid,:categoryid)';
          $s = $pdo->prepare($statement);
      
          foreach ($_POST['categories'] as $categoryid)
          {
              $s->bindValue(':jokeid', $jokeid);
              $s->bindValue(':categoryid', $categoryid);
              $s->execute();
          }
      
      } catch (Exception $e) {
          $error = 'Error deleting joke to databases...'
                  .$e->getMessage();
          include $_SERVER['DOCUMENT_ROOT'].'/PHP_MySQL_Web/includes/error.html.php';
          exit();
      }           
      
    header('Location: .');
      exit();
  }

2.5 编辑笑话

// edit a joke
  if (isset($_POST['action']) && $_POST['action'] == 'Edit') {
      
      // build list of authors
      try {
          $statement = 'select id, name from author';
          $result = $pdo->query($statement);
      
          foreach($result as $row) {
              $authors[] = array('id'=>$row['id'], 'name'=>$row['name']);
          }
      
      } catch (PDOException $e) {
          $error = 'Query authors faild...'
                  .$e->getMessage();
          include $_SERVER['DOCUMENT_ROOT'].'/PHP_MySQL_Web/includes/error.html.php';
          exit();
      }
      
      // build list of categories
      try {
          $statement = 'select distinct categoryid from jokecategory where jokeid=:jokeid';
          $s = $pdo->prepare($statement);
          $s->bindValue(':jokeid', $_POST['id']);
          $s->execute();          
          $idCount = $s->rowCount();
          while ($row = $s->fetch()) {
              $categoryids_of_joke[] = $row['categoryid'];
          }
      } catch (PDOException $e) {
          $error = 'Query categories faild...'
                  .$e->getMessage();
          include $_SERVER['DOCUMENT_ROOT'].'/PHP_MySQL_Web/includes/error.html.php';
          exit();
      }
      
      try {
          $statement = 'select id,name from category;';
          $result = $pdo->query($statement);
              
          foreach($result as $row) {
              if ($idCount > 0)
              {
                  $categories[] = array('id'=>$row['id'], 'name'=>$row['name'], 'selected' => in_array($row['id'], $categoryids_of_joke));
              }
              else 
              {
                  $categories[] = array('id'=>$row['id'], 'name'=>$row['name'], 'selected' => false);
              }
              
          }
      } catch (Exception $e) {
          $error = 'Query category faild...'
                  .$e->getMessage();
          include $_SERVER['DOCUMENT_ROOT'].'/PHP_MySQL_Web/includes/error.html.php';
          exit();
      }
      
      try {
          $sql = 'SELECT joketext,jokedate,authorid FROM joke WHERE id=:id';
          $s = $pdo->prepare($sql);
          $s->bindValue(':id', $_POST['id']);
          $s->execute();
          
          $row = $s->fetch();
          
          $jokeText = $row['joketext'];
          
          $pageTitle = 'Edit joke';
          $action = 'editJoke';
          $id = $_POST['id'];      
          $authorid = $row['authorid'];
          $button = 'Update joke';
           
          include 'form.html.php';
          exit();
          
      } catch (Exception $e) {
          $error = 'Error Edit joke ...'
                  .$e->getMessage();
          include $_SERVER['DOCUMENT_ROOT'].'/PHP_MySQL_Web/includes/error.html.php';
          exit();
      }
  }
  
  if (isset($_GET['editJoke']) && $_POST['jokeText'] != '') {      
      if ($_POST['author'] == '' || !isset($_POST['categories'])){
          $error = 'Please select author and category...';
          include $_SERVER['DOCUMENT_ROOT'].'/PHP_MySQL_Web/includes/error.html.php';
          exit();
      }
      
      // update jokeText
      try {
          $statement = 'update joke set joketext = :joketext, authorid=:authorid where id=:id';
          $s = $pdo->prepare($statement);
          $s->bindValue(':joketext', $_POST['jokeText']);
          $s->bindValue(':authorid', $_POST['author']);
          $s->bindValue(':id', $_POST['id']);
          $s->execute();
      } catch (Exception $e) {
          $error = 'Error update joke to databases...'
                  .$e->getMessage();
          include $_SERVER['DOCUMENT_ROOT'].'/PHP_MySQL_Web/includes/error.html.php';
          exit();
      }
      // update joke jokecategory , step 1 delete old items, step 2 insert new items.
      // step 1 delete old items
      try {
          $statement = 'delete from jokecategory where jokeid=:jokeid';
          $s = $pdo->prepare($statement);
          $s->bindValue(':jokeid', $_POST['id']);
          $s->execute();
      } catch (Exception $e) {
          $error = 'Error deleting jokecategory to databases...'
                  .$e->getMessage();
          include $_SERVER['DOCUMENT_ROOT'].'/PHP_MySQL_Web/includes/error.html.php';
          exit();
      }
      // step 2 insert new items.
      try {
          $statement = 'insert into jokecategory(jokeid,categoryid) values(:jokeid,:categoryid)';
          $s = $pdo->prepare($statement);
          
          foreach ($_POST['categories'] as $categoryid)
          {
              $s->bindValue(':jokeid', $_POST['id']);
              $s->bindValue(':categoryid', $categoryid);
              $s->execute();
          } 
      } catch (Exception $e) {
          $error = 'Error deleting joke to databases...'
                  .$e->getMessage();
          include $_SERVER['DOCUMENT_ROOT'].'/PHP_MySQL_Web/includes/error.html.php';
          exit();
      }
  }

2.6 删除笑话

// delete a joke button
  if (isset($_POST['action']) && $_POST['action'] == 'Delete') {
      try {
          $statement = 'DELETE FROM joke WHERE id = :id';
          $s = $GLOBALS['pdo']->prepare($statement);
          $s->bindValue(':id', $_POST['id']);
          $s->execute();          
      } catch (PDOException $e) {
          $error = 'Error deleting joke to databases...'
                  .$e->getMessage();
          include $_SERVER['DOCUMENT_ROOT'].'/PHP_MySQL_Web/includes/error.html.php';
          exit();
      }
      
      header('Location: .');
      exit();
  }

2.7 显示笑话列表

// main page contents
  try {
      $statement = 'SELECT joke.id, joketext, name, email FROM joke LEFT JOIN author ON author.id = joke.authorid LIMIT 10;';
      $result = $GLOBALS['pdo']->query($statement);
      
      while ($row = $result->fetch()) {
          $jokes[] = array(
                  'id'=>$row['id'], 
                  'text'=>$row['joketext'], 
                  'name'=>$row['name'], 
                  'email'=>$row['email']
          );          
      }      
      
      $sql = "SELECT table_rows AS RCT FROM information_schema.tables WHERE table_name = 'joke';";
      
      $s = $pdo->prepare($sql);
      $s->execute();
      $row = $s->fetch();
      $rowCount = $row['RCT'];
      
  } catch (PDOException $e) {
      $error = 'exec query sql error...'
              .$e->getMessage();
      include $_SERVER['DOCUMENT_ROOT'].'/PHP_MySQL_Web/includes/error.html.php';
      exit();
  }
  
   include 'jokes.html.php';