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