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