Class written in PHP to check for existence of a table in database, create a table or insert data into table (in bulk or one record at a time). Records are sent in as associative arrays containing the column names and values.

table_check_create_insert.php

<?php

require_once 'database_connector.php';

class TableCheckCreateInsert{

/*
* @var $conn
* @var $databse
* @var $config
*/
  private $conn;
  private $database;
  private $config;
  /*
  * @params string $database database name to be used
  * @return boolean
  * example of content in config.php:
  * [servername]
  * dbname = cake
  * host = 127.0.0.1
  * username = root
  * password = root
  * port = 3306
  */
  public function __construct($database){
    $this->database = $database;
    $dbConnect = new DatabaseConnector($database);
    $this->conn = $dbConnect->connectDb();
    $this->config = parse_ini_file('config.php', true);
  }

/*
* @params string $tablename tablename to be checked
*/
  public function checkTableExist($tablename){
    $query = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = '".$this->config[$this->database]['dbname']."' AND TABLE_NAME LIKE '".$tablename."';";
    $queryPrepared = $this->conn->prepare($query);
    $queryPrepared->execute();
    if ($queryPrepared->rowCount() == 1) {
      return true;
    }
    else{
      return false;
    }
  }

/*
* @params string $tablename
* @params array $columnNames array containing columnname => type in associative array
* @params array[$otherColumns] additional columns, contains strings with additional column command
*
* Some example:
* code
* $othercolumns = array("message VARCHAR (50) DEFAULT NULL");
* endcode
*/
  public function createTable($tablename, $columnNames = null, $otherColumns = null){
    $query = "CREATE TABLE  $tablename (";
    $query .= $this->buildColumns($columnNames);
    $query .= $this->buildOtherColumns($otherColumns);
    $query = substr($query, 0, -1);
    $query .= ");";
    //debug
    //var_dump($query);
    $queryPrepared = $this->conn->prepare($query);
    $queryPrepared->execute();
  }

  private function buildOtherColumns($otherColumns){
    $query = "";
    if ($otherColumns != null) {
    $otherColumnsLength = count($otherColumns);
            foreach ($otherColumns as $columns) {
              $query .= $columns;
              $query .= ",";
        }
      }
      return $query;
  }

  private function buildColumns($columnNames){
    $query = "";
    if ($columnNames != null) {
          $lengthtable = count($columnNames);
          foreach ($columnNames as $key => $value) {
                  $query .= $this->buildColumnTable($key, $value);
                  $query .= ",";
        }
    }
    return $query;
  }

  private function buildColumnTable($key, $value){
    if ($value == 'LONG') {
      return $key." FLOAT (20) DEFAULT NULL";
    }
    else if($value == "INTEGER"){
      return $key." INT (15) DEFAULT NULL";
    }
    else if ($value == 'DATETIME'){
      return $key." DATETIME DEFAULT NULL";
    }
    else if ($value == 'VAR_STRING'){
      return $key." VARCHAR (50) DEFAULT NULL";
    }
    else{
      return $key." ".$value." (40) DEFAULT NULL";
    }
  }

/*
* @params array $data
* some example:
* $data = {
*  [0] => {
*   'first_name' = 'bruce',
*   'last_name' = 'wayne'
*  }
*  [1] => {
*   'first_name' = 'tony',
*   'last_name' = 'stark'
*  }
*  }
*/
  public function insertData($tablename,$data = null, $bulkinsert = false){
    if ($bulkinsert == true) {
      $query = "INSERT INTO ".$tablename;
      $columnname = $this->getColumnMeta($tablename);
      $query .= $this->getColumnName($columnname, $bulkinsert);
      $query .= " VALUES ";
      foreach ($data as $arraySmall) {
        $query .= $this->getData($arraySmall);
      }
      $query = substr($query, 0, -2);
      $query .= ";";
      $this->sendQuery($query);
    }
    else{
      foreach ($data as $arraySmall) {
        $query = "INSERT INTO ".$tablename;
        $columnname = $this->getColumnNameFromArray($arraySmall);
        $query .= $this->getColumnName($columnname, $bulkinsert);
        $query .= " VALUES ";
        $query .= $this->getData($arraySmall);
        $query = substr($query, 0, -2);
        $query .= ";";
        var_dump($query);
        $this->sendQuery($query);
      }
    }
  }

  private function sendQuery($query){
    $queryPrepared = $this->conn->prepare($query);
    $queryPrepared->execute();
  }

  private function getColumnName($columnname, $bulkinsert){
    $column = " (";
    if ($bulkinsert == true) {
          foreach ($columnname as $array) {
            foreach ($array as $key => $value) {
                $column .= $value;
                $column .= ",";
                break;
            }
          }
    }
    else{
      foreach ($columnname as $name) {
        $column .= $name;
        $column .= ",";
      }
    }
    $column = substr($column, 0, -1);
    $column .= ") ";
    return $column;
  }

  private function getData($arraySmall){
    $data = " (";
    foreach ($arraySmall as $key => $value) {
      $value = mysql_real_escape_string($value);
      $data .= "'".$value."'";
      $data .= ",";
    }
    $data = substr($data, 0, -1);
    $data .= "), ";
    return $data;
  }

  private function getColumnMeta($tablename){
    $query = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '".$tablename."' AND table_schema = '".$this->config[$this->database]['dbname']."';";
    $queryPrepared = $this->conn->prepare($query);
    $queryPrepared->execute();
    $columnname = $queryPrepared->fetchAll();
    return $columnname;
  }

  private function getColumnNameFromArray($arraySmall){
    $columnname = array();
      foreach ($arraySmall as $name => $value) {
        array_push($columnname, $name);
      }
    return $columnname;
  }
}

?>