参照

PHPとMySQLのツボとコツが絶対にわかる本

DB

サンプル

db.php


<?php
class DB{
  private  $USER = "root";
  private  $PW   = "******";
  private  $dns  = "mysql:dbname=salesmanagement;host=localhost;charset=utf8";

  private function Connectdb(){
    try{
      $pdo = new PDO($this->dns,$this->USER,$this->PW);
      return $pdo;
    }catch(Exception $e){
      return false;
    }
  }

  public  function executeSQL($sql,$array){
    try{
      if(!$pdo = $this->Connectdb())return false;
      $stmt = $pdo->prepare($sql);
      $stmt->execute($array);
      return $stmt;
    }catch(Exception $e){
      return false;
    }
  }
}
?>

dbcrud.php


<?php
require_once('db.php');
class dbcrud extends DB{
  //goodsテーブルのCRUD担当
  public function SelectGoodsAll(){
    $sql = "SELECT * FROM goods";
    $res = parent::executeSQL($sql, null);
    $data = "<table class='recordlist' id='goodsTable'>";
    $data .= "<tr><th>カラム1</th><th>カラム2</th><th>カラム3</th><th></th><th></th></tr>\n";
    foreach($rows = $res->fetchAll(PDO::FETCH_NUM) as $row){
      $data .= "<tr>";
      for($i=0;$i<count($row);$i++){
        $data .= "<td>{$row[$i]}</td>";
      }
      //更新ボタンのコード
      $data .= <<<eof
      <td><form method='post' action=''>
      <input type='hidden' name='id' value='{$row[0]}'>
      <input type='submit' name='update' value='更新'>
      </form></td>
eof;
      //削除ボタンのコード
      $data .= <<<eof
      <td><form method='post' action=''>
      <input type='hidden' name='id' id='Deleteid' value='{$row[0]}'>
      <input type='submit' name='delete' id='delete' value='削除'
       onClick='return CheckDelete()'>
      </form></td>
eof;
      $data .= "</tr>\n";
    }
    $data .= "</table>\n";
    return $data;
  }

  public function InsertGoods(){
    $sql = "INSERT INTO goods VALUES(?,?,?)";
    $array = array($_POST['GoodsID'],$_POST['GoodsName'],$_POST['Price']);
    parent::executeSQL($sql, $array);
  }

  public function UpdateGoods(){
    $sql = "UPDATE goods SET GoodsName=?, Price=? WHERE GoodsID=?";
    //array関数の引数の順番に注意する
    $array = array($_POST['GoodsName'],$_POST['Price'],$_POST['GoodsID']);
    parent::executeSQL($sql, $array);
  }

  public function GoodsNameForUpdate($GoodsID){
    return $this->FieldValueForUpdate($GoodsID, "GoodsName");
  }

  public function PriceForUpdate($GoodsID){
    return $this->FieldValueForUpdate($GoodsID, "Price");
  }

  private function FieldValueForUpdate($GoodsID, $field){
    //private関数　上の2つの関数で使用している
    $sql = "SELECT {$field} FROM goods WHERE GoodsID=?";
    $array = array($GoodsID);
    $res = parent::executeSQL($sql, $array);
    $rows = $res->fetch(PDO::FETCH_NUM);
    return $rows[0];
  }
  public function DeleteGoods($GoodsID){
    $sql = "DELETE FROM goods WHERE GoodsID=?";
    $array = array($GoodsID);
    parent::executeSQL($sql, $array);
  }
}
?>

index.php

<?php
require_once('dbcrud.php');
//変数の初期値入力

$dbGoodsId = '更新のIDが表示されます';
$dbGoodsName = '更新の値が表示されます';
$Price = '更新の値が表示されます';

$dbGoods = new dbcrud();
//更新処理
if(isset($_POST['submitUpdate'])){
  $dbGoods->UpdateGoods();
}
//更新用フォーム要素の表示
if(isset($_POST['update'])){
  //更新対象の値を取得
  $dbGoodsId   = $_POST['id'];
  $dbGoodsName = $dbGoods->GoodsNameForUpdate($_POST['id']);
  $Price     = $dbGoods->PriceForUpdate($_POST['id']);
}
//削除処理
if(isset($_POST['delete'])){
  $dbGoods->DeleteGoods($_POST['id']);
}
//新規登録処理
if(isset($_POST['submitEntry'])){
  $dbGoods->InsertGoods();
}
//テーブルデータの一覧表示
$data = $dbGoods->SelectGoodsAll();
?>
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8" />
<title>title</title>
<script type="text/javascript">
function CheckDelete(){
    return confirm("削除してもよろしいですか？");
}
</script>
</head>
<body>
<h1>CRUD</h1>
<h3>新規登録</h3>
<form action="" method="post">
<label>カラム1<input type='text' name='GoodsID' size="30" required></label>
<label>カラム2<input type='text' name='GoodsName' size="30" required></label>
<label>カラム3<input type='text' name='Price' size="30" required></label>
<input type='submit' name='submitEntry' value=' 　新規登録　 '>
</form>

<div>
<?php echo $data;?>
</div>

<h2>update</h2>

<form action="" method="post">
<p>GoodsID: <?php echo $dbGoodsId;?></p>
<input type="hidden" name="GoodsID" value="<?php echo $dbGoodsId;?>" />
<label>カラム2:<input type='text' name='GoodsName'
 size="30" value="<?php echo $dbGoodsName;?>" required></label>
<label>カラム3:<input type='text' name='Price'
 size="30" value="<?php echo $Price;?>" required></label>
<input type='submit' name='submitUpdate' value=' 　更新　 '>
</form>

</body>
</html>

「CRUD」のサンプル(レガシー)

参照

DB

サンプル