第15週 Php與MySQL結合 update與delete

// 查詢資料表SELECT命令
1. 選擇全部欄位
   SELECT * FROM stmd;
2. 選擇某個欄位
   SELECT  stmd.depart_class,stmd.student_no,stmd.student_name,stmd.birth_date
         FROM stmd;
   SELECT  depart_class,student_no FROM stmd;
3. 選擇並排序
   SELECT * FROM stmd ORDER BY student_no;              -- 按學號由小至大
   SELECT * FROM stmd ORDER BY student_no ASC;     -- 按學號由小至大
   SELECT * FROM stmd ORDER BY student_no DESC;  -- 按學號由大至小
4. 加上where條件
   SELECT * FROM stmd WHERE student_no LIKE "%";           -- 選擇全部
   SELECT * FROM stmd WHERE student_no LIKE "98%";       -- 選擇98開頭者
   SELECT * FROM stmd WHERE student_no ="9414D001";   -- 選擇特定
5. 加上where條件與排序
   SELECT * FROM stmd WHERE student_no LIKE "98%"
        ORDER BY depart_class,student_no;
   
// 更新資料表UPDATE命令
UPDATE stmd
    SET depart_class="164D41",student_no="99999999",
            student_name="張三",birth_date="2010/01/01"
    WHERE STUDENT_NO="9414D001";
 

// 刪除資料表DELETE命令
DELETE FROM stmd;  -- 刪除所有資料 要很小心使用
DELETE FROM stmd WHERE student_no="9414D001";

<?php
 
  include "config.php";
  $command=$_POST["command"];
  $PHP_SELF=$_SERVER["PHP_SELF"];
  if ($command==null) {
      display_first_page($conn); 
      display_whole_data($conn); 
  }

  if ($command=="查詢") {
     display_first_page($conn);
     $student_no=$_POST["student_no"];
     if ($student_no==null) $student_no="%";
     else $student_no="%".$student_no."%";
     $sql="select * from stmd where student_no like '$student_no' order by student_no";
     $result=mysql_query($sql,$conn);
     echo "<center>
               <table border=1 bordercolor=red>
               <tr><td>學號</td><td>選擇</td></tr>";
     while ($myrow=mysql_fetch_array($result)) {
        $student_no=$myrow["student_no"];
        $save_student_no=$student_no;
        echo "<form method=post action='$PHP_SELF'>
                  <tr><td>學號
                  <input type=text name='student_no' value='$student_no'>
                  <input type=hidden name='save_student_no' value='$save_student_no'>
                  </td>
                  <td>
                  <input type=submit name='command' value='更新'>
                  <input type=submit name='command' value='刪除'>
                  </td></tr>
                  </form>";
      }
  }

  if ($command=="新增") {
      $student_no=$_POST["student_no"];
      $sql="insert into stmd values('$no')";
      $result=mysql_query($sql,$conn);
      display_first_page($conn);
      display_whole_data($conn);
  }
 
  if ($command=="更新") {
      $student_no=$_POST["student_no"];
      $save_student_no=$_POST["save_student_no"];
      $sql="update stmd set student_no='$student_no' where student_no='$save_student_no' ";
      mysql_query($sql,$conn);
      display_first_page($conn);
      display_whole_data($conn);
  }

  if ($command=="刪除") {
      $student_no=$_POST["student_no"];
      $save_student_no=$_POST["save_student_no"];
      $sql="delete from stmd where student_no='$save_student_no' ";
      mysql_query($sql,$conn);
      display_first_page($conn);
      display_whole_data($conn);
  }

  function display_first_page($conn) {
     echo "<center><table border=1 bordercolor='#ff0000'>
               <form method='post' action='$PHP_SELF'>
               <tr><td>學號</td>
               <td><input type=text name=student_no size=10></td>
               <td><input type=submit name=command value='查詢'>
               <input type=submit name=command value='新增'></td></tr>
               </table> </form><hr>
            " ;
  }
 
  function display_whole_data($conn) {
     $sql="select * from stmd  order by student_no";
     $result=mysql_query($sql,$conn);
     echo "<center>
               <table border=1 bordercolor=red>
               <tr><td>學號</td><td>選擇</td></tr>";
     while ($myrow=mysql_fetch_array($result)) {
        $student_no=$myrow["student_no"];
        $save_student_no=$student_no;
        echo "<form method=post action='$PHP_SELF'>
                  <tr><td>學號
                  <input type=text name='student_no' value='$student_no'>
                  <input type=hidden name='save_student_no' value='$save_student_no'>
                  </td>
                  <td>
                  <input type=submit name='command' value='更新'>
                  <input type=submit name='command' value='刪除'>
                  </td></tr>
                  </form>";
       }
  }

  
?>

// 作業: 仿此程式擴充上週的作業

 

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