Jill 筆記 - 線上書籍

:::

PHP從入門到放棄實戰班
============

### 一、 編輯（更新）資料

1. 編輯（更新）資料的SQL語法： 
  ```markup
  update 資料表 set 欄位1=值1,欄位2=值2,... [where 篩選條件] [limit 筆數]
  ```
2. 記得！一定要有where，否則的話，會所有欄位全部被更新！
3. 編輯功能的步驟 
  - 按下編輯連結時，將欲修改的資料流水號傳給程式（請用get方式傳遞參數）。 
      ```markup
      <a href="{$action}?op=post_form&sn={$data.sn}" class="btn btn-warning" title="編輯""><i class="fas fa-pencil-alt"></i> 編輯</a>
      ```
  - 程式接收後，判斷若有接收到流水號，則為編輯模式，否則為新增模式。 
      ```php
      // 表單
      function post_form()
      {
          global $content, $db, $smarty;
          /*****省略***/
      
          // 編輯
          if (isset($_GET['sn'])) {
      
              $next_op = 'update';
          } else {
              // 加入預設值
              $content = [
                  'title'      => '',
                  'directions' => '',
                  'end'        => date("Y-m-d", strtotime("+10 day")),
                  'priority'   => '中',
                  'assign'     => [],
                  'done'       => 0,
              ];
              $next_op = 'add';
          }
      
          $smarty->assign('next_op', $next_op);
      }
      ```
  - 接著利用接收的流水號從資料庫取得該筆資料。 
      ```php
      //以流水號取得某筆資料
      function find_one($sn = "")
      {
          global $db;
          if (empty($sn)) {
              return;
          }
      
          $sql = "select * from list where `sn` = '{$sn}'";
          if (!$result = $db->query($sql)) {
              die(error($db->error));
          }
          $data = $result->fetch_assoc();
      
          return $data;
      }
      ```
  - 將取得之資料塞回去原來的填寫表單。 
      - 複選框須使用PHP字串切割函數：`explode('分割符號',字串變數);` 
            ```php
            //以流水號取得某筆資料
            function find_one($sn = "")
            {
                global $db;
                /**省略**/
                $data = $result->fetch_assoc();
                // 複選框$data['assign']
                $data['assign_arr'] = explode(';', $data['assign']);
                return $data;
            }
            ```
      - 同時修改樣板檔 `post_form.tpl` 
            ```markup
            <div class="form-group">
                <label for="assign">指派對象</label>
                
                <div class="form-check form-check-inline">
                  <label class="form-check-label">
                    <input class="form-check-input" type="checkbox" name="assign[]" id="assign_0" value="爸爸" {if "爸爸"|in_array:$content.assign_arr}checked="checked"{/if}>爸爸
                  </label>
                  <label class="form-check-label">
                    <input class="form-check-input" type="checkbox" name="assign[]" id="assign_1" value="媽媽" {if "媽媽"|in_array:$content.assign_arr}checked="checked"{/if}>媽媽
                  </label>
                  <label class="form-check-label">
                    <input class="form-check-input" type="checkbox" name="assign[]" id="assign_2" value="哥哥" {if "哥哥"|in_array:$content.assign_arr}checked="checked"{/if}>哥哥
                  </label>
                  <label class="form-check-label">
                    <input class="form-check-input" type="checkbox" name="assign[]" id="assign_3" value="妹妹" {if "妹妹"|in_array:$content.assign_arr}checked="checked"{/if}>妹妹
                  </label>
                  <label class="form-check-label">
                    <input class="form-check-input" type="checkbox" name="assign[]" id="assign_4" value="我" {if "我"|in_array:$content.assign_arr}checked="checked"{/if}>我
                  </label>
                </div>
            </div>
            
            ```
  - 用隱藏欄位來存放模式狀態，以利送出時程式判斷該新增或更新，name通常為next\_op，記得加入 `sn` 編號。 
      ```markup
      <input type="hidden" name="next_op" value="{$next_op}">
      <input type="hidden" name="sn" value="{$content.sn}">
      ```
  - 執行更新動作 
      - 表單增加函數 `update()` 
            ```php
            function post_form()
            {
                global $content, $db, $smarty;
                if (isset($_POST['send'])) {
                    if (isset($_POST['next_op'])) {
                        /******省略******/
            
                        if ($_POST['next_op'] == "update") {
                            $sn          = update();
                            $_message    = empty($_sn) ? "更新失敗" : "更新成功!";
                            $refresh_url = 'index.php?sn={$sn}';
                        }
                    }
                    die(error($_message, $refresh_url));
                }
            
                /******省略******/
            }
            ```
      - 讓 error() 用途更廣 
            ```php
            //function.php更改error()
            //跳轉頁
            function redirect_page($message, $refresh_url = '', $page_title = '錯誤提示頁')
            {
                global $smarty;
                $smarty->assign('page_title', $page_title);
                $smarty->assign('message', $message);
                $smarty->assign('refresh_url', $refresh_url);
                $smarty->display('templates/error.tpl');
                exit();
            }
            ```
            
             
            ```php
            // 表單
            function post_form()
            {
                global $content, $db, $smarty;
                if (isset($_POST['send'])) {
                    if (isset($_POST['next_op'])) {
                        if ($_POST['next_op'] == "add") {
                            $sn = add();
                            if (empty($sn)) {
                                $_message   = "新增失敗";
                                $page_title = '錯誤提示頁';
                                $refresh_url = 'index.php';
            
                            } else {
                                $_message   = "新增成功!";
                                $page_title = '成功提示頁';
                                $refresh_url = 'index.php?sn={$sn}';
            
                            }
                        }
            
                        if ($_POST['next_op'] == "update") {
                            $sn = update();
                            if (empty($sn)) {
                                $_message   = "更新失敗";
                                $page_title = '錯誤提示頁';
                                $refresh_url = 'index.php';
            
                            } else {
                                $_message   = "更新成功!";
                                $page_title = '成功提示頁';
                                $refresh_url = 'index.php?sn={$sn}';
            
                            }
                            
                        }
                    }
                    die(redirect_page($_message, $refresh_url, $page_title));
            
                }
                /**************省略*************/
            }
            ```
      - 執行 update() 
            ```php
            //更新清單
            function update()
            {
                global $db;
                check_error();
                //過濾變數
                $sn          = (int) $_POST['sn'];
                $title       = $db->real_escape_string($_POST['title']);
                $directions  = $db->real_escape_string($_POST['directions']);
                $end         = $db->real_escape_string($_POST['end']);
                $priority    = $db->real_escape_string($_POST['priority']);
                $assign      = $db->real_escape_string(implode(';', $_POST['assign']));
                $done        = (int) $_POST['done'];
                $update_time = date('Y-m-d H:i:s');
            
                // 連線資料庫
                $sql = "UPDATE `list` SET
                `title`='{$title}',
                `directions`='{$directions}',
                `end`='{$end}',
                `priority`='{$priority}',
                `assign`='{$assign}',
                `done`='{$done}',
                `update_time`='{$update_time}'
                WHERE `sn`= '$sn'";
                // die($sql);
                $db->query($sql) or die(redirect_page($db->error));
            
                return $sn;
            }
            ```
            
             
            ```php
            // 表單
            function post_form()
            {
                global $content, $db, $smarty;
                if (isset($_POST['send'])) {
                    if (isset($_POST['next_op'])) {
                        if ($_POST['next_op'] == "add") {
                            $sn = add();
                            if (empty($sn)) {
                                $_message   = "新增失敗";
                                $page_title = '錯誤提示頁';
                            } else {
                                $_message   = "新增成功!";
                                $page_title = '成功提示頁';
                            }
            
                            $refresh_url = 'index.php';
                        }
            
                        if ($_POST['next_op'] == "update") {
                            $sn = update();
                            if (empty($sn)) {
                                $_message   = "更新失敗";
                                $page_title = '錯誤提示頁';
                            } else {
                                $_message   = "更新成功!";
                                $page_title = '成功提示頁';
                            }
                            $refresh_url = 'index.php?sn=' . $sn;
                        }
                    }
                    die(redirect_page($_message, $refresh_url, $page_title));
                }
            
                //以下省略
            }
            ```

MarkDown