[PHP]商品情報を登録する商品マスタの作成その2 編集削除編

edit_view.php
<form action="" method="post">
<p>メーカー名</p>
<p><input type="text" name="maker" value="<?php echo $list['maker']; ?>"></p>
                        
<p>商品コード</p>
<p><input type="text" name="code" value="<?php echo $list['code']; ?>"></p>
                       
<p>商品名</p>
<p><input type="text" name="item" value="<?php echo $list['item'];?>"></p>
                        
<p>発注ロット</p>
<p><input type="text" name="quantity_per_rot" value="<?php echo $list['quantity_per_rot'];?>"></p>
                        
<p>基本在庫数</p>
<p><input type="text" name="basic_quantity" value="<?php echo $list['basic_quantity'];?>"></p>
                      
<p>金額</p>
<p><input type="text" name="price" value="<?php echo $list['price'];?>"></p>

<p>容量</p>
<p><input type="text" name="capacity" value="<?php echo $list['capacity'];?>"></p>
                       
<p>1杯使用量</p>
<p><input type="text" name="usage_amount" value="<?php echo $list['usage_amount'];?>"></p>
                       
<p>メール宛先</p>
<p><input type="text" name="mail" value="<?php echo $list['mail'];?>"></p>

<input type="submit" name="submit" value="保存" class="ma_top_2">
</form>

edit.php
//編集ボタンからリンクしたデータ
if(isset($_GET['id'])){
    $id = $_GET['id'];
    $sql = "SELECT * FROM rot WHERE id = :id";
    $stmt = $dbh->prepare($sql);
    $stmt->bindParam(':id',$id,PDO::PARAM_INT);
    $stmt->execute();
    $list = $stmt->fetch(PDO::FETCH_ASSOC);
}

//各店舗に商品マスタと同時に更新するために用意する$shop
function getShops($dbh) {
    $shops = [];
    try {
        $sql = "SELECT * FROM shops"; // `shops`は店舗情報が格納されているテーブル名です。
        foreach ($dbh->query($sql) as $row) {
            $shops[] = [
                'shop_id' => $row['shop_id'],
            ];
        }
    } catch (PDOException $e) {
        echo "Error: " . $e->getMessage();
    }
    return $shops;
}

//更新
if(isset($_POST['submit'])){

    $code = isset($_POST['code']) ? $_POST['code'] : null;
    $maker = isset($_POST['maker']) ? $_POST['maker'] : null;
    $item = isset($_POST['item']) ? $_POST['item'] : null;
    $mail = isset($_POST['mail']) ? $_POST['mail'] : null;
    $basic_quantity = isset($_POST['basic_quantity']) ? $_POST['basic_quantity'] : null;
    $usage_amount = isset($_POST['usage_amount']) ? $_POST['usage_amount'] : null;
    $price = isset($_POST['price']) ? $_POST['price'] : null;
    $capacity = isset($_POST['capacity']) ? $_POST['capacity'] : null;
    $quantity_per_rot = isset($_POST['quantity_per_rot']) ? $_POST['quantity_per_rot'] : null;


    try{
            
        $dbh->beginTransaction();


    $sql = "UPDATE rot SET code = :code,maker = :maker,item=:item,mail=:mail,basic_quantity = :basic_quantity,usage_amount=:usage_amount,price=:price,capacity=:capacity,quantity_per_rot=:quantity_per_rot WHERE id=:id";
    $stmt = $dbh->prepare($sql);
    $stmt->bindParam(':code', $code, PDO::PARAM_INT);
    $stmt->bindParam(':maker', $maker, PDO::PARAM_STR);
    $stmt->bindParam(':item', $item, PDO::PARAM_STR);
    $stmt->bindParam(':mail', $mail, PDO::PARAM_STR);
    $stmt->bindParam(':basic_quantity', $basic_quantity, PDO::PARAM_INT);
    $stmt->bindParam(':usage_amount', $usage_amount, PDO::PARAM_STR);
    $stmt->bindParam(':price', $price, PDO::PARAM_INT);
    $stmt->bindParam(':capacity', $capacity, PDO::PARAM_STR);
    $stmt->bindParam(':quantity_per_rot', $quantity_per_rot, PDO::PARAM_INT);
    $stmt->bindParam(':id', $id, PDO::PARAM_INT);

    $stmt->execute();

    //各店舗の在庫テーブルも更新
        //関数は30行目参照
        $shops = getShops($dbh); // 店舗情報を取得する関数
        foreach($shops as $shop) {
        $s_sql = "UPDATE stock_input SET maker = :maker, item = :item, price = :price, basic_quantity = :basic_quantity WHERE shop_id = :shop_id AND code = :code";    
        $s_stmt = $dbh->prepare($s_sql);
        $s_stmt->bindParam(':code', $code);
        $s_stmt->bindParam(':maker', $maker);
        $s_stmt->bindParam(':item', $item);
        $s_stmt->bindParam(':price', $price);
        $s_stmt->bindParam(':basic_quantity', $basic_quantity);
        $s_stmt->bindParam(':shop_id', $shop['shop_id']);

        $s_stmt->execute();
        }


    $dbh->commit();

    $_SESSION['message'] = "編集が成功しました。";



    header('Location:index.php');
    exit();
    } catch (PDOException $e) {
    $dbh->rollback(); // トランザクションロールバック
    echo "Error: " . $e->getMessage();
    
    }
}

index_view.php
<form action="delete.php" method="POST">
<input type="hidden" name="id" value="<?php echo $list['id'];?>">
<button type="submit">削除</button>
</form>

delete.php

<?php
session_start();
include "../config.php";

$dbh = db_open();

//各店舗に商品マスタと同時に削除するために用意する$shop
function getShops($dbh) {
    $shops = [];
    try {
        $sql = "SELECT * FROM shop"; // `shops`は店舗情報が格納されているテーブル名です。
        foreach ($dbh->query($sql) as $row) {
            $shops[] = [
                'shop_id' => $row['shop_id'],
            ];
        }
    } catch (PDOException $e) {
        echo "Error: " . $e->getMessage();
    }
    return $shops;
}

if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    $id = $_POST['id'];
    $code = null;


    try{
            
    $dbh->beginTransaction();

    //最初に商品マスタ（master　テーブル）から商品コードを取得
    $sql = "SELECT code FROM master WHERE id = :id";
    $c_stmt = $dbh->prepare($sql);
    $c_stmt->bindParam(':id',$id,PDO::PARAM_INT);
    $c_stmt->execute();
    $result = $c_stmt->fetch(PDO::FETCH_ASSOC);
    $code = $result['code'];


    //商品マスタから商品を削除
    $sql = "DELETE FROM master WHERE id = :id";
    $stmt = $dbh->prepare($sql);
    $stmt->bindParam(':id', $id, PDO::PARAM_INT);
    $stmt->execute();

    //各店舗から該当する商品を削除
    $shops = getShops($dbh); // 店舗情報を取得する関数
    foreach($shops as $shop) {
    $s_sql = "DELETE FROM shop_stock WHERE shop_id = :shop_id AND code = :code";    
    $s_stmt = $dbh->prepare($s_sql);
    $s_stmt->bindParam(':shop_id', $shop['shop_id'], PDO::PARAM_INT);
    $s_stmt->bindParam(':code', $code, PDO::PARAM_INT); 
    $s_stmt->execute();
    }


    $dbh->commit();

    header("Location: index.php");
    exit;
    } catch (PDOException $e) {
    $dbh->rollback(); // トランザクションロールバック
    echo "Error: " . $e->getMessage();
    
    }
}
?>