Download presentation
Presentation is loading. Please wait.
1
PHP與SQL語法存取MySQL SQL
2
1. 新增 INSERT INTO
3
2. 修改 UPDATE
4
修改實例：網頁表單 複製AP03之insert.html，更名為input.html，加入以下藍紫色字體部分，上傳
<!DOCTYPE html> <html> <body> <form action="insert.php" method="post"> 帳號: <input type="text" name="user"><br> 密碼: <input type="password" name="pwd"><br> 最高分: <input type="number" name="best" min="0" max="100"><br> <input type="submit" value="新增資料"> </form> <hr> <form action="update.php" method="post"> <input type="submit" value="修改資料"> </body> </html>
5
修改實例：PHP 複製AP03之insert.php，更名為update.php，根據以下修改，上傳 <?php
header("Content-Type:text/html; charset=utf-8"); $con = mysqli_connect("mysql8.db4free.net","tcyang","123456","pucsim2017", "3307"); // Check connection if (mysqli_connect_errno()) { echo "無法連線到MySQL: " . mysqli_connect_error(); } $user = $_POST['user']; $pwd = $_POST['pwd']; $best = $_POST['best']; // Perform queries $sql="UPDATE Member SET UserBest = $best WHERE UserName = '$user' AND UserPass = PASSWORD('$pwd')"; if (mysqli_query($con,$sql)){ echo "您的資料修改成功"; else { echo "您的資料修改失敗"; mysqli_close($con); ?>
6
執行結果 輸入資料庫使用者及密碼
7
3. 刪除 DELETE
8
刪除實例：網頁表單 修改input.html，加入以下部分，上傳 <hr>
<form action="delete.php" method="post"> 帳號: <input type="text" name="user"><br> 密碼: <input type="password" name="pwd"><br> <input type="submit" value="刪除資料"> </form>
9
刪除實例：PHP <?php header("Content-Type:text/html; charset=utf-8");
複製update.php，更名為delete.php，根據以下修改，上傳 <?php header("Content-Type:text/html; charset=utf-8"); $con = mysqli_connect("mysql8.db4free.net","tcyang","123456","pucsim2017", "3307"); // Check connection if (mysqli_connect_errno()) { echo "無法連線到MySQL: " . mysqli_connect_error(); } $user = $_POST['user']; $pwd = $_POST['pwd']; $best = $_POST['best']; // Perform queries $sql="DELETE FROM Member WHERE UserName = '$user' AND UserPass = PASSWORD('$pwd')"; if (mysqli_query($con,$sql)){ echo "您的資料刪除成功"; else { echo "您的資料刪除失敗"; mysqli_close($con); ?>
10
執行結果
11
4. 查詢 SELECT
12
查詢 SELECT WHERE 條件
13
查詢實例：網頁表單 修改input.html，加入以下部分，上傳 <hr>
<form action="select1.php" method="post"> 帳號: <input type="text" name="user"><br> 密碼: <input type="password" name="pwd"><br> <input type="submit" value="查詢該使用者最高分"> </form>
14
查詢實例：PHP 複製delete.php，更名為select1.php，根據以下修改，上傳 <?php header("Content-Type:text/html; charset=utf-8"); $con = mysqli_connect("mysql8.db4free.net","tcyang","123456","pucsim2017", "3307"); // Check connection if (mysqli_connect_errno()) { echo "無法連線到MySQL: " . mysqli_connect_error(); } $user = $_POST['user']; $pwd = $_POST['pwd']; $best = $_POST['best']; // Perform queries $sql="SELECT UserBest FROM Member where UserName = '$user' AND UserPass = PASSWORD('$pwd')"; $row = mysqli_fetch_array(mysqli_query($con,$sql)); $data = $row[0]; if($data){ echo "您的最高分為：" . $data; else{ echo "您的帳號或密碼有誤"; mysqli_close($con); ?>
15
5. 查詢及顯示多筆資料實例 假設要以JSON格式呈現
16
JSON格式 物件(object)用大括號 { } 陣列(array)用中括號 [ ]
![JSON格式 物件(object)用大括號 { } 陣列(array)用中括號 [ ]](http://slidesplayer.com/slide/15527630/93/images/16/JSON%E6%A0%BC%E5%BC%8F+%E7%89%A9%E4%BB%B6%28object%29%E7%94%A8%E5%A4%A7%E6%8B%AC%E8%99%9F+%7B+%7D+%E9%99%A3%E5%88%97%28array%29%E7%94%A8%E4%B8%AD%E6%8B%AC%E8%99%9F+%5B+%5D.jpg "JSON格式 物件(object)用大括號 { } 陣列(array)用中括號 [ ]")
17
JSON Example 1 [ {"學校":"靜宜大學", "地址":"台中市沙鹿區台灣大道七段200號"},
{"學校":"東海大學", "地址":"台中市西屯區台灣大道四段1727號"}, {"學校":"逢甲大學", "地址":"台中市西屯區文華路100號"} ]
18
JSON Example 2 { "employees":[ {"firstName":"John", "lastName":"Doe"},
{"firstName":"Anna", "lastName":"Smith"}, {"firstName":"Peter", "lastName":"Jones"} ] }
19
使用Online JSON Viewer觀看
Text頁籤，貼上文字，或是選擇Load JSON data按鈕，輸入JSON檔案的網址 政府資料開放平台 例如：「國家森林遊樂區」 JSON資料：
20
查詢多筆資料實例：網頁表單 修改input.html，加入以下部分，上傳 <hr>
<form action="select2.php" method="post"> <input type="submit" value="查詢全部資料"> </form>
21
查詢多筆資料實例：PHP 複製select1.php，更名為select2.php，根據以下修改，上傳 <?php
header("Content-Type:text/html; charset=utf-8"); $con = mysqli_connect("mysql8.db4free.net","tcyang","123456","pucsim2017", "3307"); // Check connection if (mysqli_connect_errno()) { echo "無法連線到MySQL: " . mysqli_connect_error(); } // Perform queries mysqli_query($con,"SET CHARACTER SET UTF8"); $sql="SELECT * FROM Member"; $result = mysqli_query($con,$sql); $json = array(); if(mysqli_num_rows($result)){ while($row=mysqli_fetch_assoc($result)){ $json[]=$row; echo urldecode(json_encode($json)); mysqli_close($con); ?>
22
執行結果(以JSON格式顯示)
Similar presentations
