PHP中PDO的基本操作

PDO是php连接数据库的一套接口，下面对基本的操作进行整理总结 简单的连接数据库

$dsn = "mysql:dbname=student;host=127.0.0.1"
$usr = 'root';
$passwd = '';
try
{
    $db = new PDO($dsn,$usr,$passwd);
    $sql = 'select * from info';
    $res = query(sql); //执行查询语句
   $res = exec(sql); //执行增删改语句 
}
catch(PDOException $e)
{
    echo 'faile'.$e.getMessage();
}

预处理

因为上述简单连接存在一定安全风险，所以需要利用预处理进行连接

• 占位符插入预处理

include("pdo.php");
try
{
    $sql = 'insert into info(name, age, sex) value(?,?,?)';//设置占位符
    $stmt = $db->prepare($sql);
    
    /*
    $stmt -> bindParam(1, $name);
    $stmt -> bindParam(2, $age);
    $stmt -> bindParam(3, $sex);
    
    $name = "张三";
    $age = "12";
    $sex = "男";
    $stmt -> execute();
    */
    $stmt -> execute(array("李四", 12, "女")); //简写采用索引数组
}
catch(PDOException $e)
{
    echo $e->getMessage();
}

• 别名插入预处理

include("pdo.php")
<?php
try
{
    $sql = 'insert into usr(name,age,sex) value(:name,age,sex)';//这里注意一定要用冒号
    $stmt = $db->prepare($sql);
    /*
    $stmt -> bindParam(":name", $name);
    $stmt -> bindParam(":age", $age);
    $stmt -> bindParam(":sex", $sex);
    
    $name = "张三";
    $age = 12;
    $sex = '男';
    $stmt -> execuete();
    */
    $arr = array("name"=>"李四", "age"=>12, "sex"=> "男");
    $stmt->excute($arr);//简写采用关联数组
}
catch(PDOException $e)
{echo $e-> getMessage();}
?>

• 查询预处理

<?php
include("pdo.php");
try
{
    $sql = "select * from student where name = :name";
    $stmt = $db->prepare($sql);
    
    $stmt->execute($_GET);
    $usr = $stmt -> fetch();//提取一条查询到的内容
   $usr = $stmt -> fetchAll(); //提取多条查询到的内容
   $num = $stmt -> rowCount(); //返回查询后的条数
}
catch(PDOExcption $e)
{
    echo $e->getMessage();
}
?>

• 事务预处理

<?php
include("pdo.php");
try
{
    $pdo -> setAttribute(PDO::ATTR_AUTOCOMMIT,0);   //禁止自动提交
    $pdo -> beginTransaction(); //开始执行事务
    $sql = "update student set age = age -1 where name = '张三'";
    $res = $pdo -> exec($sql);
    if(!res){
        throw new PDOException("传入失败");
    }
    
    $sql = "update student set age = age+ 1 where name = '李四'";
    $res = $pdo->exec($sql);
    if(!res)
    {
        throw new PDOException("传入失败");
    }
    $pdo->commit();
    echo "更改成功";
}
catch(PDOException $e)
{
    echo $e->getMessage();
    $pdo->rollback();
}
$pdo -> setAttribute(PDO::AUTOCOMMIT,1);
?>