PHPとDBの常識的な話

Transcript

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. 簡単 でしょ?
23. $DSN = “mysql:dbname=db1;charset=sjis”; $DSN = “pgsql:dbname=db1; options=‘--client_encoding=sjis’”;
25. 忘れずに
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(パラメータの配列)
32. $OPT = [PDO::ATTR_EMULATE_PREPARES => false];
33. 常識？
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
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.
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. 常識…？