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

  $st = $pdo->prepare('select * from test where name=:name');
  if ($st->execute([':name' => 'lily'])) $result = $st->fetchAll();
} 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;