2020 台中科技大學 資管四甲 MySQL,PHP淺教學

Author: 陳繹仁

• 第一節範例連結

  • https://rensv.synology.me/nutc_teachweb2020/class1/

• 第二節範例連結

  • https://rensv.synology.me/nutc_teachweb2020/class2/

  • 範例登入帳號：1110330140 密碼：abc123

---

目錄

• 第一節

  1-1 : SQL Server Connection
  1-2 : SQL 通用快速呼叫方式
  1-3 : SQL Create - 新增
  1-4 : SQL Read - 讀取
  1-5 : SQL Update - 更新
  1-6 : SQL Delete - 刪除
  1-7 : 表單送出
  1-8 : 取得連結中的值
  

• 第二節

  繹仁 : 我好懶 之後再做

- SQL Server Connection 測試server是否可連線

<?php 
	
	$config_set['db_connection']['dsn'] = 'mysql:dbname=資料庫名稱;host=localhost;chartset=utf8';
	$config_set['db_connection']['user_name'] = '輸入你的帳號';
	$config_set['db_connection']['password'] ='輸入你的密碼';
	
	$dbh = new PDO(
		$config_set['db_connection']['dsn'],
		$config_set['db_connection']['user_name'],
		$config_set['db_connection']['password'],
		array(
			PDO::ATTR_EMULATE_PREPARES => false,
			PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
			)
	);

?>

---

- Function 快速執行

    function filterTable($query)
    {
        $dbType   = 'MySQL';
        $host     = 'localhost';
        $dbName   = '資料庫名稱';
        $userName = '帳號';
        $pwd      = '密碼';
        
        $dbh = mysqli_connect($host, $userName, $pwd, $dbName) or die("Error " . mysqli_error($dbh));
        //$dbh = mysqli_connect($host, $userName, $pwd, $dbName);
        mysqli_query($dbh, "SET NAMES 'utf8'");
        $filter_Result = mysqli_query($dbh, $query);
        return $filter_Result;
    }

---

- SQL Create 新增

//將form post過來的值新增至todo資料表
    $sql="INSERT INTO `todo`(`content`) VALUES ('".$_POST['content']."')";
    filterTable($sql);

---

- SQL Read 讀取

• 多行讀取

    $sql = "SELECT * FROM `todo`" ;
                            
    $result = filterTable($sql);

    while($row = mysqli_fetch_array($result)):
        //執行你要做的事情
        echo $row['欄位名']
    endwhile;

• 單列讀取

    $query = "SELECT * FROM `todo`" ;
                            
    $result = filterTable($query);

    $row = mysqli_fetch_array($result);

    echo $row['欄位名'];

---

- SQL Update 更新

//將POST過來的content覆蓋於id為editTask的值
    $sql="UPDATE `todo` SET `content`='".$_POST['content']."' WHERE `id` = '".$_POST['editTask']."'";
    filterTable($sql);

---

- SQL Delete 刪除

//將連結上的deleteTask擷取 並刪除資料庫中與此ID相符的資料列
    $sql="DELETE FROM `todo` WHERE `id` =  '".$_GET['deleteTask']."'";
    filterTable($sql);

---

- Form Post 行為

    <form name="form" method="post" action="php/userlogin.php">
        <input type="text" name="username">
        <input type="password" name="password">
        <button type="submit">送出</button>
    </form>

    $schoolid= $_POST['username']; // 對應 input 的值 name="username"
    $passtr= $_POST['password']; // 對應 input 的值 name="password"

    $query = "SELECT * FROM `classtable` Where `id` ='" . $schoolid . "' AND `password` = '".$passstr."'";
    $result = filterTable($query);
    $row = mysqli_fetch_array($result);  
    // $row 輸出的會是找到的資料 若帳號或密碼錯誤則為 null

---

- GET 行為

    <a href="link1.php?name=helloworld"> 連結1 </a>
    <a href="link2.php?id=1110331040"> 連結2 </a>

• link1.php

    $getparams = $_GET['name'];
    //$getparams = "helloworld"

• link2.php

    $getparams = $_GET['id'];
    //$getparams = "1110331040"