Presentation is loading. Please wait.

Presentation is loading. Please wait.

PHP與SQL語法存取MySQL SQL http://www.w3schools.com/sql/default.asp.

Similar presentations


Presentation on theme: "PHP與SQL語法存取MySQL SQL http://www.w3schools.com/sql/default.asp."— Presentation transcript:

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("sql6.freemysqlhosting.net","sql ","sut4eKeMIl","sql "); // 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 = CONVERT(_utf8 '$user' USING utf8) 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 複製update.php,更名為delete.php,根據以下修改,上傳 <?php header("Content-Type:text/html; charset=utf-8"); $con = mysqli_connect("sql6.freemysqlhosting.net","sql ","sut4eKeMIl","sql "); // 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 = CONVERT(_utf8 '$user' USING utf8) 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("sql6.freemysqlhosting.net","sql ","sut4eKeMIl","sql "); // Check connection if (mysqli_connect_errno()) { echo "無法連線到MySQL: " . mysqli_connect_error(); } $user = $_POST['user']; $pwd = $_POST['pwd']; // Perform queries $sql="SELECT UserBest FROM member where UserName = CONVERT(_utf8 '$user' USING utf8) 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 查詢多筆資料實例:網頁表單 修改input.html,加入以下部分,上傳 <hr>
<form action="select2.php" method="post"> <input type=“submit” value=“查詢全部資料"> </form>

17 查詢多筆資料實例:PHP mysqli_query($con,"SET CHARACTER SET UTF8");
複製select1.php,更名為select2.php,根據以下修改,上傳 <?php header("Content-Type:text/html; charset=utf-8"); $con = mysqli_connect("sql6.freemysqlhosting.net","sql ","sut4eKeMIl","sql "); // 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); ?>

18 執行結果(以JSON格式顯示)


Download ppt "PHP與SQL語法存取MySQL SQL http://www.w3schools.com/sql/default.asp."

Similar presentations


Ads by Google