PHPでMySQLからデータを抽出する方法


まだPHPをやり始めて半年くらいなので、色々つまづきました。
DBからどうやってデータを引っ張って、WEBに表示するのか？

どうやってSQLを実行するのか？
どうやってその結果をWEBへ流すのか？

オブジェクト指向とは？
クラスとは？継承って？

どんどんわからないことが出てきました。
10年前にかじったJavaを思い出しながら、ひたすらググりましたよ。

## <おおまかな流れ>
1.まずおまじない的にDBへ接続するための準備クラス。

```php:pdo_config.php
<?php
// PDO setting class
class PdoConfig {
  public $username;
  public $password;
  public $dsn;
  public $driver_options;
  public $DateTime;
  public $sql;
  public $preSQL;
  public $pdo;
  public $kiriwake;

  public function __construct($kiriwake){
    switch($kiriwake){
      case 1:
      $this->dsn = 'mysql:dbname=koushityotatsu;host=127.0.0.1;charset=utf8mb4';
      break;
      case 2:
      $this->dsn = 'mysql:dbname=konpo;host=127.0.0.1;charset=utf8mb4';
      break;
      case 3:
      $this->dsn = 'mysql:dbname=management_books;host=127.0.0.1;charset=utf8mb4';
      break;
      case 4:
      $this->dsn = 'mysql:dbname=konpo;host=127.0.0.1;charset=utf8mb4';
      break;
      default:
      $this->dsn = 'mysql:dbname=koushityotatsu;host=127.0.0.1;charset=utf8mb4';
    }
    $this->username = 'yyyy';
    $this->password = 'xxxx';

  }
  //クエリ実行
  protected function query($sql){
    return $this->preSQL = $this->pdo->query($sql);
    
  }
  //SQL準備
  protected function prepare($sql){
    return $this->preSQL = $this->pdo->prepare($sql);
  }
  // execute
  protected function exec($sql){
    $this->pdo->exec($sql);   
  }

  // 準備したprepareに入っているSQL文を実行
  protected function execute(){
    $this->pdo->execute($this->preSQL);
  }
  //日付GET
  protected function getDate(){
    return $this->DateTime = date("Y/m/d H:i:s");
  }
}
```
---

2.実際にDBへ接続するクラス
ここで継承できることのメリットを感じた。
VBAだとオブジェクト指向ではなく、カプセル化しかできないので。
driver_optionを継承元に書くと、動作しなかった。なにかいい方法はないのかな？

```php:Data.php
<?php
require('D:/home/php/pdo_config.php');

class koushiSearch extends PdoConfig {
  protected $searchSQL;
  protected $andCnt;
  public $array = [];
  public $sql;
  public $insertSQL;
  public $updateSQL;
  public $tmpSQL;

  // CSVファイルから登録
　//driver_optionを継承元に書くと、動作しなかった。なにかいい方法はないのかな？
  public function execMOQRegistSQL(){
    $driver_options=[PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,];
      try {
        $pdo = new PDO(
          $this->dsn,
          $this->username,
          $this->password,
          $this->driver_options
        );
        $searchSQL = 'TRUNCATE tmp_konpo;';
        $selectsql = $pdo->prepare($searchSQL);
        $selectsql->execute();

        // SQLファイルを読み込み
        foreach(glob('D:/home/moq/sql/{*.sql}',GLOB_BRACE) as $file){
          if(strpos($file,'insert')){
            $this->insertSQL = file_get_contents($file);
            // var_dump($this->insertSQL);
          }elseif(strpos($file,'update')){
            $this->updateSQL = file_get_contents($file);
            // var_dump($this->updateSQL);
          }else{
            $this->tmpSQL = file_get_contents($file);
            // var_dump($this->tmpSQL);
          }
        }
        // トランザクション処理
        $i=1;
        for($i=1;$i<=3;$i++){
          switch($i){
            case 1:
              $selectsql = $pdo->query($this->tmpSQL);
              echo 'save1';
              break;
            case 2:
            try{
                $pdo->beginTransaction();
                $selectsql = $pdo->prepare($this->updateSQL);
                $selectsql->execute();
                // var_dump($selectsql);
                $pdo->commit();
                echo 'save'. $i;
              }catch(PDOException $e){
                $pdo->rollback();
                throw $e;
              }
          
              break;
            case 3:
            try{
                // $pdo->beginTransaction();
                $selectsql = $pdo->prepare($this->insertSQL);
                $$selectsql->execute();
                $pdo->commit();
                echo 'save';
              }catch(PDOException $e){
                $pdo->rollback();
                throw $e;
              }
              break;
            default:
              break;

          }
        }

        $searchSQL = 'SELECT max(regist_date) FROM mt_konpo;';
        $selectsql = $pdo->prepare($searchSQL);
        $selectsql->execute();
        $rows = $selectsql->fetchall(PDO::FETCH_ASSOC);
        // get max regist date
        foreach($rows as $val){
          $maxDate = $val['max(regist_date)'];
        }
        $searchSQL = 'SELECT * FROM mt_konpo WHERE regist_date = '."'".$maxDate."'".' limit 100;';
        $selectsql = $pdo->prepare($searchSQL);
        $selectsql->execute();
        $rows = $selectsql->fetchall(PDO::FETCH_ASSOC);
        // make table data
        foreach($rows as $val){
          print "<tr>";
          print "<td>".$val['g_hinban']."</td>";
          print "<td>".$val['maker_hinban']."</td>";
          print "<td>".$val['maker_name']."</td>";
          print "<td>".$val['syosya']."</td>";
          print "<td>".$val['konpo_tani']."</td>";
          print "<td>".$val['moq']."</td>";
          print "<td>".$val['kyokyu_code']."</td>";
          print "<td>".$val['nisugata']."</td>";
          print "<td>".$val['regist_date']."</td>";
          print "<td>".$val['regist_name']."</td>";
          print "</tr>";
        }
      
      } catch (PDOException $e) {
        header('Content-Type: text/plain; charset=UTF-8', true, 500);
        exit($e->getMessage());
      }
  }
  public function execMOQSearchSQL(){
    $driver_options=[PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,];
      try {
        $pdo = new PDO(
          $this->dsn,
          $this->username,
          $this->password,
          $this->driver_options
        );

        // テキストの値をGET
        $this->andCnt = 0;
        if (!empty($_POST['ghinbanSearchbox'])){
          $this->array[] = 'g_hinban like '."'".$_POST['ghinbanSearchbox']."'";
          $this->andCnt++;
        }
        if (!empty($_POST['makerhinbanSearchbox'])){
          $this->array[] = 'maker_hinban like '."'".$_POST['makerhinbanSearchbox']."'";
          $this->andCnt++;
        }
        
        if (!empty($_POST['makernameSearchbox'])) {
          $this->array[] = 'maker_name like '."'".$_POST['makernameSearchbox']."'";
          $this->andCnt++;
        }
        if (!empty($_POST['syosyaSearchbox'])) {
          $this->array[] = 'syosya like '."'".$_POST['syosyaSearchbox']."'";
          $this->andCnt++;
        }
        if (!empty($_POST['konpotaniSearchbox'])) {
          $this->array[] = 'konpo_tani = '."'".$_POST['konpotaniSearchbox']."'";
          $this->andCnt++;
        }
        if (!empty($_POST['moqSearchbox'])) {
          $this->array[] = ' moq = '."'".$_POST['moqSearchbox']."'";
          $this->andCnt++;
        }
        if (!empty($_POST['kyokyucodeSearchbox'])) {
          $this->array[] = ' kyokyu_code like '."'".$_POST['kyokyucodeSearchbox']."'";
          $this->andCnt++;
        }
        if (!empty($_POST['nisugataSearchbox'])) {
          $this->array[] = ' nisugata like '."'".$_POST['nisugataSearchbox']."'";
          $this->andCnt++;
        }
        if (!empty($_POST['registdateSearchbox'])) {
          $this->array[] = ' regist_date like '."'".$_POST['registdateSearchbox']."'";
          $this->andCnt++;
        }
        if (!empty($_POST['registnameSearchbox'])) {
          $this->array[] = ' regist_name like '."'".$_POST['registnameSearchbox']."'";
          $this->andCnt++;
        }

        $i;
        for($i=0;$i<=$this->andCnt-1;$i++){
          if ($i==0){
            $this->sql = ' WHERE '.$this->array[$i];
          }else{
            $this->sql .= ' AND '.$this->array[$i];
          }
        }
        $searchSQL = 'SELECT distinct * FROM mt_konpo'.$this->sql.' limit 30000 ;';
        $selectsql = $pdo->prepare($searchSQL);
        $selectsql->execute();
        $rows = $selectsql->fetchall(PDO::FETCH_ASSOC);
        // make table data
        foreach($rows as $val){
        print "<tr>";
        print "<td>".$val['g_hinban']."</td>";
        print "<td>".$val['maker_hinban']."</td>";
        print "<td>".$val['maker_name']."</td>";
        print "<td>".$val['syosya']."</td>";
        print "<td>".$val['konpo_tani']."</td>";
        print "<td>".$val['moq']."</td>";
        print "<td>".$val['kyokyu_code']."</td>";
        print "<td>".$val['nisugata']."</td>";
        print "<td>".$val['regist_date']."</td>";
        print "<td>".$val['regist_name']."</td>";
        print "</tr>";
      }

      } catch (PDOException $e) {
        header('Content-Type: text/plain; charset=UTF-8', true, 500);       
        exit($e->getMessage());
      }
      
  }
  public function execSearchSQL(){
    $driver_options=[PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,];
   
    $pdo = new PDO(
      $this->dsn,
      $this->username,
      $this->password,
      $this->driver_options
    );
      try {
        // プルダウン、テキストの値をGET
        $this->andCnt = 0;
        if (empty($_POST['huyo'])){
          $huyo = "not_need <> '*'";
          $this->array[] = $huyo;
          $this->andCnt++;
        }else{
          $this->array[] = 'not_need = '."'".$_POST['huyo']."'";
          $this->andCnt++;
        }
        if (empty($_POST['genre'])){
          $genre = "";
        }else{
          $this->array[] = 'genre = '."'".$_POST['genre']."'";
          $this->andCnt++;
        }
        if (empty($_POST['headerSearchbox'])) {
          $header = "";
        } else {
          $this->array[] = 'header like '."'".$_POST['headerSearchbox']."'";
          $this->andCnt++;
        }
        if (empty($_POST['venderSearchbox'])) {
          $vender = "";
        } else {
          $this->array[] = 'vender like '."'".$_POST['venderSearchbox']."'";
          $this->andCnt++;
        }
        if (empty($_post['kisyu'])) {
          $modelname = "";
        } else {
          $this->array[] = 'kisyu = '."'".$_POST['kisyu']."'";
          $this->andCnt++;
        }
        if (empty($_POST['kensyust'])) {
          $kensyust = "";
        } else {
          $this->array[] = ' kensyu_st ='."'".$_POST['kensyust']."'";
          $this->andCnt++;
        }

        $i;
        for($i=0;$i<=$this->andCnt-1;$i++){
          if ($i==0){
            $this->sql = ' WHERE '.$this->array[$i];
          }else{
            $this->sql .= ' AND '.$this->array[$i];
          }
        }
        

        $searchSQL = 'SELECT distinct * FROM mrokeisan '. $this->sql.';';
        $selectsql = $pdo->prepare($searchSQL);
        $selectsql->execute();
        $rows = $selectsql->fetchall(PDO::FETCH_ASSOC);
        // make table data
        foreach($rows as $val){
        print "<tr>";
        print "<td>".$val['id']."</td>";
        print "<td>".$val['not_need']."</td>";
        print "<td>".$val['header']."</td>";
        print "<td>".$val['vender']."</td>";
        print "<td>".$val['kisyu']."</td>";
        print "<td>".$val['genre']."</td>";
        print "<td>".$val['part_name']."</td>";
        print "<td>".$val['part_num']."</td>";
        print "<td>".$val['kensyu_st']."</td>";
        print "<td>".$val['gcode_bar']."</td>";
        print "<td>".$val['juryobi']."</td>";
        print "<td>".$val['juryosuryo']."</td>";
        print "<td>".$val['total']."</td>";
        print "<td>".$val['bikou']."</td>";
        print "<td>".$val['awb_1']."</td>";
        print "<td>".$val['awb_2']."</td>";
        print "<td>".$val['awb_3']."</td>";
        print "<td>".$val['awb_4']."</td>";
        print "<td>".$val['awb_5']."</td>";
        print "<td>".$val['piece']."</td>";
        print "<td>".$val['weight']."</td>";
        print "<td>".$val['kobai_irai_num'].'-'.$val['kobai_irai_mei_num']."</td>";
        print "<td>".$val['mitsu_irai_num']."</td>";
        print "<td>".$val['hatyu_num'].'-'.$val['hatyu_mei_num']."</td>";
        print "<td>".$val['hatyu_tanka']."</td>";
        print "<td>".$val['kibounyusyubi']."</td>";
        print "</tr>";
      }

      $pdo = null;
      } catch (PDOException $e) {
        
        header('Content-Type: text/plain; charset=UTF-8', true, 500);
        // echo "failed connection";
        exit($e->getMessage());
      }
      
  }

  public function searchSelect(){
    $driver_options=[PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,];
    try {
      $pdo = new PDO(
        $this->dsn,
        $this->username,
        $this->password,
        $this->driver_options
      );
      // 不要欄 nameを同じにすればどっちかの選択になる
      print '<label>不要：</label><input type="radio" name="huyo" value="" checked="checked">OFF ';
      print '<label></label><input type="radio" name="huyo" value="*">ON ';

      print '<p class="log_list_genre">';
      print '<select name="genre" class="round">';
      print '<option value="" hidden class="genre">ジャンル</option>';
      // $i = 1;
      print '<option value="denki">denki</option>';
      print '<option value="kiko">kiko</option>';
      print '<option value="panel">panel</option>';
      foreach($row as $val){
        // print '<option value='.$val['genre'].'>';
        // print $val['genre'];
        // print '</option>';
        // ++$i;
      }
      print "</select>";
      print "</p></br>";
      
      // pulldown set
      print '<p class="log_list_hinmoku">';
      print '<label>手配項目：</label><input type="search" name="headerSearchbox" class="headerSearchbox">';
      print "</p>";

      print '<p class="log_list_vender">';
      print '<label>Vender：</label><input type="search" name="venderSearchbox" class="venderSearchbox">';
      print "</p></br>";

      $selectsql = 'SELECT distinct kisyu FROM mrokeisan;';
      $res = $pdo->query($selectsql);
      // pulldown set
      $row = $res->fetchall(PDO::FETCH_ASSOC);
      print '<p class="log_list_kisyu">';
      print '<select name="kisyu" class="round">';
      print '<option value="" hidden class="kisyu">機種名</option>';

      foreach($row as $val){
        print '<option value='.$val['kisyu'].'>';
        print $val['kisyu'];
        print '</option>';
      }
      print "</select>";
      print "</p></br>";

      $selectsql = 'SELECT distinct kensyu_st FROM mrokeisan;';
      $res = $pdo->query($selectsql);
      // pulldown set

      $row = $res->fetchall(PDO::FETCH_ASSOC);
      print '<p class="list_kensyust">';
      print '<select name="kensyust" class="round">';
      print '<option value="" hidden class="kensyust">検収ST</option>';
      foreach($row as $val){
        print '<option value='.$val['kensyu_st'].'>';
        print $val['kensyu_st'];
        print '</option>';
        // ++$i;
      }
      print "</select>";
      print "</p></br>";


      // すべてのレコードを表示

      $pdo=null;
  
    } catch (PDOException $e) {

      header('Content-Type: text/plain; charset=UTF-8', true, 500);
      exit($e->getMessage());
  
    }

  }

  public function MOQsearchSelect(){
    $maxDate;
    $res1;
    $row1;

    $driver_options=[PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,];
    try {
      $pdo = new PDO(
        $this->dsn,
        $this->username,
        $this->password,
        $this->driver_options
      );
    
      // 不要欄 nameを同じにすればどっちかの選択になる
      // まず登録してある日付の最大値をGET
      $selectsql = "SELECT DISTINCT MAX(regist_date),regist_date FROM mt_konpo;";
      $res1 = $pdo->query($selectsql);
      $row1 = $res1->fetchall(PDO::FETCH_ASSOC);
      foreach($row1 as $value1){
        $maxDate = $value1['MAX(regist_date)'];
      }
      // 最大日付のTOP5000件を表示
      $selectsql = 'SELECT distinct * FROM mt_konpo WHERE regist_date = '."'". $maxDate."'". ' limit 5000;';
      $res = $pdo->query($selectsql);
      $row = $res->fetchall(PDO::FETCH_ASSOC);
      print '<p class="moq_form">';
      print '<label>G品番：</label><input type="search" name="ghinbanSearchbox" class="headerSearchbox"><br>';
      print '<label>メーカー品番：</label><input type="search" name="makerhinbanSearchbox" class="headerSearchbox"><br>';
      print '<label>メーカー名：</label><input type="search" name="makernameSearchbox" class="headerSearchbox"><br>';
      print '<label>商社：</label><input type="search" name="syosyaSearchbox" class="headerSearchbox"><br>';
      print '<label>梱包単位：</label><input type="search" name="konpotaniSearchbox" class="headerSearchbox"><br>';
      print '<label>MOQ：</label><input type="search" name="moqSearchbox" class="headerSearchbox"><br>';
      print '<label>供給コード：</label><input type="search" name="kyokyucodeSearchbox" class="headerSearchbox"><br>';
      print '<label>荷姿：</label><input type="search" name="registdateSearchbox" class="headerSearchbox"><br>';
      print '<label>登録日付：</label><input type="search" name="registdateSearchbox" class="headerSearchbox"><br>';
      print '<label>登録者：</label><input type="search" name="registnameSearchbox" class="headerSearchbox">';
      print "</p></br>";

      // すべてのレコードを表示
  
      $pdo=null;
  
    } catch (PDOException $e) {
      header('Content-Type: text/plain; charset=UTF-8', true, 500);
      exit($e->getMessage());
  
    }

  }
  
  public function ImportDate(){
    $driver_options=[PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,];
  
    try {
        $pdo = new PDO(
            $this->dsn,
            $this->username,
            $this->password,
            $this->driver_options
        );
        // $array = array(
        //   'source D:\home\sql\denki.sql',
        //   'source D:\home\sql\dhl.sql',
        //   'source D:\home\sql\kiko.sql',
        //   'source D:\home\sql\panel.sql',
        //   'source D:\home\sql\mrost.sql'
        // );
  
        // foreach ($sql as $array) {
        //   $pdo->query($sql);
        // }
  
        $pdo=null;
  
    } catch (PDOException $e) {

        header('Content-Type: text/plain; charset=UTF-8', true, 500);
        exit($e->getMessage());
  
    }
    header('Content-Type: text/html; charset=utf-8');
  
  }
}

```

##不明点
- IDEやエディタで書いていて、デバッグをしたいときどうするのか？
  - 毎回サーバーへUPしてBrowserから実行してデバッグしていた。

##感想
- クラス化やオブジェクト指向を学ぶとコーディングは楽になるが、設計するのが大変(慣れないと)
- まだトランザクション部分は動かず、最近はRPAで忙しくPHPに手が回らない。