Upgrade to Pro
— share decks privately, control downloads, hide ads and more …
Speaker Deck
Speaker Deck
PRO
Sign in
Sign up for free
Why don't you try "Query Builder"
omoon
May 21, 2016
Technology
1
2.8k
Why don't you try "Query Builder"
PHPカンファレンス福岡2016のトークで使ったスライドです
関連レポジトリ)
https://github.com/omoon/phpconfuk2016
omoon
May 21, 2016
Tweet
Share
More Decks by omoon
See All by omoon
My First Adobe XD Plugin
omoon
0
89
LaravelでTDDを体験しよう #phpcon2018
omoon
1
2.6k
Laravel でアプリケーションを開発し Heroku で公開するまでの一部始終 #laravel_osaka
omoon
3
1.2k
素晴らしき Carbon の世界 〜あなたも今日から日時マスター〜
omoon
4
12k
omoon.org の裏側 〜FuelPHP の task 活用例〜
omoon
0
95
Dive into the Cloud with our buddy, lovely PHP!
omoon
0
650
FuelPHP をさわってみて #phpkansai
omoon
2
140
Other Decks in Technology
See All in Technology
SRE_チーム立ち上げから1年_気づいたら_SRE_っぽくない仕事まで貢献しちゃってる説
bitkey
PRO
0
2.1k
YAMLを書くだけで構築できる分散ストレージ
sat
PRO
0
180
1年間のポストモーテム運用とそこから生まれたツール sre-advisor / SRE NEXT 2022
fujiwara3
6
3.1k
srenext2022-skaru
mixi_engineers
1
520
Graph API について
miyakemito
0
230
runn is a package/tool for running operations following a scenario. / golang.tokyo #32
k1low
1
160
開発者のための GitHub Organization の安全な運用と 継続的なモニタリング
flatt_security
3
3.6k
2022年度ロボットフロンティア第1回
ryuichiueda
0
140
Devに力を授けたいSREのあゆみ / SRE that wants to empower developers
tocyuki
3
470
ZOZOTOWNのProduction Readiness Checklistと信頼性向上の取り組み / Improvement the reliability of ZOZOTOWN with Production Readiness Checklist
akitok_
5
1.5k
New Features in C# 10/11
chack411
0
920
Spotify物理コントローラーがほしい
miso
0
160
Featured
See All Featured
It's Worth the Effort
3n
172
25k
Automating Front-end Workflow
addyosmani
1351
200k
The Art of Programming - Codeland 2020
erikaheidi
31
5.8k
WebSockets: Embracing the real-time Web
robhawkes
57
5k
In The Pink: A Labor of Love
frogandcode
130
21k
Large-scale JavaScript Application Architecture
addyosmani
499
110k
Keith and Marios Guide to Fast Websites
keithpitt
404
21k
Support Driven Design
roundedbygravity
86
8.4k
A Philosophy of Restraint
colly
192
14k
ピンチをチャンスに:未来をつくるプロダクトロードマップ #pmconf2020
aki_i
21
14k
How GitHub Uses GitHub to Build GitHub
holman
465
280k
Responsive Adventures: Dirty Tricks From The Dark Corners of Front-End
smashingmag
237
19k
Transcript
8IZEPO`UZPVUSZ 2VFSZ#VJMEFS 1)1$POGFSFODF'VLVPLB .BZ 4PUBSP0.63"
"CPVU.F w4PUBSP0.63" w0TBLB +BQBO w,BOTBJ1)16TFST(SPVQ4UB⒎ w1)1 w-BSBWBM wPNPPOPSH
w!PNPPO
None
2VFSZ#VJMEFS
2VFTUJPOT w%PZPVLOPX42- w.Z42-1PTUHSF42-0SBDMFPUIFST w1%0 w,OPX2VFSZ#VJMEFS w6TF2VFSZ#VJMEFS w6TF03.
/PUF w1%0 w*MMVNJOBUF%BUBCBTF w.Z42-
4BNQMF%BUBCBTF
None
5PQJDT w8IBUJTl2VFSZ#VJMEFSz w#FOFpUT w)PXUPBQQMZ2VFSZ#VJMEFSUPZPVSPXOBQQ w4PNFFYBNQMFT
8IBU`T 2VFSZ#VJMEFS
1JD DPOTUSVDUJPO
1JD DPOTUSVDUJPO
"UPPMUPCVJME42-
None
SELECT name, price FROM books WHERE name LIKE '%PHP%' ORDER
BY price DESC LIMIT 10
SELECT name, price FROM books WHERE name LIKE '%PHP%' ORDER
BY price DESC LIMIT 10
&BDIQBSUPG42- IBTB30-&
$builder->table('books') ->where('name', 'like', '%PHP%') ->orderBy('name', 'desc') ->take(10) ->select('name', 'price');
SELECT `name`, `price` FROM `books` WHERE `name` LIKE '%PHP%' ORDER
BY `price` DESC LIMIT 10
DELETE FROM `books` WHERE `name` LIKE '%PHP%' ORDER BY `price`
DESC LIMIT 10
$builder->table('books') ->where('name', 'like', '%PHP%') ->orderBy('price', 'desc') ->take(10) ->select('name', 'price');
$builder->table('books') ->where('name', 'like', '%PHP%') ->orderBy('price', 'desc') ->take(10) ->delete();
$builder->table('books') ->where('name', 'like', '%PHP%') ->orderBy('price', 'desc') ->take(10) ->select('name', 'price');
$builder->table('books') ->where('name', 'like', '%PHP%') ->orderBy('price', 'desc') ->take(10) ->select('name', ‘price') ->get();
#FOFpUT
#FOFpUT w&BTZUPFEJU w4FDVSF w&BTZUPSFBE w3FVTBCMF
&BTZUPFEJU
%FNP
4FDVSF
$name = 'PHPΤϯδχΞཆಡຊ'; $sql = " SELECT `name`, `price`
FROM `books` WHERE `name` = $name ORDER BY `price` DESC "; $sth = $pdo->prepare($sql); $sth->execute();
None
None
$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();
None
$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]);
None
$name = "t' OR 't' = 't"; $builder->table('books') ->select('name',
'price') ->where('name', '=', $name) ->orderBy('name', 'desc');
&BTZUPSFBE
SELECT `name`, `price` FROM `books` WHERE `name` LIKE
‘%PHP%' AND `price` > 2000 ORDER BY `price` DESC LIMIT 10
$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();
$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]);
$name = '%PHP%'; $price = 2000; $limit = 10;
$builder->table('books') ->select('name', 'price') ->where('name', 'like', $name) ->where('price', '>', $price) ->orderBy('name', 'desc') ->take($limit);
None
3FVTBCMF
SELECT `name`, `price` FROM `books` WHERE `price` <
2000
$cheap_books = $builder->table('books') ->where('price', '<', 2000);
$cheap_books->orderBy('name'); $cheap_books->orderBy('price'); $cheap_books->orderBy('price', 'desc'); $cheap_books->orderBy('price', ‘desc') ->take(10);
$cheap_books->orderBy('price', ‘desc') ->take(10) ->select();
$cheap_books->orderBy('price', ‘desc') ->take(10) ->delete();
$cheap_books->count(); $cheap_books->max('price'); $cheap_books->min('price'); $cheap_books->average('price'); $cheap_books->sum('price');
#FOFpUT w&BTZUPFEJU w4FDVSF w&BTZUPSFBE w3FVTBCMF
)PXUPBQQMZ 2VFSZ#VJMEFS UPZPVSPXOBQQT
{ "require": { "illuminate/database": "5.2.*" } }
require_once '/path/to/vendor/autoload.php';
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();
4PNFNPSFFYBNQMFT
+0*/
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` >= ?
$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' );
*/4&35
$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);
INSERT INTO `books`(`name`, `author`, `price`) VALUES(?, ?, ?) . .
. VS INSERT INTO `books`(`name`, `author`, `price`) VALUES(?, ?, ?),(?, ?, ?),(?, ?, ?)......
NZTRMJ T 1%0 T 2VFSZ#VJMEFS NT
61%"5&3"8
UPDATE `books` SET `name` = CONCAT('ʲ͍҆Αʂʳ', `name`) WHERE `price` <
3000
$cheap_books->update([ 'name' =>"CONCAT('ʲ͍҆Αʂʳ' , `name`)" ]);
None
$cheap_books->update([ 'name' => $builder->raw( "CONCAT('ʲ͍҆Αʂʳ' , `name`)" ) ]);
None
"OZ2VFTUJPOT