Upgrade to Pro — share decks privately, control downloads, hide ads and more …

PHPとDBの常識的な話

ryer
February 19, 2017

 PHPとDBの常識的な話

ryer

February 19, 2017
Tweet

More Decks by ryer

Other Decks in Programming

Transcript

  1. None
  2. None
  3. None
  4. None
  5. None
  6. None
  7. None
  8. None
  9. None
  10. # yum install php-pdo

  11. $DSN = ‘mysql:host=10.0.0.1;dbname=userdb’; $OPT = [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]; $pdo =

    new PDO($DSN, $USER, $PASSWD, $OPT);
  12. $DSN = ‘mysql:host=10.0.0.1;dbname=userdb’;

  13. $OPT = [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION];

  14. $cnt = $pdo->exec(“INSERT INTO prefs (id, pref) VALUES (1, ’沖縄県')");

    $stmt = $pdo->query("SELECT * FROM prefs"); $rows = $stmt->fetchAll(PDO::FETCH_ASSOC); var_dump($rows);
  15. $cnt = $pdo->exec(更新SQL) $stmt = $pdo->query(選択SQL)

  16. $rows = $stmt->fetchAll();

  17. $pdo->beginTransaction(); try { ... $pdo->commit(); } catch (Exception $ex) {

    ... $pdo->rollBack(); }
  18. 簡単 でしょ?

  19. None
  20. None
  21. None
  22. None
  23. $DSN = “mysql:dbname=db1;charset=sjis”; $DSN = “pgsql:dbname=db1; options=‘--client_encoding=sjis’”;

  24. None
  25. 忘れずに

  26. None
  27. $sql = “SELECT * FROM prefs WHERE id =:id”; $stmt

    = $pdo->prepare($sql); $stmt->execute([‘id’ => 123]); $rows = $stmt->fetchAll(PDO::FETCH_ASSOC); var_dump($rows);
  28. $stmt = $pdo->prepare(プレースホルダ付SQL文) $stmt->execute(パラメータの配列)

  29. None
  30. None
  31. None
  32. $OPT = [PDO::ATTR_EMULATE_PREPARES => false];

  33. 常識?

  34. None
  35. None
  36. None
  37. // すべての記事をSELECT $sql = “SELECT * FROM kiji”; $stmt =

    $pdo->query($sql); $rows = $stmt->fetchAll(); foreach ($rows as $kiji) { // 記事の本文を更新する関数 update($kiji[’id’], $kiji[‘body’]); }
  38. // すべての記事をSELECT $sql = “SELECT * FROM kiji”; $stmt =

    $pdo->query($sql); $rows = $stmt->fetchAll(); foreach ($stmt as $kiji) { // 記事の本文を更新する関数 update($kiji[’id’], $kiji[‘body’]); }
  39. $ php update_kiji.php

  40. $ php update_kiji.php PHP Fatal error: Allowed memory size of

    134217728 bytes exhausted PHP Stack trace: PHP 1. update_kiji.php PHP 2. PDO->query()
  41. PHP Fatal error: Allowed memory size of 134217728 bytes exhausted

  42. None
  43. $OPT = [PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => false];

  44. PHP Fatal error: Uncaught PDOException: SQLSTATE[HY000]: General error: 2014 Cannot

    execute queries while other unbuffered queries are active.
  45. None
  46. // すべての記事をSELECT ini_set(“memory_limit”, “1G”); $sql = “SELECT id FROM kiji”;

    $stmt = $pdo->query($sql); foreach ($stmt as $kiji) { // 記事の本文をSELECTして更新する関数 update($kiji[’id’], $kiji[‘body’]); }
  47. 常識…?

  48. None