PDO 超入門

2020年5月6日
-
####PDO方式で、dbと繋ぐサンプル：
---

######1. Mampを立ち上げでcmdに以下の内容を入力:

```bash
cd /Applications/MAMP/Library/bin/
./mysql -u root -proot 
```
---

######2.繋ぐ

```php
<?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
<?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
<?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
<?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
<?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
<?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
<?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
<?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
<?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
<?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