作业内容:
1. 实例演示数据库的CURD操作
<?php
namespace pdo_edu;
//var_dump($db); 测试是否连接成功
/**
* 数据库常用操作
* 1. 读操作: select
* 2. 写操作: insert,update,delete
* 简称: CURD, 增删改查
*/
//1、连接数据库
//require __DIR__.'/hw.php';
use PDO;
/**操作数据库三步:
* 1、连接数据库
* 2、增删改查CURD
* 3、关闭连接
*/
//DSN:数据库的配置参数,类型,主机名,默认数据库,端口号,默认字符编码
// $dbConfig = require __DIR__.'/myconfig.php';
$dbConfig =[
'type'=>'mysql',
'host' => 'localhost',
'dbname' => 'testsql',
'port'=>'3306',
'charset'=>'utf8',
'username' => 'zolo',
'password' => '123456'
];
//import variables into the current symbol table from an array
extract($dbConfig);
// Source Host : localhost:3306
// Source Schema : zolotest
$db = new PDO("$type:host=$host;port=$port;dbname=$dbname;charset=$charset",$username,$password);
//$db = new PDO($dsn,$username,$password);
//2、操作
// 新增操作
$sql = 'INSERT `users` SET `name` = ?,`dept`= ?,`grade`= ?';
$stmt = $db->prepare($sql);
if($stmt ->execute(['zolo','SaaS','7'])){
$stmt ->execute(['test','Air','6']);
$stmt ->execute(['tony','Air','3']);
$stmt ->execute(['miny','SaaS','6']);
$stmt ->execute(['galen','Air','2']);
$stmt ->execute(['rita','SaaS','6']);
$stmt ->execute(['selina','Air','1']);
if($stmt->rowCount()>0){
echo "执行成功<br>";
}else{
echo "rowCount失败<br>";
print_r($stmt->errorInfo());
}
}else{
echo "execute执行失败<br>";
print_r($stmt->errorInfo());
}
//更新操作
$sql = 'UPDATE `users` SET `name` = ? where `id`=?';
$stmt = $db->prepare($sql);
if($stmt ->execute(['sss',1])){
if($stmt->rowCount()>0){
echo "更新成功<br>";
}else{
echo "rowCount失败<br>";
print_r($stmt->errorInfo());
}
}else{
echo "execute更新失败<br>";
print_r($stmt->errorInfo());
}
//删除操作
$sql = 'DELETE from `users` WHERE `id` = ? ';
// 对应传参:[2]
//$sql = 'DELETE from `users` WHERE `id` = :id ';
//对应传参:([':id'=>2]
$stmt = $db->prepare($sql);
//$_GET['id']获取传参id http://php19.edu/426/hw1.php?id=3
if($stmt ->execute([$_GET['id']])){
if($stmt->rowCount()>0){
echo "删除成功<br>";
}else{
print_r($stmt->errorInfo());
echo "rowCount失败<br>";
}
}else{
echo "execute删除失败<br>";
print_r($stmt->errorInfo());
}
//查询操作
//单条查询
$sqlquery = "select * from `users` where `dept` = 'SaaS'";
$stmt = $db->prepare($sqlquery);
if($stmt ->execute()){
if($stmt->rowCount()>0){
$res = $stmt->fetch(PDO::FETCH_ASSOC);
echo "单条查询成功:<br>";
printf('<pre>%s</pre>',print_r($res,true));
}else{
echo "rowCount失败<br>";
print_r($stmt->errorInfo());
}
}else{
echo "execute查询失败<br>";
print_r($stmt->errorInfo());
}
//多条查询
if($stmt ->execute()){
if($stmt->rowCount()>0){
$res = $stmt->fetchAll(PDO::FETCH_ASSOC);
echo "批量查询成功:<br>";
foreach($res as $user){
printf('<pre>%s</pre>',print_r($user,true));}
}else{
echo "rowCount失败<br>";
print_r($stmt->errorInfo());
}
}else{
echo "execute查询失败<br>";
print_r($stmt->errorInfo());
}
2. 描述PDO的本质与原理是什么?为什么要用预处理?
PHP 数据对象 (PDO) 扩展为PHP访问数据库定义了一个轻量级的一致接口。
PDO 提供了一个 数据访问 抽象层,这意味着,不管使用哪种数据库,都可以用相同的函数(方法)来查询和获取数据。
/**
* PDO预处理
* 为什么要用预处理?
* 1. 防止SQL注入攻击, 2. 数据延迟绑定
* (编程时只写SQL语句模板,执行SQL时再给占位符绑定真实数据)
* 预处理过程:
* 1. 创建SQL语句模板对象: 数据使用占位符表示
* 2. 执行SQL语句,根据操作类型(写/读),读返回结果集/数组, 写返回受影响的记录数量
*/