PHP操作mysql（mysqli + PDO）

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

添加、修改、删除数据

$mysqli = new mysqli('localhost','root','','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','','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','','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','','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','');

$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','');

$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','');

$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','');

$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','');

$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','');

$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);