溫瑞烘老師的教學歷程檔案(Teaching ePortfolio) - 103-2-資管二甲乙資料庫管理系統 - 第6週-MySQL-PHP-UPDATE
 

資訊管理系
副教授
温瑞烘

 歷程檔案 Portfolio

關於我 About Me

第6週-MySQL-PHP-UPDATE

UPDATE 更新欄位資料，常要搭配 WHERE
1. UPDATE stmd SET student_no="10014D001";
  會將所有學號都改成 10014D001
2. UPDATE stmd SET student_name="張三豐" WHERE student_no="10014D001";
  會將學號 10014D001 的姓名欄更改
3. UPDATE stmd set depart_class=
   concat(SUBSTR(depart_class,1,4),"2",SUBSTR(depart_class,6,1))
   WHERE depart_class LIKE "____1_";
  會將年級升上一級

<?php
  include "config.php";
  if (isset($_POST["command"])) $command=$_POST["command"];
  else $command="";
 
  if ($command=="" || $command=="返回") {
    display_first_page($conn);
  }
  elseif ($command=="查詢") {
    display_search_page($conn);
  }
  elseif ($command=="新增") {
    display_insert_page($conn);
    display_first_page($conn);
  }
  elseif ($command=="更新") {
    display_modify_page($conn);
  }
  mysqli_close($conn);
 
  function display_first_page($conn) { // 第一個頁面
    echo "<html><head><title>MySql與PHP結合-插入範例</title>
          <link rel='stylesheet' type='text/css' href='style.css'>
          <script>
            function check() {
              var depart_class=document.getElementById('depart_class').value;
              if (depart_class=='') {
                alert('請輸入班級代螞'); return false;
              }
              var depart_name=document.getElementById('depart_name').value;
              if (depart_name=='') {
                alert('請輸入班級名稱'); return false;
              }
              return true;
            }
          </script></head><body><center>
          <table> <form method='post' action=''>
          <tr class='alt0'><td colspan=4>MySql與PHP結合-新增-範例</td></tr>
          <tr><td class='alt1'>班級代碼</td>
          <td><input type='text' id='depart_class' name='depart_class' /></td></tr>
          <tr><td class='alt1'>班級名稱</td>
          <td><input type='text' id='depart_name' name='depart_name' /></td></tr>
          </table>
          <input class='cmd' type='submit' name='command' value='查詢' >
          <input class='cmd' type='submit' name='command' value='新增'
           onclick='return check()' >
          <input class='cmd' type='reset' name='command' value='清除' >
          </form></center></body></html>";
  }
  function display_insert_page($conn) {
    //將每個攔位資料取出
    $depart_class=trim($_POST["depart_class"]);
    $depart_name=trim($_POST["depart_name"]);
    $sql="insert into depart values('$depart_class','$depart_name')";
    $retcode=mysqli_query($conn,$sql);
    if ($retcode===true) {
      echo "<script> alert('插入一筆記錄成功'); </script>";
    } else {
      echo "<script> alert('插入一筆記錄失敗'); </script>"; 
    }        
  }
 
  function display_search_page($conn) {
    $today=date("Y-m-d");
    $depart_class=trim($_POST["depart_class"]); // 以學號為鍵值查詢
    if ($depart_class=="") $depart_class="%";   // 查詢全部
    else $depart_class="%".$depart_class."%";
    $depart_name=trim($_POST["depart_name"]); // 以學號為鍵值查詢
    if ($depart_name=="") $depart_name="%";   // 查詢全部
    else $depart_name="%".$depart_name."%";
    $sql="select * from depart where depart_class like '$depart_class' and ";
    $sql.="depart_name like '$depart_name' order by depart_class,depart_name";
    $result=mysqli_query($conn,$sql);        // SQL命令執行
    echo "<html><head><title>MySql與PHP結合-查詢範例</title>
          <link rel='stylesheet' type='text/css' href='style.css'>
          </head><body><center><table>
          <form method='post' action=''>
          <tr class='alt3'><td colspan=4>[depart.php] MySql與PHP結合-查詢範例 [$today]</td></tr>
          <tr class='alt1'><td>序號</td><td>班級代碼</td><td>班級名稱</td>
          <td>選擇</td></tr>";
    $cnt=0;
    while ($myrow=mysqli_fetch_array($result)) {  // 取出一列
      $depart_class=$myrow["depart_class"];      // 取出欄位
      $depart_name=$myrow["depart_name"];          // 取出欄位
      $cnt++;
      $bgc=($cnt % 2) + 2;
      echo "<tr class='alt$bgc'><td>$cnt</td><td>$depart_class</td>
            <td>$depart_name</td><td>
            <input type='radio' id='depart_class' name='depart_class'
              value='$depart_class'></td></tr>";
    }
    echo "</table><input class='cmd' type='submit' name='command' value='更新'>
          <input class='cmd' type='submit' name='command' value='返回'>
          </center></body></html>";
  }
  function display_modify_page($conn) { // 更新頁面
    $depart_class=$_POST["depart_class"];
    if ($depart_class=="") return;
    $sql="select * from depart where depart_class='$depart_class' ";
    $result=mysqli_query($conn,$sql);
    $myrow=mysqli_fetch_array($result);
    $depart_name=$myrow["depart_name"];
    echo "<html><head><title>MySql與PHP結合-插入範例</title>
          <link rel='stylesheet' type='text/css' href='style.css'>
          <script>
            function check() {
              var depart_name=document.getElementById('depart_name').value;
              if (depart_name=='') {
                alert('請輸入班級名稱'); return false;
              }
              return true;
            }
          </script></head><body><center>
          <table> <form method='post' action=''>
          <tr class='alt0'><td colspan=4>MySql與PHP結合-新增-範例</td></tr>
          <tr><td class='alt1'>班級代碼</td>
          <td><input type='text' id='depart_class' name='depart_class'
                value='$depart_class' readonly/></td></tr>
          <tr><td class='alt1'>班級名稱</td>
          <td><input type='text' id='depart_name' name='depart_name'
                value='$depart_name' /></td></tr>
          </table>
          <input class='cmd' type='submit' name='command' value='確認'
            onclick=\" return confirm('確定要更新嗎?') \" >
          <input class='cmd' type='submit' name='command' value='返回' >
          </form></center></body></html>";
  }
?>


請完成確認的程式碼
再撰寫stmd的update功能


 
全部共 0則留言
登入帳號密碼代表遵守學術網路規範
 

 文章分類 Labels

 

 最新文章 Top10

中華科技大學數位化學習歷程 - 意見反應