2020年5月6日
PDO方式で、dbと繋ぐサンプル：
1. Mampを立ち上げでcmdに以下の内容を入力:
cd /Applications/MAMP/Library/bin/
./mysql -u root -proot
2.繋ぐ
<?php
$dsn = "mysql:host = localhost";
$user = "root";
$password = "root";
date_default_timezone_set('Asia/Tokyo');
try{
$pdo = new PDO($dsn ,$user, $password);
echo "Connected: " . date("Y-m-d H:i:s");
}
catch(PDOException $e){
echo $e->getMessage();
}
?>
3.CREATE db
<?php
$dsn = "mysql:host = localhost";
$user = "root";
$password = "root";
date_default_timezone_set('Asia/Tokyo');
try{
$conn = new PDO($dsn ,$user, $password);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql = "CREATE DATABASE dbMay6";
$conn->exec($sql);
echo "New db created: " . date("Y-m-d H:i:s");
}
catch(PDOException $e){
echo $sql . ': ' . $e->getMessage();
}
?>
4.CREATE table
<?php
$dsn = "mysql:host=localhost;dbname=dbMay6";
$user = "root";
$password = "root";
date_default_timezone_set('Asia/Tokyo');
try{
$conn = new PDO($dsn ,$user, $password);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql =<<<EOL
CREATE TABLE Users (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
email VARCHAR(50),
reg_date TIMESTAMP)
EOL;
$conn->exec($sql);
echo "New table created: " . date("Y-m-d H:i:s");
}
catch(PDOException $e){
echo $sql . ': ' . $e->getMessage();
}
?>
5.INSERT new data
<?php
$dsn = "mysql:host=localhost;dbname=dbMay6";
$user = "root";
$password = "root";
date_default_timezone_set('Asia/Tokyo');
try{
$conn = new PDO($dsn ,$user, $password);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql =<<<EOL
INSERT INTO Users (firstname, lastname, email)
VALUES ('John', 'Doe', 'john@example.com')
EOL;
$conn->exec($sql);
echo "New data inserted: " . date("Y-m-d H:i:s");
}
catch(PDOException $e){
echo $sql . ': ' . $e->getMessage();
}
?>
6.INSERT new data(複数回)
<?php
$dsn = "mysql:host=localhost;dbname=dbMay6";
$user = "root";
$password = "root";
date_default_timezone_set('Asia/Tokyo');
try{
$conn = new PDO($dsn ,$user, $password);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$conn->beginTransaction();
$conn->exec("INSERT INTO Users (firstname, lastname, email)
VALUES ('John', 'Doe', 'john@example.com')");
$conn->exec("INSERT INTO Users (firstname, lastname, email)
VALUES ('Mary', 'Moe', 'mary@example.com')");
$conn->exec("INSERT INTO Users (firstname, lastname, email)
VALUES ('Julie', 'Dooley', 'julie@example.com')");
$conn->commit();
echo "Mutiplied data inserted: " . date("Y-m-d H:i:s");
}
catch(PDOException $e){
$conn->rollback();
echo $sql . ': ' . $e->getMessage();
}
?>
7.INSERT new data(Prepared、複数回)
<?php
$dsn = "mysql:host=localhost;dbname=dbMay6";
$user = "root";
$password = "root";
date_default_timezone_set('Asia/Tokyo');
try{
$conn = new PDO($dsn ,$user, $password);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$stmt = $conn->prepare("INSERT INTO Users (firstname, lastname, email)
VALUES (:firstname, :lastname, :email)");
$stmt->bindParam(':firstname', $firstname);
$stmt->bindParam(':lastname', $lastname);
$stmt->bindParam(':email', $email);
$firstname = "John";
$lastname = "Doe";
$email = "john@example.com";
$stmt->execute();
$firstname = "Mary";
$lastname = "Moe";
$email = "mary@example.com";
$stmt->execute();
$firstname = "Julie";
$lastname = "Dooley";
$email = "julie@example.com";
$stmt->execute();
echo "Prepared data inserted: " . date("Y-m-d H:i:s");
}
catch(PDOException $e){
echo $sql . ': ' . $e->getMessage();
}
?>
8.SELECT * FROM _TABLE
fetchAll(PDO::FETCH_ASSOC): ↓
<?php
$dsn = "mysql:host=localhost;dbname=dbMay6";
$user = "root";
$password = "root";
try{
$pdo = new PDO($dsn, $user, $password);
$sql = "SELECT * FROM users";
$stm = $pdo->query($sql);
$data = $stm->fetchAll(PDO::FETCH_ASSOC);
foreach($data as $row){
echo $row['id'] . $row['email'] . "<br>";
}
}
catch(PDOException $e){
echo $e->getMessage();
}
?>
fetchAll(PDO::FETCH_NUM): ↓
<?php
$dsn = "mysql:host=localhost;dbname=dbMay6";
$user = "root";
$password = "root";
try{
$pdo = new PDO($dsn, $user, $password);
$sql = "SELECT * FROM users";
$stm = $pdo->query($sql);
$data = $stm->fetchAll(PDO::FETCH_NUM);
foreach($data as $row){
echo $row[0] . $row[count($row)-1] . "<br>";
}
}
catch(PDOException $e){
echo $e->getMessage();
}
?>
8.5 ダミーサンプル
<?php
$val = "";
$selVal = $_POST['select'];
if($_SERVER["REQUEST_METHOD"]=="POST"){
if(!empty($selVal)&&($selVal!="")){
$val = text_input($selVal);
}
}
function text_input($text){
$text = trim($text);
$text = stripslashes($text);
$text = htmlspecialchars($text);
return $text;
}
?>
<form action = "<?php echo htmlspecialchars($_SERVER["PHP_SELF"])?>" method = "POST">
<select name = "select">
<option value = "">please check</option>
<option value = "1">1</option>
<option value = "2">2</option>
<option value = "3">3</option>
<option value = "4">4</option>
<option value = "5">5</option>
<option value = "6">6</option>
<option value = "7">7</option>
</select>
<input type = "submit" value = "submit">
</form>
<?php
//echo $val;
$dsn = "mysql:host=localhost;dbname=dbMay6";
$user = "root";
$password = "root";
$pdo = new PDO($dsn, $user, $password);
$sql = "SELECT * FROM users WHERE id = ?";
$stm = $pdo->prepare($sql);
$stm->bindValue(1 , $val);
$stm->execute();
$data = $stm->fetch(PDO::FETCH_ASSOC);
echo $data['email'];
?>
9.DELETE
<?php
$dsn = "mysql:host=localhost;dbname=dbMay6";
$user = "root";
$password = "root";
try{
$dbh = new PDO($dsn, $user, $password);
$sql = "DELETE FROM users WHERE id = ?";
$stmt = $dbh->prepare($sql);
$id = 2;
$stmt->bindValue(1, $id);
$stmt->execute();
echo "done!";
}
catch(PDOException $e){
throw $e;
}
?>
[ref1] https://geek-docs.com/php/php-tutorial/php-pdo.html#PHP_PDO-4
[ref2] https://www.runoob.com/php/php-mysql-update.html