PHPデータベース入門 ドットインストール
全体的に理解が浅いので後でもう一回やる
exec() 結果を返さない、安全なSQL
query() 結果を返す、安全、何回も実行されないSQL
prepare() 結果を返す、安全対策が必要、複数回実行されるSQL
おまじない?
$db = new PDO(PDO_DSN, DB_USERNAME, DB_PASSWORD);
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
exec()
$db->exec("insert into users (name,score) values ('taguchi', 55)");
echo "user added!";
prepare()
$stmt = $db->prepare("insert into users(name, score) values (?,?)");
$stmt->execute(['taguchi', 44]);
echo "inserted:" . $db->lastInsertId();
bindValue() 値をbind
$stmt = $db->prepare("insert into users(name, score) values(?, ?)");
$name = 'taguchi';
$stmt->bindValue(1, $name, PDO::PARAM_STR);
$score = 23;
$stmt->bindValue(2, $score, PDO::PARAM_INT);
$stmt->execute();
bindParam() 変数への参照をbind
$stmt->bindParam(2, $score, PDO::PARAM_INT);
$score = 52;
$stmt->execute();
query()
$stmt = $db->query("select * from users");
$users = $stmt->fetchAll(PDO::FETCH_ASSOC);
foreach ($users as $user){
var_dump($user);
}
echo $stmt->rowCount() . " records found.";
prepare()
$stmt = $db->prepare("select score from users where score > ?");
$stmt->execute([60]);
like句
$stmt = $db->prepare("select score from users where name like ?");
$stmt->execute(['%t%']);
型指定するときはbindValue
$stmt = $db->prepare("select score from users order by score desc limit ?");
$stmt->bindValue(1, 1, PDO::PARAM_INT);
class User{
//FETCH_CLASSではカラムをクラスのpublicプロパティに自動でセットして くれる
// public $id;
// public $name;
// public $score;
public function show(){
echo "$this->name ($this->score)";
}
}
$stmt = $db->query("select * from users");
$users = $stmt->fetchAll(PDO::FETCH_CLASS, 'User');
更新
$stmt = $db->prepare("update users set score = :score where name = :name");
$stmt->execute([
':score' => 100,
':name' => 'taguchi'
])
echo 'row updated: ' . $stmt->rowCount();
$db->beginTransaction();
$db->exec("update users set score = score - 10 where name = 'taguchi'");
$db->exec("update users set score = score + 10 where name = 'fkoji'");
$db->commit();
catch(PDOException $e){
$db->rollback();
}