利用PHP將資料存入MySQL PHP 5 Tutorial http://www.w3schools.com/php/ 範例： <!DOCTYPE html> <html> <body> <?php echo "My first PHP script!"; ?> </body> </html> 存成first.php (utf-8)，上傳到學校網路空間 ftp://www1.pu.edu.tw (於html建立php資料夾)，再到瀏覽器測試 http://www1.pu.edu.tw/~帳號/php/first.php 若改成 echo "楊子青的第一個PHP動態網頁"; 執行後變亂碼
1. PHP正確顯示中文碼：作法1 加入head網頁標籤，宣告utf-8編碼方式 <!DOCTYPE html> <meta charset="utf-8"> </head> <body> <?php echo "楊子青的第一個PHP動態網頁"; ?> </body> </html>
PHP正確顯示中文碼：作法2 全部去掉html網頁標籤，直接在php宣告 <?php header("Content-Type:text/html; charset=utf-8"); echo "楊子青的第一個PHP動態網頁"; ?>
2. PHP 5 MySQLi Functions http://www.w3schools.com/php/php_ref_mysqli.asp
PHP mysqli_connect() Function http://www.w3schools.com/php/func_mysqli_connect.asp mysql主機, 資料庫使用者, 資料庫密碼, 資料庫名稱
先確認自己的MySQL資料庫 Free MySQL Hosting https://www.freemysqlhosting.net/ 按login，輸入帳號(email)及密碼 查詢資料庫相關資訊：
重要！！！ Free MySQL Hosting大約每週都會詢問是否要持續使用，會再mail認證 請儘量一週內就要登入 按上方的超鏈節進行展延 沒做的話，免費測試帳號將會到期無法再用
利用PHP連線到自己的SQL Server header("Content-Type:text/html; charset=utf-8"); $con = mysqli_connect("sql6.freemysqlhosting.net","sql6137207","sut4eKeMIl","sql6137207"); // Check connection if (mysqli_connect_errno()) { echo "無法連線到MySQL: " . mysqli_connect_error(); } ?> 存成insert.php上傳，如果資料庫資訊有誤(例如密碼錯誤)： mysql主機, 資料庫使用者, 資料庫密碼, 資料庫名稱
PHP mysqli_query() Function http://www.w3schools.com/php/func_mysqli_query.asp
3. 新增一筆記錄 存成insert.php上傳，用瀏覽器執行 <?php header("Content-Type:text/html; charset=utf-8"); $con = mysqli_connect("sql6.freemysqlhosting.net","sql6137207","sut4eKeMIl","sql6137207"); // Check connection if (mysqli_connect_errno()) { echo "無法連線到MySQL: " . mysqli_connect_error(); } // Perform queries mysqli_query($con,"INSERT INTO member (UserName,UserPass,UserBest) VALUES ('test','xyz',33)"); mysqli_close($con); ?> 存成insert.php上傳，用瀏覽器執行
查詢關聯表內容
修改1：提供新增是否成功訊息 <?php header("Content-Type:text/html; charset=utf-8"); $con = mysqli_connect("sql6.freemysqlhosting.net","sql6137207","sut4eKeMIl","sql6137207"); // Check connection if (mysqli_connect_errno()) { echo "無法連線到MySQL: " . mysqli_connect_error(); } // Perform queries $sql="INSERT INTO member (UserName,UserPass,UserBest) VALUES ('test','xyz',33)"; if (mysqli_query($con,$sql)){ echo "您的資料新增成功"; else { echo "您的資料新增失敗"; mysqli_close($con); ?>
修改2：密碼欄位加密 <?php header("Content-Type:text/html; charset=utf-8"); $con = mysqli_connect("sql6.freemysqlhosting.net","sql6137207","sut4eKeMIl","sql6137207"); // Check connection if (mysqli_connect_errno()) { echo "無法連線到MySQL: " . mysqli_connect_error(); } // Perform queries $sql="INSERT INTO member (UserName,UserPass,UserBest) VALUES ('test',PASSWORD('xyz'),33)"; if (mysqli_query($con,$sql)){ echo "您的資料新增成功"; else { echo "您的資料新增失敗"; mysqli_close($con); ?>
問題：中文欄位值，存入後變亂碼 <?php header("Content-Type:text/html; charset=utf-8"); $con = mysqli_connect("sql6.freemysqlhosting.net","sql6137207","sut4eKeMIl","sql6137207"); // Check connection if (mysqli_connect_errno()) { echo "無法連線到MySQL: " . mysqli_connect_error(); } // Perform queries $sql="INSERT INTO member (UserName,UserPass,UserBest) VALUES ('靜宜資管',PASSWORD('xyz'),33)"; if (mysqli_query($con,$sql)){ echo "您的資料新增成功"; else { echo "您的資料新增失敗"; mysqli_close($con); ?>
修改3：中文欄位值轉換 <?php header("Content-Type:text/html; charset=utf-8"); $con = mysqli_connect("sql6.freemysqlhosting.net","sql6137207","sut4eKeMIl","sql6137207"); // Check connection if (mysqli_connect_errno()) { echo "無法連線到MySQL: " . mysqli_connect_error(); } // Perform queries $sql="INSERT INTO member (UserName,UserPass,UserBest) VALUES (CONVERT(_utf8 '靜宜資管' USING utf8),PASSWORD('xyz'),33)"; if (mysqli_query($con,$sql)){ echo "您的資料新增成功"; else { echo "您的資料新增失敗"; mysqli_close($con); ?>
4. 讓PHP讀取使用者輸入之變數 參數傳遞方法1： GET (網址傳遞參數，較不安全) <?php header("Content-Type:text/html; charset=utf-8"); $user = $_GET['user']; $pwd = $_GET['pwd']; $best = $_GET['best']; echo $user."; ".$pwd."; ".$best; ?> 存成get.php (utf-8)上傳，再到瀏覽器測試 http://www1.pu.edu.tw/~帳號/php/get.php?user=楊子青&pwd=123456&best=50 ?後接欲傳入的參數 並以&相連接
讓PHP讀取使用者輸入之變數 參數傳遞方法2： POST (非透過網址參數，較安全) <?php header("Content-Type:text/html; charset=utf-8"); $user = $_POST['user']; $pwd = $_POST['pwd']; $best = $_POST['best']; echo $user."; ".$pwd."; ".$best; ?> 存成post.php (utf-8)上傳，再到瀏覽器測試 http://www1.pu.edu.tw/~帳號/php/post.php?user=楊子青&pwd=123456&best=50
GET與POST比較 http://www.w3schools.com/tags/ref_httpmethods.asp 於網址列顯示傳遞參數，安全性堪慮 參數傳遞較安全
如何將參數透過POST傳遞？ 撰寫網頁，利用表單方式傳遞參數 <!DOCTYPE html> <html> <body> <form action="post.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> </body> </html> 存成form.html (utf-8)上傳，再到瀏覽器測試 http://www1.pu.edu.tw/~帳號/php/form.html，輸入測試資料
5.表單及POST傳遞參數，新增資料 利用表單方式傳遞參數，呼叫insert.php新增資料 <!DOCTYPE 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> </body> </html> 存成insert.html (utf-8)上傳
讓insert.php傳入參數 <?php header("Content-Type:text/html; charset=utf-8"); $con = mysqli_connect("sql6.freemysqlhosting.net","sql6137207","sut4eKeMIl","sql6137207"); // Check connection if (mysqli_connect_errno()) { echo "無法連線到MySQL: " . mysqli_connect_error(); } $user = $_POST['user']; $pwd = $_POST['pwd']; $best = $_POST['best']; // Perform queries $sql="INSERT INTO member (UserName,UserPass,UserBest) VALUES (CONVERT(_utf8 '$user' USING utf8),PASSWORD('$pwd'),$best)"; if (mysqli_query($con,$sql)){ echo "您的資料新增成功"; else { echo "您的資料新增失敗"; mysqli_close($con); ?>