PHP操作mysql（mysqli + PDO）

【Mysqli面向對象方式操作資料庫】

添加、修改、刪除數據

$mysqli = new mysqli('localhost','root','123456','test');
$mysqli->query('set names utf8');
//添加數據
$result = $mysqli->query("INSERT INTO users(name,money) VALUE ('張三',10)");
$result = $mysqli->query("INSERT INTO users(name,money) VALUE ('李四',200)");

//修改數據
$result = $mysqli->query("UPDATE users SET money=money+10 WHERE id = 3");

//刪除數據
$result = $mysqli->query("DELETE FROM users WHERE id=3");

var_dump($result);

查詢數據

header("content-type:text/html;charset=utf-8");
$mysqli = new mysqli('localhost','root','123456','test');
$mysqli->query('set names utf8');

$result = $mysqli->query("SELECT * FROM users");
$data = $result->fetch_all(MYSQLI_ASSOC);
var_dump($data);

事務控制

header('content-type:text/html;charset=utf-8');
$mysqli = new mysqli('localhost','root','123456','test');
$mysqli->query('set names utf8');
$mysqli->autocommit(false); //開啟事務
$sql1 = "UPDATE users SET money=money-10 where id=1";
$sql2 = "UPDATE users SET money=money+10 where id=20";
$mysqli->query($sql1);
$r1 = $mysqli->affected_rows;
$mysqli->query($sql2);
$r2 = $mysqli->affected_rows;
if($r1>0 && $r2>0){
    $mysqli->commit(); //事務提交
    echo '操作成功';
}else{
    $mysqli->rollback(); //事務回滾
    echo '操作失敗';
}

預處理-增刪改操作

header('content-type:text/html;charset=utf-8');
$mysqli = new mysqli('localhost','root','123456','test');
$mysqli->query('set names utf8');
$sql = "INSERT INTO users(name,money) VALUE(?,?)";
$stmt = $mysqli->prepare($sql);

$name = "王小小";
$money = 500;
$stmt->bind_param('si',$name,$money);
$result = $stmt->execute();
var_dump($result);

$name = "王大大";
$money = 600;
$stmt->bind_param('si',$name,$money);
$result = $stmt->execute();
var_dump($result);

預處理-查詢操作

header('content-type:text/html;charset=utf-8');
$mysqli = new mysqli('localhost','root','','test');
$mysqli->query('set names utf8');
$sql = "SELECT * FROM users WHERE id>?";
$stmt = $mysqli->prepare($sql);
$id=1;
$stmt->bind_param('i',$id);
$stmt->bind_result($id,$name,$money);
$stmt->execute();

while($stmt->fetch()){
    $data[] = [
        'id'=>$id,
        'name'=>$name,
        'money'=>$money
    ];
}
var_dump($data);

【PDO方式操作資料庫】

PDO查詢數據

header('content-type:text/html;charset=utf-8');
$dsn = "mysql:host=localhost;dbname=test";
$pdo = new PDO($dsn,'root','123456');
$pdo->exec('set names utf8');
$sql = "SELECT * FROM users";
$stmt = $pdo->query($sql);
//$data = $stmt->fetch(PDO::FETCH_ASSOC);
$data = $stmt->fetchAll(PDO::FETCH_ASSOC);
var_dump($data);

PDO增刪改數據

header('content-type:text/html;charset=utf-8');
$dsn = "mysql:host=localhost;dbname=test";
$pdo = new PDO($dsn,'root','123456');
$pdo->exec('set names utf8');
$sql = "UPDATE users SET money=500 WHERE id=1";
$result = $pdo->exec($sql);
var_dump($result);

PDO事務控制

header('content-type:text/html;charset=utf-8');
$dsn = "mysql:host=localhost;dbname=test";
$pdo = new PDO($dsn,'root','123456');
$pdo->exec('set names utf8');
$pdo->beginTransaction(); //開啟事務
$sql1 = "UPDATE users SET money=money-100 WHERE id=1";
$r1 = $pdo->exec($sql1);

$sql2 = "UPDATE1 users SET money=money+100 WHERE id=2";
$r2 = $pdo->exec($sql2);

if($r1>0 && $r2>0){
    $pdo->commit(); //事務提交
    echo "操作成功";
}else{
    $pdo->rollBack(); //事務回滾
    echo "操作失敗";
}
$pdo->setAttribute(PDO::ATTR_AUTOCOMMIT,1);
//var_dump($result);

PDO預處理

header('content-type:text/html;charset=utf-8');
$dsn = "mysql:host=localhost;dbname=test";
$pdo = new PDO($dsn,'root','123456');
$pdo->exec('set names utf8');

//$sql = "SELECT * FROM users WHERE id>:id";
$sql = "UPDATE users SET money=1000 WHERE id=:id";
$stmt = $pdo->prepare($sql);

$id = 4;
$stmt->bindParam(":id",$id);

//$stmt->bindValue(1,2);

$result = $stmt->execute();
var_dump($result);

//$data  = $stmt->fetchAll(PDO::FETCH_ASSOC);
//var_dump($data);

常見的SQL註入方式及防範措施

header('content-type:text/html;charset=utf-8');
$dsn = "mysql:host=localhost;dbname=test";
$pdo = new PDO($dsn,'root','123456');
$pdo->exec('set names utf8');
$id = isset($_GET['id'])?$_GET['id']:1;
$sql = "SELECT * FROM users WHERE id=".$id;
$stmt = $pdo->query($sql);
$data = $stmt->fetchAll(PDO::FETCH_ASSOC);
var_dump($data);

//SELECT * FROM users WHERE id=1
//SELECT * FROM users WHERE id=1 or 1=1    SELECT * FROM users
//SELECT * FROM users WHERE id=1;drop table test;--

或

header('content-type:text/html;charset=utf-8');
$dsn = "mysql:host=localhost;dbname=test";
$pdo = new PDO($dsn,'root','123456');
$pdo->exec('set names utf8');

$sql = "SELECT * FROM users WHERE id=:id";
$stmt = $pdo->prepare($sql);
$id = isset($_GET['id'])?$_GET['id']:1;
$stmt->bindParam(":id",$id);

$result = $stmt->execute();
$data = $stmt->fetchAll(PDO::FETCH_ASSOC);
var_dump($data);