PDO和MySQL
MySQL由於效能高、成本低、可靠性好，已經成為最流行的開源SQL資料庫，因此被廣泛地應用在Internet上的中小型網站中。隨著MySQL的不斷成熟，它也逐漸用於更多大規模網站和應用，比如維基百科、Google和Facebook等網站。
PDO(PHP Data Objects)是PHP語言的一個延伸函式庫，用來存取資料庫。雖然PHP語言另外有mysql函數可以連接資料庫。但是為了避免SQL injection攻擊，儘量不要用mysql函數來連接資料庫。改用PDO連接資料庫，對安全性比較好。
參考: Why You Should Use PDO Instead of MySQLi https://www.conetix.com.au/blog/why-you-should-use-pdo-instead-mysqli
mysql函數和PDO比較
<?php
// PDO
$pdo = new PDO("mysql:host=localhost;dbname=database", 'username', 'password');
// mysqli, procedural way
$mysqli = mysqli_connect('localhost','username','password','database');
// mysqli, object oriented way
$mysqli = new mysqli('localhost','username','password','database');
?>
-- 資料庫： `web2017`
--
-- --------------------------------------------------------
--
-- 資料表結構 `tour_user`
--
CREATE TABLE IF NOT EXISTS `tour_user` (
`SID` varchar(12) NOT NULL,
`SName` varchar(20) NOT NULL,
`SCode` varchar(12) NOT NULL,
`SDepart` varchar(20) NOT NULL,
`LastLogin` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`SID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- --------------------------------------------------------
--
-- 資料表結構 `tour_vote`
--
CREATE TABLE IF NOT EXISTS `tour_vote` (
`SID` varchar(12) NOT NULL,
`SName` varchar(20) NOT NULL,
`SCode` varchar(20) NOT NULL,
`SLoc` varchar(20) NOT NULL,
`SComment` varchar(200) NOT NULL,
`SDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
PDO連線資料庫
localhost, 120.108.112.179
<?php
$dbconfig = array (
'database' => 'web2017',
'username' => 'student2017',
'password' => 'mALL7Fs2E7hcuXsD',
'host' => 'localhost',
'port' => '',
'driver' => 'mysql',
);
$dsn = $dbconfig['driver'].":host=".$dbconfig['host'].";dbname=".$dbconfig['database'];
$pdo = new PDO($dsn, $dbconfig['username'],$dbconfig['password']);
$pdo->query("set names utf8");
$sql = "SELECT * FROM admin WHERE username = :userName AND userpwd = :userPWD";
$pdoStatement->bindValue(':userName', $vdate, PDO::PARAM_STR);
$pdoStatement->bindValue(':userPWD', $pid, PDO::PARAM_STR);
$pdoStatement = $pdo->prepare($sql);
$ret = $pdoStatement->execute();
?>
SQL for build the database
-- 資料庫： `web2017`
-- --------------------------------------------------------
--
-- 資料表結構 `tour_user`
--
CREATE TABLE IF NOT EXISTS `tour_user` (
`SID` varchar(12) NOT NULL,
`SName` varchar(20) NOT NULL,
`SCode` varchar(12) NOT NULL,
`SDepart` varchar(20) NOT NULL,
`LastLogin` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`SID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- --------------------------------------------------------
--
-- 資料表結構 `tour_vote`
--
CREATE TABLE IF NOT EXISTS `tour_vote` (
`SID` varchar(12) NOT NULL,
`SName` varchar(20) NOT NULL,
`SCode` varchar(20) NOT NULL,
`SLoc` varchar(20) NOT NULL,
`SComment` varchar(200) NOT NULL,
`SDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
PHP表單處理
vote2.php(ver.2)
<html lang="lang="zh-Hant-TW"">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>畢業旅行投票</title>
</head>
<body>
<p>畢業旅行投票</p>
<form method='post' action='confirm2.php'>
<table border='1' width='100%' id='table1'>
<tr>
<td align='right' width='200'>學號</td>
<td><input type='text' name='SID' size='10'> </td>
</tr>
<tr>
<td align='right' width='200'>姓名</td>
<td><input type='text' name='SName' size='10'> </td>
</tr>
<tr>
<td align='right' width='200'>身份證末四碼</td>
<td><input type='text' name='SCode' size='10'></td>
</tr>
<tr>
<td align='right' width='200'>選擇地點</td>
<td><input type='radio' value='澎湖' name='SLoc'>澎湖
<input type='radio' value='花蓮' name='SLoc'>花蓮
<input type='radio' value='泰國' name='SLoc'>泰國</td>
</tr>
<tr>
<td align='right' width='200'>意見</td>
<td><input type='text' name='SComment' size='50'> </td>
</tr>
<tr>
<td align='right' width='200'> </td>
<td><input type='submit' name='Submit' value='投票'> </td>
</tr>
</table>
</form>
</body>
</html>
confirm2.php(ver.2)
<?php
//將表單元件的值轉成php變數
$Var1=htmlspecialchars($_POST["SID"]);
$Var2=htmlspecialchars($_POST["SName"]);
$Var3=htmlspecialchars($_POST["SCode"]);
$Var4=htmlspecialchars($_POST["SLoc"]);
$Var5=htmlspecialchars($_POST["SComment"]);
?>
<html lang="zh-Hant-TW">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>畢業旅行投票確認</title>
</head>
<body>
<p>畢業旅行投票-確認投票</p>
<form method='post' action='save2.php'>
<?php
print "
<table border='1' width='100%' id='table1'>
<tr>
<td align='right' width=200>學號</td>
<td><input type='hidden' name='SID' value='$Var1'>$Var1</td>
</tr>
<tr>
<td align='right' width='200'>姓名</td>
<td><input type='hidden' name='SName' value='$Var2'>$Var2</td>
</tr>
<tr>
<td align='right' width='200'>身份證末四碼</td>
<td><input type='hidden' name='SCode' value='$Var3'>$Var3</td>
</tr>
<tr>
<td align='right' width='200'>選擇地點</td>
<td><input type='hidden' name='SLoc' value='$Var4'>$Var4</td>
</tr>
<tr>
<td align='right' width='200'>意見</td>
<td><input type='hidden' name='SComment' value='$Var5'>$Var5</td>
</tr>
<tr>
<td align='right' width='200'> </td>
<td>
";
if ($Var4=='')
echo "沒有選擇地點<a href='javascript:history.back()'>,請回上一頁重新填寫</a>";
else
echo "若要更改<a href='javascript:history.back()'>,請回上一頁重新填寫</a>";
echo "
</td>
</tr>
</table>";
$row = NULL;
if (!empty($Var1))
{//檢查是否投過票
require_once 'db_func2.php';
$row = CheckVoted($Var1);
}
if ($row != NULL)
{
print "
您在".$row['SDate']."完成投票,您的選擇如下<br>
<table border='1' width='100%' id='table1'>
<tr>
<td align='right' width='200'>學號</td>
<td>".$row['SID']."</td>
</tr>
<tr>
<td align='right' width='200'>姓名</td>
<td>".$row['SName']."</td>
</tr>
<tr>
<td align='right' width='200'>身份證末四碼</td>
<td>".$row['SCode']."</td>
</tr>
<tr>
<td align='right' width='200'>選擇地點</td>
<td>".$row['SLoc']."</td>
</tr>
<tr>
<td align='right' width='200'>意見</td>
<td>".$row['SComment']."</td>
</tr>
<tr>
<td align=right width=200>是否更新</td>
<td align='center'>
<input type='hidden' name='SMethod' value='update' >
<input type='submit' name='Submit' value='重新投票'>
</td>
</tr>
</table>
";
}
else if (empty($Var1)||empty($Var2)||empty($Var3)||empty($Var4))//要有前四欄資料才能存檔
print "
<table border='1' width='100%' id='table1'>
<tr>
<td align='center'>
<input type='hidden' name='SMethod' value='insert' >
<input type='submit' name='Submit' value='確認投票' disabled='disabled'>
</td>
</tr>
</table>
";
else
print "
<table border='1' width='100%' id='table1'>
<tr>
<td align='right'>
<input type='hidden' name='SMethod' value='insert' >
<input type='submit' name='Submit' value='確認投票'>
</td>
</tr>
</table>
";
?>
</form>
</body>
</html>
db_func2.php
<?php
/**
* @file
* db_funtions.php: demo PDO usage
* Hsueh-Ting Chu 2017/10/13
*/
//require_once 'testlog.php';
$GLOBALS['dbconfig'] = array (
'database' => 'web2017',
'username' => 'student2017',
'password' => 'mALL7Fs2E7hcuXsD',
'host' => 'localhost',
'port' => '',
'driver' => 'mysql',
);
//CheckUser
/*
$link=mysql_connect("localhost", "bioinfo", "6193") or die(mysql_error());
mysql_select_db("dino") or die(mysql_error());
$query="SELECT SID, SName, SCode, SLoc, SComment, SDate FROM vote WHERE SID='$Var1'";
$result=mysql_query($query) or die(mysql_error());
$num_rows = mysql_num_rows($result);
if ($num_rows ==1)
{
$row = mysql_fetch_array($result);
*/
function CheckVotedTest($sID)
{
$row =array("SID"=>"920100625", "SName"=>"朱小弟", "SCode"=>"43");
return "Hello";
}
function CheckVoted($sID)
{
$dbconfig = $GLOBALS['dbconfig'];
$dsn = $dbconfig['driver'].":host=".$dbconfig['host'].";dbname=".$dbconfig['database'];
$pdo = new PDO($dsn, $dbconfig['username'],$dbconfig['password']);
$pdo->query("set names utf8");
$sql = "SELECT * FROM tour_vote WHERE SID = :SID";
$pdoStatement = $pdo->prepare($sql);
$pdoStatement->bindValue(':SID', $sID, PDO::PARAM_STR);
if (!$pdoStatement->execute()) {
print_r($pdoStatement->errorInfo());
return NULL;
}
$row = $pdoStatement->fetch();
if ($row)
{
return $row;
}
return NULL;
}
save2.php(ver.2)
<?php
//將表單元件的值轉成php變數
$Var1=htmlspecialchars($_POST["SID"]);
$Var2=htmlspecialchars($_POST["SName"]);
$Var3=htmlspecialchars($_POST["SCode"]);
$Var4=htmlspecialchars($_POST["SLoc"]);
$Var5=htmlspecialchars($_POST["SComment"]);
$Var6=htmlspecialchars($_POST["SMethod"]);
?>
<html lang="zh-Hant-TW">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>畢業旅行投票</title>
</head>
<?php
//將表單元件的值存入資料庫//
//
require_once 'db_func3.php';
SaveVote($Var1, $Var2, $Var3, $Var4, $Var5, $Var6);
?>
<body>
<p>畢業旅行投票-投票完成</p>
<?php
echo "
<table border='1' width='100%' id='table1'>
<tr>
<td align=right width=200>學號</td>
<td>$Var1</td>
</tr>
<tr>
<td align=right width=200>姓名</td>
<td>$Var2</td>
</tr>
<tr>
<td align=right width=200>身份證末四碼</td>
<td>$Var3</td>
</tr>
<tr>
<td align=right width=200>選擇地點</td>
<td>$Var4</td>
</tr>
<tr>
<td align=right width=200>意見</td>
<td>$Var5</td>
</tr>
</table>";
?>
</form>
<hr>
<a href='list2.php'>查看報名資料</a>
</body>
</html>
db_func3.php
<?php
/**
* @file
* db_funtions.php: demo PDO usage
* Hsueh-Ting Chu 2017/10/13
*/
//require_once 'testlog.php';
$GLOBALS['dbconfig'] = array (
'database' => 'web2017',
'username' => 'student2017',
'password' => 'mALL7Fs2E7hcuXsD',
'host' => 'localhost',
'port' => '',
'driver' => 'mysql',
);
/*
//將表單元件的值存入資料庫
$link=mysql_connect("localhost", "bioinfo", "6193") or die(mysql_error());
mysql_select_db("dino") or die(mysql_error());
if ($Var6 == 'insert')
$query="INSERT INTO vote(SID, SName, SCode, SLoc, SComment) VALUES('$Var1', '$Var2', '$Var3', '$Var4', '$Var5')";
else
$query="UPDATE vote SET SName='$Var2', SCode='$Var3', SLoc='$Var4', SComment='$Var5', SDate=NOW() WHERE SID='$Var1'";
$result=mysql_query($query) or die(mysql_error());
mysql_close($link);
*/
function SaveVoteTest($sID, $sName, $sCode, $sLoc, $sComment, $sMethod)
{
return "OK";
}
function SaveVote($sID, $sName, $sCode, $sLoc, $sComment, $sMethod)
{
$dbconfig = $GLOBALS['dbconfig'];
$dsn = $dbconfig['driver'].":host=".$dbconfig['host'].";dbname=".$dbconfig['database'];
$pdo = new PDO($dsn, $dbconfig['username'],$dbconfig['password']);
$pdo->query("set names utf8");
if ($sMethod == 'insert')
$sql="INSERT INTO tour_vote(SID, SName, SCode, SLoc, SComment) VALUES(:SID, :SName, :SCode, :SLoc, :SComment)";
else //($sMethod == 'update')
$sql="UPDATE tour_vote SET SName=:SName, SCode=:SCode, SLoc=:SLoc, SComment=:SComment WHERE SID=:SID";
$pdoStatement = $pdo->prepare($sql);
$pdoStatement->bindValue(':SID', $sID, PDO::PARAM_STR);
$pdoStatement->bindValue(':SName', $sName, PDO::PARAM_STR);
$pdoStatement->bindValue(':SCode', $sCode, PDO::PARAM_STR);
$pdoStatement->bindValue(':SLoc', $sLoc, PDO::PARAM_STR);
$pdoStatement->bindValue(':SComment', $sComment, PDO::PARAM_STR);
if (!$pdoStatement->execute()) {
print_r($pdoStatement->errorInfo());
}
}
list2.php(ver.2)
<html lang="zh-Hant-TW">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>畢業旅行投票資料</title>
</head>
<body>
<p>畢業旅行投票資料</p>
<table border="1" width="100%" id="table1">
<tr>
<td>學號</td>
<td>姓名</td>
<td>選擇地點</td>
<td>投票時間</td>
<td>意見</td>
</tr>
<?php
require_once 'db_func4.php';
$rowAll = ListVotes();
foreach($rowAll as $row)
{
print "
<tr>
<td>".$row["SID"]."</td>
<td>".$row["SName"]."</td>
<td>".$row["SLoc"]."</td>
<td>".$row['SDate']."</td>
<td>".$row["SComment"]."_</td>
</tr>";
}
?>
</table>
<hr>
投票結果
<table border="1" width="100%" id="table2">
<?php
$rowAll = CountVotes();
foreach($rowAll as $row)
{
print "
<tr>
<td>".$row["SLoc"]."</td>
<td>".$row["SNum"]."</td>
</tr>";
}
?>
</table>
</body>
</html>
db_func4.php
<?php
/**
* @file
* db_funtions.php: demo PDO usage
* Hsueh-Ting Chu 2017/10/13
*/
//require_once 'testlog.php';
$GLOBALS['dbconfig'] = array (
'database' => 'web2017',
'username' => 'student2017',
'password' => 'mALL7Fs2E7hcuXsD',
'host' => 'localhost',
'port' => '',
'driver' => 'mysql',
);
/*
//從資料庫讀出投票資料
<?php
$link=mysql_connect("localhost", "bioinfo", "6193") or die(mysql_error());
mysql_select_db("dino") or die(mysql_error());
$query="SELECT SID, SName, SLoc, SComment, SDate FROM vote ORDER BY SDate";
$result=mysql_query($query) or die(mysql_error());
$num_rows = mysql_num_rows($result);
echo "<p>".$num_rows."筆投票資料</p>
";
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
print "
<tr>
<td>".$row["SID"]."</td>
<td>".$row["SName"]."</td>
<td>".$row["SLoc"]."</td>
<td>".$row['SDate']."</td>
<td>".$row["SComment"]."_</td>
</tr>
";
}
*/
function ListVotesTest()
{
return "OK";
}
function ListVotes()
{
$dbconfig = $GLOBALS['dbconfig'];
$dsn = $dbconfig['driver'].":host=".$dbconfig['host'].";dbname=".$dbconfig['database'];
$pdo = new PDO($dsn, $dbconfig['username'],$dbconfig['password']);
$pdo->query("set names utf8");
$sql="SELECT * from tour_vote";
$pdoStatement = $pdo->prepare($sql);
if (!$pdoStatement->execute()) {
print_r($pdoStatement->errorInfo());
}
$rowAll = $pdoStatement->fetchAll();
return $rowAll;
}
/*
$query="SELECT Count(SLoc) AS SNum, SLoc FROM tour_vote GROUP BY SLoc";
$result=mysql_query($query) or die(mysql_error());
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
echo "
<td>".$row["SLoc"]."</td>
<td>".$row["SNum"]."</td>
";
}*/
function CountVotesTest()
{
return "OK";
}
function CountVotes()
{
$dbconfig = $GLOBALS['dbconfig'];
$dsn = $dbconfig['driver'].":host=".$dbconfig['host'].";dbname=".$dbconfig['database'];
$pdo = new PDO($dsn, $dbconfig['username'],$dbconfig['password']);
$pdo->query("set names utf8");
$sql="SELECT Count(SLoc) AS SNum, SLoc FROM tour_vote GROUP BY SLoc";
$pdoStatement = $pdo->prepare($sql);
if (!$pdoStatement->execute()) {
print_r($pdoStatement->errorInfo());
}
$rowAll = $pdoStatement->fetchAll();
return $rowAll;
}
SQL基本CRUD語法說明
SQL(Structured Query Language)結構化查詢語言，用來操作資料庫的讀寫。
数据操纵语言（Data Manipulation Language, DML）是SQL語言中，負責對資料庫物件執行資料存取工作的指令集，以 INSERT 、 UPDATE 、 DELETE 三種指令為核心，分別代表插入、更新与删除，是開發以資料為中心的應用程式必定會使用到的指令，因此有很多開發人員都把加上SQL的SELECT语句的四大指令以「CRUD」來稱呼。
SELECT
讀出資料： 從 (FROM)資料庫表格內選出 (SELECT)欄位資料
SELECT *
FROM Book
WHERE price > 100.00
ORDER BY title;
INSERT
插入資料：插入一筆資料到資料庫表格的欄位值
INSERT INTO example
(column1, column2, column3)
VALUES
('test', 'N', NULL);
UPDATE
更新資料：更新一筆資料到資料庫表格的欄位值
UPDATE example
SET column1 = 'updated value'
WHERE column2 = 'N';
SELECT
刪除資料
DELETE FROM example
WHERE column2 = 'N';
SQL條件語法說明
WHERE
WHERE 表示資料要選取或更新或刪除的"條件(= > < )";
UPDATE example
SET column1 = 'updated value'
WHERE column2 = 'N';
WHERE (LIKE)
萬用字元 (wildcard) % - The percent sign represents zero, one, or multiple characters _ - The underscore represents a single character
SELECT *
FROM Store_Information
WHERE store_name LIKE '%AN%';
| LIKE Operator | Description |
|---|---|
| WHERE CustomerName LIKE 'a%' | Finds any values that starts with "a" |
| WHERE CustomerName LIKE '%a' | Finds any values that ends with "a" |
| WHERE CustomerName LIKE '%or%' | Finds any values that have "or" in any position |
| WHERE CustomerName LIKE '_r%' | Finds any values that have "r" in the second position |
| WHERE CustomerName LIKE 'a%%' | Finds any values that starts with "a" and are at least 3 characters in length |
| WHERE ContactName LIKE 'a%o' | Finds any values that starts with "a" and ends with "o" |
SQL COUNT 和 DISTINCT 語法說明
SELECT COUNT (Store_Name)
FROM Store_Information
WHERE Store_Name IS NOT NULL;
SELECT COUNT (DISTINCT Store_Name)
FROM Store_Information;
SQL ORDER BY 語法說明
SELECT Store_Name, Sales, Txn_Date
FROM Store_Information
ORDER BY Sales DESC;
SELECT Store_Name, Sales, Txn_Date
FROM Store_Information
ORDER BY Sales ASC;
SELECT Store_Name, Sales, Txn_Date
FROM Store_Information
ORDER BY 2 DESC;