Embedded Files
Mysqli &PDO 介紹：
MySQLi (MySQL Improved) 是 MySQL 針對 PHP 所設計的一個擴充模組，其可用來存取MySQL 資料庫之函式庫。一般來說，連結不同資料庫時都需要使用不同的語法，而PDO (PHP Data Objects) 為物件導向的設計方式與資料庫進行連結，其特性是把存取資料庫的動作抽象化，讓使用者可以使用一致的方式存取和處理資料 (即將存取出來的資料存入物件當中 )。
Server 連線與選擇資料庫：
在寫程式的時候，必須先與資料庫伺服器進行連線，再來選擇資料庫，所以在第一段程式是判斷帳號、密碼與Host主機，在第二段程式是判斷是否有權限操作指定的資料庫。
使用 Mysqli 連線 Server 語法：
$連線物件 = new mysqli(‘主機','帳號','密碼','資料庫');
$mysqli = new mysqli('localhost','root','admin','hrebc');
建立資料庫程式範例：
<?php
header("Content-Type:text/html; charset=utf-8");
$mysqli = new mysqli('localhost','root','admin');
if ($mysqli->connect_error) { die('連結錯誤訊息: ' . $mysqli->connect_error."<br/>");}
$sql = "create database hrebc DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;";
if($mysqli -> query($sql)){
echo "新增資料庫成功<br/>";
}else{ echo "新增資料庫失敗<br/>"; }
$mysqli->close();
?>
連接資料庫程式範例：
<?php
header("Content-Type:text/html; charset=utf-8");
$mysqli = new mysqli('localhost','root','admin','hrebc');
if ($mysqli->connect_error) { die('連結錯誤訊息: ' . $mysqli->connect_error."<br/>");
}else {
echo "資料庫伺服器連結成功、hrebc資料庫開啟成功。"; }
$mysqli->close();
?>
如果要切換資料庫語法如下：
<?php
header("Content-Type:text/html; charset=utf-8");
$mysqli = new mysqli('localhost','root','admin','hrebc');
if (!$mysqli->select_db('test')){
die("無法開啟 test 資料庫!<br/>");
}else{
echo "切換至 test 資料庫成功!<br/>";}
$mysqli->close();
?>
SQL 建立資料表程式範例：
<?php
header("Content-Type:text/html; charset=utf-8");
$mysqli = new mysqli('localhost','root','admin','hrebc');
if ($mysqli->connect_error) { die('連結錯誤訊息: ' . $mysqli->connect_error."<br/>");}
$sql = "create table hrebc15 (sid integer auto_increment primary key, eid char(30), name char(30), department char(30), position char(30), Tel char(20), ext char(10), mobile char(20), mail_addr char(60), line char(20), web_addr char(60), bkg_addr char(60))";
if($mysqli -> query($sql)){
echo "新增 hrebc15 資料表成功<br/>";
}else{ echo "新增 hrebc15 資料表失敗<br/>"; }
$mysqli->close();
?>
SQL 連接資料表暨插入欄位資料(增加資料)程式範例：
<?php
header("Content-Type:text/html; charset=utf-8");
$mysqli = new mysqli('localhost','root','admin','hrebc');
$mysqli->query("SET CHARACTER SET utf8");
if ($mysqli->connect_error) { die('連結錯誤訊息: ' . $mysqli->connect_error."<br/>");}
$eid = "104481001"; $name = "Bless01"; $department = "資訊部";
$position = "經理"; $Tel = "03-123456"; $ext = "168"; //工程師
$sql = "insert into hrebc15 (eid,name,department,position,Tel,ext,mobile,mail_addr,line,web_addr,bkg_addr) values('$eid','$name','$department','$position','$Tel','$ext','0937130xxx','chuang???@gmail.com','Line168','www.google.com.tw','no')";
if($mysqli -> query($sql)){
echo "在hrebc15 資料表中新增一筆資料成功<br/>";
}else{ echo "在hrebc15 資料表中新增一筆資料失敗<br/>"; }
$mysqli->close();
?>
SQL 查詢資料表所有資料
<?php
header("Content-Type:text/html; charset=utf-8");
$mysqli = new mysqli('localhost','root','admin','hrebc');
$mysqli->query("SET CHARACTER SET utf8");
if ($mysqli->connect_error) { die('連結錯誤訊息: ' . $mysqli->connect_error."<br/>");}
$sql = "select * from hrebc15";
$result = $mysqli->query($sql);
if ($result->num_rows > 0) {
while($row = $result->fetch_assoc()) { // fetch_assoc() 將讀出的資料Key值設定為該欄位的名稱。
echo "序號:".$row["sid"].", 名字:".$row["name"].", 部門:".$row["department"].", 職位:".$row["position"]."<br/>"; }
} else { echo "沒有資料!"; }
$mysqli->close();
?>
SQL 查詢資料表之條件式查詢 and
<?php
header("Content-Type:text/html; charset=utf-8");
$mysqli = new mysqli('localhost','root','admin','hrebc');
$mysqli->query("SET CHARACTER SET utf8");
if ($mysqli->connect_error) { die('連結錯誤訊息: ' . $mysqli->connect_error."<br/>");}
$sql = "select * from hrebc15 where sid=2 and name='Bless02'";
$result = $mysqli->query($sql);
if ($result->num_rows > 0) {
while($row = $result->fetch_assoc()) { // fetch_assoc() 將讀出的資料Key值設定為該欄位的名稱。
echo "序號:".$row["sid"].", 名字:".$row["name"].", 部門:".$row["department"].", 職位:".$row["position"]."<br/>"; }
} else { echo "沒有資料!"; }
$mysqli->close();
?>
XAMPP / PDO 支援，可執行 <?php phpinfo(); ?> 查看
使用 PDO 連線 Server 語法：
$連線物件 = new PDO("mysql:host=主機; dbname = 資料庫", "帳號", "密碼");
$conn = new PDO("mysql:host=$host; dbname=$dbname", $user, $password);
PDO 連接資料庫程式範例：
<?php
header("Content-Type:text/html; charset=utf-8");
$host = 'localhost'; $dbname = 'hrebc'; $user = 'root'; $password = 'admin';
try {
$conn = new PDO("mysql:host=$host; dbname=$dbname", $user, $password);
echo '資料庫伺服器連結成功、hrebc資料庫開啟成功。';
$conn = null; // disconnect
} catch (PDOException $e) { echo $e->getMessage(); }
?>
PDO 連接資料表暨插入欄位資料(增加資料)程式範例：
<?php
header("Content-Type:text/html; charset=utf-8");
$host = 'localhost'; $dbname = 'hrebc'; $user = 'root'; $password = 'admin';
try {
$conn = new PDO("mysql:host=$host; dbname=$dbname", $user, $password); // 在PDO中的「=」前後不能有空白, 否則資料庫不會作動作。
// echo '資料庫伺服器連結及資料庫開啟成功<br>';
$conn->exec("SET CHARACTER SET utf8");
$eid = "104481001"; $name = "Bless01"; $department = "資訊部";
$position = "工程師"; $Tel = "03-123456"; $ext = "168"; //工程師
$mobile =""; $mail_addr=""; $line = ""; $web_addr =""; $bkg_addr ="";
$sql = $conn->prepare("insert into hrebc15 (eid,name,department,position,Tel,ext,mobile,mail_addr,line,web_addr,bkg_addr) values (:eid,:name,:department,:position,:Tel,:ext,:mobile,:mail_addr,:line,:web_addr,:bkg_addr)") ;
$sql->execute (array("eid"=>$eid,"name"=>$name,"department"=>$department,"position"=>$position,"Tel"=>$name,"ext"=>$ext,"mobile"=>$mobile,"mail_addr"=>$mail_addr,"line"=>$line,"web_addr"=>$web_addr,"bkg_addr"=>$bkg_addr));
echo "在hrebc15 資料表中新增一筆資料成功<br/>";
$conn = null; // Disconnect
} catch(PDOException $e) { echo $e->getMessage(); }
?>
在 values 綁定的欄位參數可以使用「?」設定之，但在 array 時僅需引用變數。
<?php
header("Content-Type:text/html; charset=utf-8");
$host = 'localhost'; $dbname = 'hrebc'; $user = 'root'; $password = 'admin';
try {
$conn = new PDO("mysql:host=$host; dbname=$dbname", $user, $password); // 在PDO中的「=」前後不能有空白, 否則資料庫不會作動作。
// echo '資料庫伺服器連結及資料庫開啟成功<br>';
$conn->exec("SET CHARACTER SET utf8");
$eid = "1044810013"; $name = "Bless13"; $department = "資訊部";
$position = "工程師"; $Tel = "03-123456"; $ext = "168"; //工程師
$mobile =""; $mail_addr=""; $line = ""; $web_addr =""; $bkg_addr ="";
$sql = $conn->prepare("insert into hrebc15 (eid,name,department,position,Tel,ext,mobile,mail_addr,line,web_addr,bkg_addr) values (?,?,?,?,?,?,?,?,?,?,?)"); // 在 values 綁定的欄位參數可以使用「?」設定之，但在array時僅需引用變數。
$sql->execute (array($eid,$name,$department,$position,$name,$ext,$mobile,$mail_addr,$line,$web_addr,$bkg_addr));
echo "在hrebc15 資料表中新增一筆資料成功<br/>";
$conn = null; // Disconnect
} catch(PDOException $e) { echo $e->getMessage(); }
?>
查詢特定筆數資料
<?php
header("Content-Type:text/html; charset=utf-8");
$host = 'localhost'; $dbname = 'hrebc'; $user = 'root'; $password = 'admin';
try {
$conn = new PDO("mysql:host=$host; dbname=$dbname", $user, $password);
$conn->exec("SET CHARACTER SET utf8");
$sql = "select * from hrebc15 limit 2, 3"; // (第2筆資料開始選取3筆資料：第3、4、5筆)
$result = $conn->query($sql);
foreach ($result->fetchAll() as $ary){
echo $ary['eid']. ', '.$ary['name']. ', '.$ary['department']. ', '.$ary['position']. ', '.$ary['Tel']. ', '.$ary['ext']. ', '.$ary['mobile']. ', '.$ary['mail_addr']. ', '.$ary['line']. ', '.$ary['web_addr']. ', '.$ary['bkg_addr']. "<br><br>"; }
$conn = null; // Disconnect
} catch(PDOException $e) { echo $e->getMessage(); }
?>
查詢某一範圍 between
<?php
header("Content-Type:text/html; charset=utf-8");
$host = 'localhost'; $dbname = 'hrebc'; $user = 'root'; $password = 'admin';
try {
$conn = new PDO("mysql:host=$host; dbname=$dbname", $user, $password);
$conn->exec("SET CHARACTER SET utf8");
$sql = "select * from hrebc15 where sid between 2 and 3";
$result = $conn->query($sql);
foreach ($result->fetchAll() as $ary){
echo $ary['sid']. ', '.$ary['eid']. ', '.$ary['name']. ', '.$ary['department']."<br><br>"; }
$conn = null; // Disconnect
} catch(PDOException $e) { echo $e->getMessage(); }
?>
更新欄位資料(修改資料)
<?php
header("Content-Type:text/html; charset=utf-8");
$host = 'localhost'; $dbname = 'hrebc'; $user = 'root'; $password = 'admin';
try {
$conn = new PDO("mysql:host=$host; dbname=$dbname", $user, $password);
$conn->exec("SET CHARACTER SET utf8");
$sql = "update hrebc15 set name='Chuan-Fu' where eid='104481003'";
$conn->query($sql);
$conn = null; // Disconnect
} catch(PDOException $e) { echo $e->getMessage(); }
?>
刪除條件值資料
<?php
header("Content-Type:text/html; charset=utf-8");
$host = 'localhost'; $dbname = 'hrebc'; $user = 'root'; $password = 'admin';
try {
$conn = new PDO("mysql:host=$host; dbname=$dbname", $user, $password);
$conn->exec("SET CHARACTER SET utf8");
$sql = "delete from hrebc15 where sid between 4 and 10";
// $sql = "delete from hrebc15 where sid = 2 "; // 數值
// $sql = "delete from hrebc15 where eid = '104481003' "; // 字元
$conn->query($sql);
$conn = null; // Disconnect
} catch(PDOException $e) { echo $e->getMessage(); }
?>
Report abuse