PHP操作mysql（mysqli+PDO）

  • Z时代
  • 2024-01-10
  • 分类：综合

database

【Mysqli面向对象方式操作数据库】

添加、修改、删除数据

$mysqli = new mysqli("localhost","root","123456","test");
$mysqli->query("set names utf8");
//添加数据
$result = $mysqli->query("INSERTINTO users(name,money) VALUE ("张三",10)");
$result = $mysqli->query("INSERTINTO users(name,money) VALUE ("李四",200)");
//修改数据
$result = $mysqli->query("UPDATE users SETmoney=money+10WHERE id =3");
//删除数据
$result = $mysqli->query("DELETEFROM 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 SETmoney=money-10where id=1";
$sql2 = "UPDATE users SETmoney=money+10where 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 = "INSERTINTO 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 SETmoney=500WHERE 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 SETmoney=money-100WHERE id=1";
$r1 = $pdo->exec($sql1);
$sql2 = "UPDATE1 users SETmoney=money+100WHERE 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 SETmoney=1000WHERE 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=1or1=1SELECT*FROM users
//SELECT*FROM users WHERE id=1;droptable 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);

以上是 PHP操作mysql（mysqli+PDO） 的全部内容， 来源链接： utcz.com/z/532030.html

回到顶部