php的pdo库使用总结

连接

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

query

需注意防止SQL注入

try {
    #query
    $sql = 'select * from test limit 5';
    $result = $pdo->query($sql);
    if ($result) {
        var_dump($result->fetchAll());
        var_dump($result->rowCount());
    }

    #query
    $sql = 'select * from test limit 5';
    $result = $pdo->query($sql);
    if ($result) {
        foreach ($result as $row) {
            var_dump($row);
        }
    }
} catch (Exception $error) {
  var_dump($error->getCode(), $error->getLine(), $error->getFile(), $error->getMessage());
}

exec

需注意防止SQL注入

try {
    #exec  
    $sql = 'insert into test(name,age) values ("lele",5),("lily",8)';
    $affect_rows = $pdo->exec($sql);
    var_dump($affect_rows, $pdo->lastInsertId());
} catch (Exception $error) {
    var_dump($error->getCode(), $error->getLine(), $error->getFile(), $error->getMessage());
}

prepare

防止SQL注入的最佳方案

#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]
    ];
    $num = 0;
    $num_score = 0;
    $total = count($data);
    $total_score = 0;
    foreach ($data as $index => $item) {
        $name = $item['name'];
        $age = $item['age'];
        $st->execute();
        $sid = $pdo->lastInsertId();
        $num += $st->rowCount();

        foreach ($scores[$index] as $val) {
            $total_score += 1;
            $score = $val;
            $st_score->execute();
            $num_score += $st_score->rowCount();
        }
    }

    if ($total == $num && $total_score == $num_score) {
        $pdo->commit();
        echo 'commit';
    } else {
        $pdo->rollBack();
        echo 'rollback';
    }
} catch (Exception $error) {
    var_dump($error->getCode(), $error->getLine(), $error->getFile(), $error->getMessage());
}