Why don't you try "Query Builder"

PHPカンファレンス福岡2016のトークで使ったスライドです
関連レポジトリ）https://github.com/omoon/phpconfuk2016

Transcript

1. 8IZ
   EPO`U
   ZPV
   USZ
   2VFSZ
   #VJMEFS 1)1
   $POGFSFODF
   'VLVPLB
   
   .BZ
   
   
   4PUBSP
   0.63"
   
2. "CPVU
   .F w4PUBSP
   0.63"
   w0TBLB
   +BQBO
   w,BOTBJ
   1)1
   6TFST
   (SPVQ
   4UB⒎
   w1)1
   
   w-BSBWBM
   wPNPPOPSH
   w!PNPPO
4. 2VFSZ
   #VJMEFS
5. 2VFTUJPOT w%P
   ZPV
   LOPX
   42-
   w.Z42-
   
   1PTUHSF42-
   
   0SBDMF
   
   PUIFST
   
   w1%0
   w,OPX
   2VFSZ
   #VJMEFS
   w6TF
   2VFSZ
   #VJMEFS
   w6TF
   03.
6. /PUF w1%0
   w*MMVNJOBUF%BUBCBTF
   w.Z42-
7. 4BNQMF
   %BUBCBTF
9. 5PQJDT w8IBU
   JT
   l2VFSZ
   #VJMEFSz
   w#FOFpUT
   w)PX
   UP
   BQQMZ
   2VFSZ
   #VJMEFS
   UP
   ZPVS
   PXO
   BQQ
   w4PNF
   FYBNQMFT
10. 8IBU`T
    2VFSZ
    #VJMEFS
11. 1JD DPOTUSVDUJPO
12. 1JD DPOTUSVDUJPO
13. "
    UPPM
    UP
    CVJME
    42-
15. SELECT name, price
 FROM books
 WHERE name LIKE '%PHP%'
 ORDER BY price DESC
 LIMIT 10
16. SELECT name, price
 FROM books
 WHERE name LIKE '%PHP%'
 ORDER BY price DESC
 LIMIT 10
17. &BDI
    QBSU
    PG
    42-
    IBT
    B
    30-&
18. $builder->table('books')
 ->where('name', 'like', '%PHP%')
 ->orderBy('name', 'desc')
 ->take(10) ->select('name', 'price');
19. SELECT `name`, `price`
 FROM `books`
 WHERE `name` LIKE '%PHP%'
 ORDER BY `price` DESC
 LIMIT 10
20. DELETE FROM `books`
 WHERE `name` LIKE '%PHP%'
 ORDER BY `price` DESC
 LIMIT 10
21. $builder->table('books')
 ->where('name', 'like', '%PHP%')
 ->orderBy('price', 'desc')
 ->take(10) ->select('name', 'price');
22. $builder->table('books')
 ->where('name', 'like', '%PHP%')
 ->orderBy('price', 'desc')
 ->take(10) ->delete();
23. $builder->table('books')
 ->where('name', 'like', '%PHP%')
 ->orderBy('price', 'desc')
 ->take(10) ->select('name', 'price');
24. $builder->table('books')
 ->where('name', 'like', '%PHP%')
 ->orderBy('price', 'desc')
 ->take(10) ->select('name', ‘price') ->get();
25. #FOFpUT
26. #FOFpUT w&BTZ
    UP
    FEJU
    w4FDVSF
    w&BTZ
    UP
    SFBE
    w3FVTBCMF
27. 
    &BTZ
    UP
    FEJU
28. %FNP
29. 
    4FDVSF
30. $name = 'PHPΤϯδχΞཆ੒ಡຊ'; 
 $sql = "
 SELECT `name`, `price` 
 FROM `books` 
 WHERE `name` = $name
 ORDER BY `price` DESC ";
 
 $sth = $pdo->prepare($sql);
 $sth->execute();
33. $name = "t' OR 't' = 't";
 
 $sql = <<<'SQL'
 SELECT `name`, `price`
 FROM `books`
 WHERE `name` = $name
 ORDER BY `price` DESC
 SQL;
 
 $sth = $pdo->prepare($sql);
 $sth->execute();
35. $name = "t' OR 't' = 't";
 
 $sql = <<<SQL
 SELECT `name`, `price`
 FROM `books`
 WHERE `name` = ?
 ORDER BY `price` DESC
 SQL;
 
 $sth = $pdo->prepare($sql);
 $sth->execute([$name]);
37. $name = "t' OR 't' = 't"; 
 $builder->table('books')
 ->select('name', 'price')
 ->where('name', '=', $name)
 ->orderBy('name', 'desc');
38. 
    &BTZ
    UP
    SFBE
39. SELECT `name`, `price` 
 FROM `books` 
 WHERE `name` LIKE ‘%PHP%' AND `price` > 2000
 ORDER BY `price` DESC 
 LIMIT 10
40. $sql = "
 SELECT `name`, `price` 
 FROM `books` 
 WHERE `name` LIKE :name
 AND `price` > :price 
 ORDER BY `price` DESC 
 LIMIT :limit 
 ";
 
 $sth = $pdo->prepare($sql);
 $sth->bindParam(':name', $name);
 $sth->bindParam(':price', $price);
 $sth->bindParam(':limit', $limit, PDO::PARAM_INT);
 $sth->execute();
41. $name = '%PHP%';
 $price = 2000;
 $limit = 10; 
 $sql = "SELECT `name`, `price` 
 FROM `books` 
 WHERE `name` LIKE ? AND `price` > ?
 ORDER BY `price` DESC 
 LIMIT ?";
 
 $sth = $pdo->prepare($sql);
 $sth->execute([$name, $price, $limit]);
42. $name = '%PHP%';
 $price = 2000; $limit = 10;
 
 $builder->table('books')
 ->select('name', 'price')
 ->where('name', 'like', $name)
 ->where('price', '>', $price)
 ->orderBy('name', 'desc')
 ->take($limit);
44. 
    3FVTBCMF
45. SELECT `name`, `price` 
 FROM `books` 
 WHERE `price` < 2000
46. $cheap_books =
 $builder->table('books')
 ->where('price', '<', 2000);
47. $cheap_books->orderBy('name');
 $cheap_books->orderBy('price');
 $cheap_books->orderBy('price', 'desc');
 $cheap_books->orderBy('price', ‘desc') ->take(10);
48. $cheap_books->orderBy('price', ‘desc') ->take(10) ->select();
49. $cheap_books->orderBy('price', ‘desc') ->take(10) ->delete();
50. $cheap_books->count();
 $cheap_books->max('price');
 $cheap_books->min('price');
 $cheap_books->average('price');
 $cheap_books->sum('price');
51. #FOFpUT w&BTZ
    UP
    FEJU
    w4FDVSF
    w&BTZ
    UP
    SFBE
    w3FVTBCMF
52. )PX
    UP
    BQQMZ
    
    2VFSZ
    #VJMEFS
    
    UP
    ZPVS
    PXO
    BQQT
53. {
 "require": {
 "illuminate/database": "5.2.*"
 }
 }
54. require_once '/path/to/vendor/autoload.php';
55. use Illuminate\Database\Capsule\Manager as Builder; class Connection
 {
 private $host = 'localhost';
 private $username = 'user';
 private $password = 'pass';
 private $database = 'database';
 public function getBuilder()
 {
 $builder = new Builder();
 $builder->addConnection([
 'driver' => 'mysql',
 'host' => $this->host,
 'database' => $this->database,
 'username' => $this->username,
 'password' => $this->password,
 'charset' => 'utf8',
 'collation' => 'utf8_unicode_ci',
 'prefix' => '',
 ]);
 $builder->setAsGlobal();
 $builder->setFetchMode(PDO::FETCH_ASSOC);
 return $builder->connection();
 }
 } $builder = (new Connection())->getBuilder();
56. 4PNF
    NPSF
    FYBNQMFT
57. +0*/
58. SELECT
 `books`.`name` AS `book_name`,
 `books`.`price`,
 `authors`.`name` AS `author_name`,
 `authors`.`age`
 FROM `books` LEFT JOIN `authors`
 ON `books`.`author_id` = `authors`.`id`
 WHERE `books`.`price` >= ?
59. $results = $builder->table('books')
 ->leftJoin('authors', 'books.author_id', '=', 'authors.id')
 ->where('books.price', '>=', 5000)
 ->select(
 'books.name as book_name',
 'books.price',
 'authors.name as author_name',
 'authors.age'
 );
60. */4&35
61. $values = [];
 for ($i = 0; $i < 10000; $i++) {
 $values[] = [
 'name' => 'name' . $i,
 'author_id' => rand(1, 10),
 'price' => rand(500, 10000),
 ];
 }
 
 $sql = "INSERT INTO books (name, author_id, price) values (?, ?, ?)”; 
 $sth = $pdo->prepare($sql);
 foreach ($values as $value) {
 $sth->execute(array_values($value));
 }
 
 $builder->table('books')->insert($values);
62. INSERT INTO `books`(`name`, `author`, `price`) VALUES(?, ?, ?)
 .
 .
 .
 VS 
 INSERT INTO `books`(`name`, `author`, `price`) VALUES(?, ?, ?),(?, ?, ?),(?, ?, ?)......
63. NZTRMJ T 1%0 T 2VFSZ
    #VJMEFS NT
64. 61%"5&
    
    3"8
65. UPDATE `books`
 SET `name` = CONCAT('ʲ͍҆Αʂʳ', `name`)
 WHERE `price` < 3000
66. $cheap_books->update([
 'name' =>"CONCAT('ʲ͍҆Αʂʳ' , `name`)"
 ]);
68. $cheap_books->update([
 'name' => $builder->raw( "CONCAT('ʲ͍҆Αʂʳ' , `name`)" )
 ]);
70. "OZ
    2VFTUJPOT