PHPでのSQL文の書き方について

Question

PHP、MySQLでWebシステムを作成中の独学初心者です。

自分の中で、出来るだけHTMLとSQL文を混ぜたくないという思いがあります。
この考えが合っているのかは分かりませんが、ソースの可読性を高める為になると信じているからです。

そのため、SQL用のファイル/クラスを作り、そこにSQL単位で関数を書きSQL文を書いて行っています。
(パーミッションの設定もしやすい)
今まで短いSQL文だけだったので、自分の中では分かりやすい！いい感じだ！と喜んでいたのですが、カラムの多いテーブルのINSERT/UPDATE文でつまづきました。

引数が大量になり何とも不恰好なソースになってしまったのです。

そこで皆さんはどのような書き方をしているのか教えてもらいたいです。
どうぞよろしくお願いします。

ちなみに今まではこんな感じで書いてました。

---

// 元クラス
class DATABASE{
    protected $pdo;
    function __construct($dsn, $user, $pass, $options){
        try {
            $this->pdo = new PDO($dsn, $user, $pass, $options);
            $this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            $this->pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
        } catch (Exception $ex) {
            die($ex->getMessage());
        }
    }
}
// ログインページで使用
class LOGIN extends DATABASE{
    function login($id, $pass){
        try {
            $sql = "-------ログインチェック用SQL文------";
            $stmh = $this->pdo->prepare($sql);
            $stmh->bindValue(":id", $id, PDO::PARAM_STR);
            $stmh->bindValue(":pass", $pass, PDO::PARAM_STR);
            $stmh->execute();
            $result = $stmh->fetch(PDO::FETCH_ASSOC);
            return $result;
        } catch (Exception $ex) {
            die($ex->getMessage());
        }
    }
    function aaaa(){...}
    function bbbb(){...}
}
// 表出力ページで使用
class LIST extends DATABASE{
    function getList($case){
        try {
            $sql = "-------表出力用SQL文------";
            $stmh = $this->pdo->prepare($sql);
            $stmh->bindValue(":case", $case, PDO::PARAM_INT);
            $stmh->execute();
            $result = $stmh->fetch(PDO::FETCH_ASSOC);
            return $result;
        } catch (Exception $ex) {
            die($ex->getMessage());
        }
    }
    function cccc(){...}
    function dddd(){...}
}

---

補足

↑は上手くいっている部分しか書いてませんでした。
今までの書き方だとINSERT/UPDATEしようとすると↓こうなってしまって、他にスマートな書き方はないかと質問しました。
あくまで例なのでDB設計はスルーで

// データ更新用phpで使用
class UPSERT extends DATABASE{
    function insert($name_sei, $name_mei, $name_seikana, $name_meikana, $age, $sex, $hight, $weight, $company, $tel1, $tel2, $address){
        try {
            $sql = "-------レコード挿入SQL文------";
            $stmh = $this->pdo->prepare($sql);
            $stmh->bindValue(":...", $..., PDO::PARAM_INT);
            ...
            ...
            $stmh->execute();
        } catch (Exception $ex) {
            die($ex->getMessage());
        }
    }
}

例えば、連想配列に格納してそれを受け渡しする(回答を参考にしました)
例えば、受け取る変数分クラスにプロパティを用意しておいて、渡す側で直接そっちに格納する(これがいいかな？)
例えば、そもそもSQL文は関数に入れずに直接書くべき
などなど

すみません、流行りが分からなかったので...
一般的にはこうします、こうするのがよりベターです、というご回答があればぜひお願いします。

Answer 1

方向性としてはとても的を射ていると思います。
既存の優れたO/Rマッパーや、
フレームワークのモデルの考え方を参考にされるととても勉強になると思います。

その上で、以下は私の個人的な感想というか考えになります。

まず、引数が多いことは特に問題にはならないと考えます。
問題になるのは、

• 引数のうち、必然性の無いオプション的な項目が多すぎる（メソッドになんでもやらせようとした結果、責任範囲があいまいになる）
• 引数によって振る舞いが変わりすぎる

ようなケースかと思います。
特定のテーブルに対して挿入や更新を行うメソッドにおいて、
フィールドの数だけ引数が存在するのは引数に必然性があるので全く問題無いと思います。

また、下手に連想配列で渡してしまうと、
その引数のチェックをメソッド内で行う必要が発生し、
見通しの悪いソースになってしまうことがあります。

これをどうクリアするかは色々な方法がありますが、
例えばタイプヒンティング
参考URL
を使い、
引数はオブジェクトで渡す。
引数のオブジェクトとしての正当性はそのクラスのコンストラクタで保証する。

というような方法があります。
例えばこんな感じでしょうか。
*実際には抽象クラスを使って、テーブル単位での共通の振る舞いを記述するケースが多いかと思います。
*既存のO/Rマッパーやフレームワークの方が確実に参考になります。

クラス定義

class member{
   private $first_name = "";
   private $family_name = "";

   public function __construct($first_name,$family_name){
        //引数の正当性はここでチェックする。問題があれば例外を吐いて死ぬ
        if(//menberレコードとして必須条件を記入){
           $this->first_name  = $first_name;
           $this->family_name = $family_name;
           return true;
        }
        //
        throw new Exception('memberのインスタンス化に失敗');
   }
    //フィールドにあたる部分を連想配列で取得
    public function getFieldsAsASSOC(){
         return array("first_name" => $this->first_name, "family_name" => $this->first_name);

    }
    

}
class menberUpdater extends updater{
    public function update(member $menber){
        //memberオブジェクトの正当性は自身に保証させるので、ここでチェックする必要は無し
        try {
            $sql = "-------レコード更新SQL文------";
            $stmh = $this->prepare($sql);
            $stmh->execute($member->getFieldsAsASSOC());
            //update出来たかチェックして、成功してたらtrueを返すような処理を書く
        } catch (Exception $ex) {
            die($ex->getMessage());
        }
    }

}

実行

//

try{

//入力値がおかしかったらここで例外
$menber = new member("tanaka","taro");
$updater = new memberUpdater();
$updater->update($menber);
}catch($e){
//エラー処理
}

Comments

[erika.m]

ありがとうございます。
実際のソースがとても参考になりました。

Answer 2

ある程度以上大掛かりになるのであれば、直接PHPで書くより、何かしらのフレームワークを使ったほうがいいのではないかと思います。

フレームワークと言っても、「設定より規約」型のCakePHPから、MVCの枠組み程度のCodeIgniterまで、いろいろあります。比較検討した上で、自分にあったものを使ってみるのがいいでしょう。

Comments

[erika.m]

PHPのフレームワーク、使ったことがないので調べてみます！

Answer 3

もっと単純にプリぺアド処理分を?とそれに適合する配列になるよう調整すればよいでしょう
例えば

INSERT INTO テーブル VALUES(?,?,?);

に対して配列を

$data=(1,2,"test");

的に用意すれば

$stmt = $pdo->prepare( $query);
$stmt->execute($data);

のような処理で行けます
このほうがバルクインサート処理をするときにも楽です

Answer 4

型別に別途クラスを作っておいて、カラムをプロパティに持たせて、型クラスのインスタンスを代入するのはどうでしょう？型チェックや必須項目等、型クラス側にプロパティを持たせてチェック。
データは皆さんおっしゃるように配列でデータを渡します。
こんな雰囲気で。（あちこちエラーあるかも）

private $elms = new array();

public insert($arr){
  try{
    $this->setElms($arr);
    $this->sqlExec(); //elmsを使ってSQLを作成し実行
  }catch (Exception $ex) {
    die($ex->getMessage());
  }
}
private function init(){ // コンストラクタで実行
  $elms['name'] = new varcharElm('notnull');
  $elms['age'] = new intElm();
}
private setElms($arr){
  foreach ( $elms as $key => $val ) {
    if(array_key_exists($arr,$key))
      $elms[$key]->setVal($val); //型エラー等問題があればsetval側でエラー
    }else{ //keyに対応するデータがなければ初期化
      $elms[$key]->clear(); 
      $elms[$key]->req(); //notnull等問題があればreq側でエラー
    }
  }
}
private sqlExec(){}

Answer 5

引数が大量……ですか？
見たところ別段多くないと思いますよ。

参考までに引数をまとめたいなら引数を一つだけにして、連想配列で渡すという方法はあります。

マニュアルのですが具体例です。

<?php
/* Execute a prepared statement by passing an array of insert values */
$calories = 150;
$colour = 'red';
$sth = $dbh->prepare('SELECT name, colour, calories
    FROM fruit
    WHERE calories < :calories AND colour = :colour');
$sth->execute(array(':calories' => $calories, ':colour' => $colour));
?>

引用元　http://php.net/manual/en/pdostatement.execute.php

追記２

SQLの周りのコードを綺麗にするならこうです

 //テストデータ
  $example_name_sei     = 'a';
  $example_name_mei     = 'b';
  $example_name_seikana = 'a';
  $example_name_meikana = 'b';
  $example_age          = 5;
  $example_sex          = 'm';
  $example_hight        = 60;
  $example_weight       = 70;
  $example_company      = 'hoge';
  $example_tel1         = 111;
  $example_tel2         = 111;
  $example_address      = 'hogehoge';

//これを別のところで作る
$hogedatas = array(
  ':name_sei'     => $example_name_sei,
  ':name_mei'     => $example_name_mei,
  ':name_seikana' => $example_name_seikana,
  ':name_meikana' => $example_name_meikana,
  ':age'          => $example_age,
  ':sex'          => $example_sex,
  ':hight'        => $example_hight,
  ':weight'       => $example_weight,
  ':company'      => $example_company,
  ':tel1'         => $example_tel1,
  ':tel2'         => $example_tel2,
  ':address'      => $example_address
);

function InsertRecord($array){
 try{
  $sql = 'INSERT INTO hogetable SET 
          name_sei       = :name_sei, 
          name_mei       = :name_mei, 
          name_seikana   = :name_seikana, 
          name_meikana   = :name_meikana, 
          age            = :age , 
          sex            = :sex , 
          hight          = :hight, 
          weight         = :weight, 
          company        = :company,
          tel1           = :tel1 , 
          tel2           = :tel2 , 
          address        = :address ';
  $stmh = $this->prepare($sql);
  $stmh->execute($array);
 }catch (Exception $ex) {
  die($ex->getMessage());
 }
}


//どっかで発火
InsertRecord($hogedatas);

Comments

[erika.m]

すみません、多くなった関数は途中で変だと感じて書くのを止めたので載せてないです。
-----補足で追記しました。-----

executeに連想配列で渡すというのも試してみます。

Answer 6

項目が多いのがあれだったら

function ins($table, $data) {
  $key = array_keys($data);
  $val = array_values($data);
  $sql = 'insert into ' . $table
       . ' (' . implode(',', $key) . ')'
       . ' VALUES(' . implode(',', $val) . ')';
}

こんな感じで。
ただし、文字はシングルクォーテーションでくくるとか
エスケープどうするかとか考えなきゃいけないことは
ありますけどね。

Comments

[erika.m]

イレギュラーへの対応やエスケープが難しそうですね。
参考にします。