mysql pdo php 基礎

データベースの作成

mysql -u root -p

データベース名の確認

mysql> show databases;

DBの作成

mysql> create database sample_db;

データベースに接続

<?php
try {
$pdo = new PDO('mysql:host=ホスト名;dbname=DB名;charset=utf8','ユーザー名','パスワード',
array(PDO::ATTR_EMULATE_PREPARES => false));
} catch (PDOException $e) {
 exit('データベース接続失敗。'.$e->getMessage());
}
?>

データを呼び出し

<?php
$stmt = $pdo->query("SELECT * FROM テーブル名 ORDER BY no ASC");
while($row = $stmt -> fetch(PDO::FETCH_ASSOC)) {
 $ttitle = $row["title"];
 $tr = $row["r"];
 $tk = $row["k"];
 $tt = $row["t"];
 $tm = $row["m"];
echo<<<EOF

ヒアドキュメント内の表示部分

EOF;
}
?>

INSERT

$stmt = $pdo -> prepare("INSERT INTO テーブル名 (name, value) VALUES (:name, :value)");
$stmt->bindParam(':name', $name, PDO::PARAM_STR);
$stmt->bindValue(':value', 1, PDO::PARAM_INT);

$name = 'one';
$stmt->execute();

UPDATE

<?php
$sql = 'update テーブル名 set name =:name where id = :value';
$stmt = $pdo -> prepare($sql);
$stmt->bindParam(':name', $name, PDO::PARAM_STR);
$stmt->bindValue(':value', 1, PDO::PARAM_INT);
$stmt->execute();
?>

DELETE

テーブルの値を削除する。

<?php
$sql = 'DELETE FROM テーブル名 where id = :delete_id';
$stmt = $pdo -> prepare($sql);
$stmt -> bindParam(':delete_id', $value, PDO::PARAM_INT);
$stmt -> execute();
?>

$stmt = $pdo -> query("SELECT * FROM テーブル名");
$count = $stmt -> rowCount();

$stmt = $pdo -> prepare("SELECT SUM(a1) as a1 FROM テーブル名 WHERE y=:y");
$stmt -> bindParam(':y', $y, PDO::PARAM_STR);
$stmt -> execute();
if($row = $stmt -> fetch()){
$kei = $row['a1'];
}

$stmt = $pdo -> prepare("SELECT SUM(a1 + a2 + a3) as goukei FROM テーブル名 WHERE y=:y");
$stmt -> bindParam(':y', $y, PDO::PARAM_STR);
$stmt -> execute();
if($row = $stmt -> fetch()){
$kei = $row['goukei'];
}

<?php 
$sql = "CREATE TABLE IF NOT EXISTS `テーブル名`"
."("
. "`dd` INT auto_increment primary key,"
. "`y` INT,"
. "`m` INT,"
. "`d` INT,"
. "`youbi` INT,"
. "`yokin` INT,"
. "`a1` INT,"
. "`a2` INT,"
. "`a3` INT,"
. "`a4` INT,"
. "`a5` INT,"
. "`i_date` DATETIME"
.");";
$stmt = $pdo -> prepare($sql);
$stmt -> execute();
?>

$sql = "DROP TABLE IF EXISTS テーブル名";
$pdo -> exec($sql);