LoginSignup
3
7

More than 5 years have passed since last update.

@yasuaki0206

phpでmysqliクラスを使ってみた

Last updated at Posted at 2017-06-20

経緯

初投稿
先日php7を自鯖に導入したところ mysql_connect 等のmysql関数が使えなくなった(削除されたっぽい)ので
mysqliクラスをラップして、簡単なDB操作クラスを作成してみました:thinking:
github:https://github.com/YasuakiHirano/phptools/tree/master/mysqli

06/22
指摘いただいた個所を修正してみました。
namespaceの追加、sqlインジェクション対策でpreparedステートメントを追加。
close処理がなかっため追加。

mysql操作クラス

mysqli.lib.php
<?php
namespace DBLib;

class DBMysqli {

    private $mysqli;
    private $debugflg = false;
    private $logtype = 0;

    public function __construct($host, $username, $password, $dbname) {
        $this->mysqli = new \mysqli($host, $username, $password, $dbname);
        if($this->mysqli->connect_error) {
            error_log($this->mysqli->connect_error , $this->logtype);
            exit;
        }
    }

    public function selectPrepare($table, $column = '*', $where, $values){
      $this->emptyValidate(array('table' => $table, 'where' => $where));

      $sql = "SELECT {$column} FROM {$table} WHERE {$where} ";

      if($stmt = $this->mysqli->prepare($sql)){
        $params = [];
        foreach($values as $val) {
          $params[0] .= 's';
          $params[] = &$val;
        }

        \call_user_func_array(array($stmt, 'bind_param'), $params); 

        $stmt->execute();
        $result = $stmt->get_result();
        $datas = array();
        while($ret = $result->fetch_assoc()){
          array_push($datas, $ret);
        }

        $stmt->close();

        return $datas;
      }

      return false;
    }

    public function select($table, $column = '*', $where = '', $option = MYSQLI_ASSOC) {
      $this->emptyValidate(array('table' => $table));

      $sql = 'SELECT ';

      if(is_array($column)) {
        foreach($column as $value) {
            $sql .= $value.',';
        }

        $sql = substr($sql, 0,  -1); 
        $sql .= " FROM {$table}";

      } else {
        $sql .= "{$column} FROM {$table}"; 
      }

      if(!empty($where)) {
        $sql .= " WHERE {$where}"; 
      }

      if($this->debugflg) {
        $log = "execute sql:".$sql;
        error_log($log, $this->logtype);
      }

      $res = $this->mysqli->query($sql);
      if(!$res) {
         echo 'select error:'.$this->mysqli->error;
        if($this->debugflg) error_log($this->mysqli->error, $this->logtype);
        exit;
      }  

      $rows = null;
      while($row = $res->fetch_array($option)){
        $rows[] = $row;
      }

      return $rows;
    }

    public function insert($table, $column, $values = ''){
      $this->emptyValidate(array('table' => $table, 'column' => $column));

      $sql = "INSERT INTO {$table} ";
      $column_str = '(';
      $values_str = '(';

      if(is_array($column)) {
        foreach($column as $key => $value) {
          $column_str .= $key.',';

          if(is_string($value)){
            $values_str .= "'{$value}',"; 
          } else {
            $values_str .= $value.','; 
          }
        }
        $column_str = substr($column_str, 0,  -1).')'; 
        $values_str = substr($values_str, 0,  -1).')'; 

        $sql .= $column_str;
        $sql .= ' VALUES '.$values_str;

      } else {
        $sql .= " ({$column}) VALUES ({$values})"; 
      }

      if($this->debugflg) {
        $log = "execute sql:".$sql;
        error_log($log, $this->logtype);
      }


      $res = $this->mysqli->query($sql);
      if(!$res) {
        echo 'insert error:'.$this->mysqli->error;
        if($this->debugflg) error_log($this->mysqli->error, $this->logtype);
        exit;
      }  
      return $res;
    }

    public function insertPrepare($table, $values){
      $this->emptyValidate(array('table' => $table));

      $where_values = '';
      $column = '';
      foreach($values as $key => $val){
        $column .= "{$key},";
        $where_values .= '?,';
      }
      $column = substr($column, 0, -1);
      $where_values = substr($where_values, 0, -1);

      $sql = "INSERT INTO {$table} ({$column}) VALUES ({$where_values}) ";

      if($stmt = $this->mysqli->prepare($sql)){
        $params = [];
        foreach($values as $key => $val) {
          if(is_int($val)){
            $params[0] .= "i";
          } elseif(is_double($val)){
            $params[0] .= "d";
          } else {
            if(strpos($val, "\0") === false){
              $params[0] .= "s";
              $val = (string)$val;
            } else {
              $params[0] .= "b";
            }
          }

          $params[] = &$values[$key];
        }

        call_user_func_array(array($stmt, 'bind_param'), $params); 

        $stmt->execute();
        $stmt->close();

        return true;
      }

      return false;
    }

    public function delete($table, $where = ''){
      $this->emptyValidate(array('table' => $table));
      $sql = "DELETE FROM {$table} ";

      if(!empty($where)) {
        $sql .= "WHERE {$where}";
      }

      if($this->debugflg) {
        $log = "execute sql:".$sql;
        error_log($log, $this->logtype);
      }

      $res = $this->mysqli->query($sql);
      if(!$res) {
        echo 'delete error:'.$this->mysqli->error;
        if($this->debugflg) error_log($this->mysqli->error, $this->logtype);
        exit;
      }  
    }

    public function update($table, $column, $where = ''){
      $this->emptyValidate(array('table' => $table, 'column' => $column));

      $sql = "UPDATE {$table} SET ";
      if(is_array($column)) {
        foreach($column as $key => $value) {
          if(is_string($value)) {
            $sql .= "{$key} = '{$value}',";
          } else {
            $sql .= "{$key} = {$value},";
          }
        }
        $sql = substr($sql, 0,  -1); 

      } else {
        $sql .= "{$column}"; 
      }     

      if(!empty($where)) {
        $sql .= " WHERE {$where}"; 
      }

      if($this->debugflg) {
        $log = "execute sql:".$sql;
        error_log($log, $this->logtype);
      }

      $res = $this->mysqli->query($sql);
      if(!$res) {
         echo 'update error:'.$this->mysqli->error;
        if($this->debugflg) error_log($this->mysqli->error, $this->logtype);
        exit;
      } 
    }

    public function tranInsert($table, $column, $values = ''){
        $this->mysqli->autocommit(FALSE);

        try {
            if(!$this->insert($table, $column, $values)){
                throw new Exception('tranInsert error');
            }
        } catch(Exception $e) {
            $this->mysqli->rollback();
        }

        if($this->mysqli->commit()) {
            if($this->debugflg) error_log('commit failed', $this->logtype);
            exit();
        }

        $this->mysqli->autocommit(TRUE);
    }

    public function close(){
        $this->mysqli->close();
    }

    public function queryExec($sql){
      $res = $this->mysqli->query($sql);
      if(!$res) {
         echo 'queryExec error:'.$this->mysqli->error;
        if($this->debugflg) error_log($this->mysqli->error, $this->logtype);
        exit;
      } 

      return $res;
    }

    public function emptyValidate($data, $outname = '') {
      $error_message = '';
      if(is_array($data)) {
        foreach($data as $key => $value) {
          if(empty($value)){
            $error_message .=  "please input {$key}.";
          }
        }
      } else {
          if(empty($data)){
             $error_message .= "please input {$outname}.";        
          }
      }

      if(!empty($error_message)){
        echo $error_message;
        exit;
      }
    }

}

テスト用PHP

testmysqli.php
<?php
    require_once 'mysqli.lib.php';

    function outputResult($test_name,$ary){
        echo $test_name.":\n";
        print_r($ary);
    }

    $password = 'password';
    $dbname = 'dbname';
    $user = 'root';
    $host = 'localhost';

    $mysqli = new DBLib\DBMysqli($host, $user, $password, $dbname);

    // ## exec query
    $mysqli->queryExec("drop table hoge"); 
    $mysqli->queryExec("create table hoge(id int, name text, date_enterd datetime)"); 

    // ## insert test
    // 1. insert
    for($i = 0; $i < 10; $i++) {
        $insert_data = array(
               'id' => $i, 
               'name' => 'hoge'.$i,
               'date_enterd' => date('Ymd')
             );
        $mysqli->insert('hoge', $insert_data);
    }

    // 2. insert prepare 
    for($i = 10; $i < 15; $i++) {
        $insert_data = array(
               'id' => $i,
               'name' => 'hoge'.$i,
               'date_enterd' => date('Ymd')
             );
        $mysqli->insertPrepare('hoge', $insert_data);
    }

    // ## select test
    // 1. table name only 
    $row = $mysqli->select('hoge');
    outputResult('select test1',$row);

    // 2. table and column name
    $row = $mysqli->select('hoge', array('id', 'name'));
    outputResult('select test2',$row);

    // 3. where
    $row = $mysqli->select('hoge', array('id', 'name'), "id in (1,2,3)");
    outputResult('select test3',$row);

    // 4. select prepare
    $row = $mysqli->selectPrepare('hoge', 'name, id', 'id = ? OR name = ?', array('1', 'hoge5'));
    outputResult('select test4',$row);

    // ## delete test
    $mysqli->delete('hoge', "id in (0,1,2,3,4,5,6,7,10,11,12,13,14,15,16,17,18,19)");
    $row = $mysqli->select('hoge');
    outputResult('delete test',$row);

    // ## update test
    $mysqli->update('hoge', array('name' => 'update!!!', 'id' => '999'),"id in (8, 9)");
    $row = $mysqli->select('hoge');
    outputResult('update test',$row);

    // close
    $mysqli->close();

雑ですが、ぱぱっと作った割にはいい感じかなと。。

3
7
2

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
3
7