php的pdo库使用总结

<?php
try {
    $dsn = "mysql:host=127.0.0.1;port=3306;dbname=test;charset=utf8";
    $user = "root";
    $password = "";
    #持久连接
    $pdo = new PDO($dsn, $user, $password, [PDO::ATTR_PERSISTENT => true]);
    #设置获取值的方式
    $pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
    #设置报错模式，默认报错模式为silent模式：不会主动报错，还有警告模式：错误发生后通过php标准报告错误，异常模式：错误发生后抛出异常
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (Exception $error) {
    die('connect error');
}

try {
    #exec  
    $sql = 'insert into test(name,age) values ("lele",5),("lily",8)';
    $rows = $pdo->exec($sql);
    var_dump($rows);
    var_dump($pdo->lastInsertId());

    //query
    $sql = 'select * from test limit 5';
    $st = $pdo->query($sql);
    echo '<pre>';
    if ($st) {
        foreach ($st as $arr) {
            var_dump($arr);
        }
        // var_dump($st->fetchAll());
        var_dump($st->rowCount());
    }
} catch (Exception $error) {
    var_dump($error->getCode(), $error->getLine(), $error->getFile(), $error->getMessage());
}

#prepare预处理
try {
    $sql = 'insert into test(name,age) values (?,?)';
    $st = $pdo->prepare($sql);
    $st->bindValue(1, 'rose');
    $st->bindValue(2, 23);
    $st->execute();

    $sql = 'insert into test(name,age) values (:name,:age)';
    $st = $pdo->prepare($sql);
    #bindValue  把一个值绑定到一个参数  
    $name = 'cc';
    $age = 17;
    $st->bindValue(':name', $name, PDO::PARAM_STR);
    $st->bindValue(':age', $age, PDO::PARAM_INT);
    $st->execute();

    #bindParam 绑定一个参数到指定的变量名
    $st->bindParam(':name', $name, PDO::PARAM_STR);
    $st->bindParam(':age', $age, PDO::PARAM_INT);
    $name = 'aa';
    $age = 14;
    $st->execute();
    $name = 'bb';
    $age = 15;
    $st->execute();

    $st = $pdo->prepare('select * from test where name=:name');
    $st->bindValue(':name', 'lily', PDO::PARAM_STR);
    if ($st->execute()) {
        foreach ($st as $arr) {
            var_dump($arr);
        }
    }
} catch (Exception $error) {
    var_dump($error->getCode(), $error->getLine(), $error->getFile(), $error->getMessage());
}

#事务
try {
    $pdo->beginTransaction();
    $st = $pdo->prepare('insert into test(name,age) values (:name,:age)');
    $st->bindParam(':name', $name, PDO::PARAM_STR);
    $st->bindParam(':age', $age, PDO::PARAM_INT);

    $st_score = $pdo->prepare("insert into score(sid,score) values (:sid,:score)");
    $st_score->bindParam(':sid', $sid, PDO::PARAM_INT);
    $st_score->bindParam(':score', $score, PDO::PARAM_INT);

    $data = [
        ['name' => 'ee', 'age' => 24],
        ['name' => 'tt', 'age' => 23]
    ];
    $scores = [
        [89, 87, 67, 85],
        [78, 67, 59, 89]
    ];
    foreach ($data as $index => $item) {
        $name = $item['name'];
        $age = $item['age'];
        $st->execute();
        $sid = $pdo->lastInsertId();

        foreach ($scores[$index] as $val) {
            $score = $val;
            $st_score->execute();
        }
    }

    $pdo->commit();
} catch (Exception $error) {
    $pdo->rollBack();
    echo 'transaction failed';
}

$pdo = null;