LoginSignup
1
0

More than 3 years have passed since last update.

PHPAdvent Calendar 2019Day 7
@karcenciel0113(nobuhiko inoma)

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

Posted at

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

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

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

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

<おおまかな流れ>

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

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を継承元に書くと、動作しなかった。なにかいい方法はないのかな？

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に手が回らない。
1
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
1
0