db.php

<?php

class DB
{
  public $db = null;

  public function __construct($db)
  {
    $this->db = $db;
  }


  /**
   * @param $table
   * @param string $cols
   * @param bool $all
   * @param string $optional
   * @return mixed
   */
  public function select($table, $cols = "*", $all = true, $optional = '')
  {
    $sql = "SELECT {$cols} FROM {$table} {$optional}";
    $query = $this->db->prepare($sql);
    $query->execute();
    if ($query->rowCount() > 0) {
      return $all ? $query->fetchAll(PDO::FETCH_OBJ) : $query->fetch(PDO::FETCH_OBJ);
    }
    return false;
  }


  /**
   * @param $table
   * @param string $cols
   * @param array $where
   * @param bool $all
   * @param string $optional
   * @return mixed
   */
  public function select_where($table, $cols = "*", $where = [], $all = true, $optional = '')
  {

    $whereCommand = "";

    foreach ($where as $key => $value) {
      $whereCommand .= $key . " = " . "? AND ";
    }

    $whereCommand = rtrim($whereCommand, 'AND ');

    $sql = "SELECT {$cols} FROM $table WHERE {$whereCommand} {$optional}";
    $query = $this->db->prepare($sql);
    $query->execute(array_values($where));

    if ($query->rowCount() > 0) {
      return $all ? $query->fetchAll(PDO::FETCH_OBJ) : $query->fetch(PDO::FETCH_OBJ);
    }
    return false;
  }

  /**
   * Insert Query
   * @param $table
   * @param $array
   * @return bool
   */
  public function insert($table, $array = [])
  {
    $columnString = implode(',', array_keys($array));
    $valueString = implode(',', array_fill(0, count($array), '?'));

    $sql = "INSERT INTO {$table} ({$columnString}) VALUES ({$valueString})";

    $query = $this->db->prepare($sql);
    return $query->execute(array_values($array));
  }

  /**
   * Batch Insert Query
   * @param $table
   * @param $data
   * @return bool
   */
  public function batch_insert($table, $data = [])
  {
    //Will contain SQL snippets.
    $rowsSQL = array();

    //Will contain the values that we need to bind.
    $toBind = array();

    //Get a list of column names to use in the SQL statement.
    $columnNames = array_keys($data[0]);

    //Loop through our $data array.
    foreach ($data as $arrayIndex => $row) {
      $params = array();
      foreach ($row as $columnName => $columnValue) {
        $param = ":" . $columnName . $arrayIndex;
        $params[] = $param;
        $toBind[$param] = $columnValue;
      }
      $rowsSQL[] = "(" . implode(", ", $params) . ")";
    }

    //Construct our SQL statement
    $sql = "INSERT INTO `$table` (" . implode(", ", $columnNames) . ") VALUES " . implode(", ", $rowsSQL);


    //Prepare our PDO statement.
    $pdoStatement = $this->db->prepare($sql);

    //Bind our values.
    foreach ($toBind as $param => $val) {
      $pdoStatement->bindValue($param, $val);
    }

    //Execute our statement (i.e. insert the data).
    return $pdoStatement->execute();
  }


  /**
   * @param $table
   * @param array $colname
   * @param array $data
   * @return mixed
   */
  public function update($table, $colname = [], $data = [])
  {
    $fieldname = implode(',', array_keys($colname));

    $sqlCommand = "";
    foreach ($data as $key => $value) {
      $sqlCommand .= $key . '=' . '?, ';
    }

    $sqlCommand = rtrim($sqlCommand, ', ');

    $sql = "UPDATE {$table} SET {$sqlCommand} WHERE {$fieldname} = ?";

    $query = $this->db->prepare($sql);
    $params = array_merge(array_values($data), array_values($colname));
    return $query->execute($params);
  }

  /**
   * Batch Update Query
   * @param $table
   * @param $data
   * @param $colname
   * @return bool
   */
  public function batch_update($table, $data = [], $colname = [])
  {
    if ($this->delete($table, $colname)) {
      //Will contain SQL snippets.
      $rowsSQL = array();

      //Will contain the values that we need to bind.
      $toBind = array();

      //Get a list of column names to use in the SQL statement.
      $columnNames = array_keys($data[0]);

      //Loop through our $data array.
      foreach ($data as $arrayIndex => $row) {
        $params = array();
        foreach ($row as $columnName => $columnValue) {
          $param = ":" . $columnName . $arrayIndex;
          $params[] = $param;
          $toBind[$param] = $columnValue;
        }
        $rowsSQL[] = "(" . implode(", ", $params) . ")";
      }

      //Construct our SQL statement
      $sql = "INSERT INTO `$table` (" . implode(", ", $columnNames) . ") VALUES " . implode(", ", $rowsSQL);


      //Prepare our PDO statement.
      $pdoStatement = $this->db->prepare($sql);

      //Bind our values.
      foreach ($toBind as $param => $val) {
        $pdoStatement->bindValue($param, $val);
      }

      //Execute our statement (i.e. insert the data).
      return $pdoStatement->execute();
    }
    return false;
  }

  /**
   * Delete query
   * @param $table
   * @param $colname
   * @return bool
   */
  public function delete($table, $colname = [])
  {
    $fieldname = implode(',', array_keys($colname));
    $sql = "DELETE FROM {$table} WHERE {$fieldname} = ?";
    $query = $this->db->prepare($sql);
    return $query->execute(array_values($colname));
  }
}

$db = new DB($mainconn);