This is a copy of the SQLblocks.php program that I made for a discussion on HIVE:

https://hive.blog/@yintercept


<?php
define('LOG_DIR','/var/www/log/');

/**
* @package ResourceModel
* sqlBlock will create an INSERT, UPDATE, REPLACE or DELETE SQL statement.
* You can define the columns and validation in a single block or add
* items one by one.
*
* @copyright 2007-2016 kd
* The test script is found here:
* @see https://yintercept.com/resources/blockTest.php
* The program executes the SQL with sqlExec() from the sql.php file.
* @see https://yintercept.com/resources/sqlTest.php
*
* Program by Kevin Delaney ( https://yintercept/resources/ )
* 2007-04-30 : kd. Alpha Release.
* 2007-05-04 : kd. Made log private, it is now called from execSQL.
* 2016-01-30 : kd. Updated for Resource Model
* 2016-08-23 : kd. changed order so that we define the columns then bindValues
* 2020-12-15 : kd. Added ability to process date prompts, which I convert to julian days.
*/
class sqlBlock {
  private $dbi;      // reference to the database holding the Table.
  private $table;    // name of table to update
  private $command;  // a single character (i,r,u,d) denotes the command.
  private $limit;    // optional limit on rows updated.
  private $colCnt=0; // number of columns in the query.

  // private $colArr = array(); // Multidimensional Array defines columns and validation.
  public $colArr = array(); // holds the data for the statement.
  // translates the $command character into a SQL command.
  private $actions = array('i'=>'INSERT INTO ','u'=>'UPDATE ', 'd'=>'DELETE FROM ','r'=>'REPLACE INTO ');
  public $stripTags=true;

  /**
   * __construct defines the table and command for the SQL Statement.
   * @PARAM string $cmd is 1st letter of 'I'NSERT, 'R'EPLACE, 'U'PDATE or 'D'ELETE
   *               NOTE: I like pass the whole word to make my program clear.
   * @PARAM string $table is the name of the Table to modify.
   * @PARAM string $dbi is the database containing the table.
   */
  function __construct ($cmd, $table, $dbi=DB_MAIN) {
    $this->command = strtolower(substr($cmd,0,1));
    if (!isset($this->actions[$this->command])) msgError('Invalid command '.$cmd);
    $this->table = $table;
    $this->dbi = $dbi;
    $sqlStr = ''; // This is the main SQL Statement
    $valStr = ''; // The VALUES section for INSERT and REPLACE.
    $whereStr=''; // The WHERE string for an UPDATE Statement.
    $this->colCnt=0;
    $this->delimiter=' '; // changes to a comma after first value
    $this->limit=null;  // You can limit affected rows of an UPDATE statementi
  }

  /**
   * defColumn() defines a column for query and validates the data.
   * NOTE, you must add the data before defining the column!
   * @PARAM string $colName is the name of the column in the Table.
   * @PARAM mixed $required is a directive for the column.
   *   values can be true, false, "y", "n" or "w"
   *   "w" means the value is in the WHERE clause.
   * @PARAM string $type is the data type of the column

   * @PARAM mixed $w_or_len is either 'w' indicating that variable is part of
   *        the key in WHERE clause or maximum length of the column.
   */
  public function defColumn($colName, $req=false, $type='str', $max=0, $val=null) {
    // the format of $colArr is
    // [colName, required, type, max, dataSource, val]
    // if value is null, set the datasource to p for $_POST.
    $reqStr = '<b>'.$colName.'</b> is a required field';
    if (is_string($req)) {
      if ($req=='') {
        $req=false;
      } elseif (strlen($req) == 1) {
        $req = in_array(substr($req,0,1),['y','Y','t','T','r','R']);
      } else {
        // if there is more than one spaces assume req is a whole sentence.
        $reqStr = (substr_count($req,' ') < 2)? '<b>"'.$req.'"</b> is a required field.' : $req;
        $req = true;
      }
    }
    $dsrc = (is_null($val))? 'p': 'v';
    if ($colName == 'where') {
      // separates UPDATE SET and WHERE clause.
      // not needed on Delete as everything is in WHERE.
      $type = 'where';
    } elseif ($type == 'now') {
      $type = 'str'; // it is really a float
      $val = $GLOBALS['rmSite']->jd;
    } elseif ($type=='today') {
      $type = 'int';
      $val = floor($GLOBALS['rmSite']->jd + .5);
    } elseif ($type=='site') {
      $type = 'int';
      $val = $GLOBALS['rmSite']->id;
    } elseif ($type=='user') {
      $type = 'int';
      $dsrc='v';
      $val = $GLOBALS['rmUser']->id;
    } elseif ($type=='ip') {
      $type = 'ip';
      $val = $GLOBALS['rmIP']->id;
    } elseif ($type=='sess') {
      $type = 'int';
      $val = $GLOBALS['rmUser']->session;
    } elseif ($dsrc=='p') {
      // the data is found in $_POST.
      if (isset($_POST[$colName])) {
        if ($req && $_POST[$colName] == '') msgError($reqStr);
        if ($type == 'int') {
          // cast to int and put data in $val.
          $val = (int) $_POST[$colName];
          $dsrc = 'v';
          if ($max > 0 && $val > $max) msgError('<b>'.$colName.'</b> is greater than maximum value '.$max);
        } elseif ($type=='color') {
          $type='int';

          $val = hexdec($_POST[$colName]); // strip off the #
          msgNote('Col is '.$_POST[$colName].' = '.$val);
        } elseif ($type=='date') {
          $dtArr = explode('-',$_POST[$colName]);
          $val = 0;
          if (isset($dtArr[2])) {
            // gregoriantojd ( int $month , int $day , int $year )
            $val = gregoriantojd( (int) $dtArr[1], (int) $dtArr[2], (int) $dtArr[0]);
            msgNote('Browser date '.$_POST[$colName].' translated to Julianday '.$val);
          }
        } else {
          if ($max > 0 && strlen($_POST[$colName]) > $max) msgError('The maximum length of <b>'.$colName.'</b> is '.$max.' characters.');
        }
      } else {
        if ($req) msgError($reqStr);
        $dsrc = 'v'; // change dataSource to 'v' so we don't check $_POST again.
        // I prefer having 0 to null in integer fields.
        $val = ($type=='int')? 0 : '';
      }
    } else {
      if ($type == 'int') {
        // cast the value to int.
        $val = (int) $val;
        if ($max > 0 && $val > $max) msgError('<b>'.$colName.'</b> is greater than maximum value '.$max);
      } elseif ($type=='color') {
        $type='int';
        $val = hexdec($val); // strip off the #
      } else {
        if ($max > 0 && strlen($_POST[$colName]) > $max) msgError('The maximum length of <b>'.$colName.'</b> is '.$max.' characters.');
      }
    }
    if ($val !== null) $dsrc = 'v';
    if ($colName != '') $this->colArr[$this->colCnt++] = [$colName, $req, $type, $max, $dsrc, $val, ($type=='html')? false : $this->stripTags];
    return $val;
  }
  public function addCol($colName, $val, $type='str', $req=false, $max=0) {
    $this->defColumn($colName, $req, $type, $max, $val);
  }
  /**
   * addColumn() is the same as defColumn, with parameters in a different order.
   */
  public function addColumn($colName, $val, $type='str', $max=0, $req=false) {
    $this->defColumn($colName, $req, $type, $max, $val);
  }
  /**
   * addBlock() will take a delimited string and add a block of columns.
   * The program calls defColum() with each row in the block.
   * @PARAM string $block is a formatted block defining multiple columns
   * @PARAM string $delim is the data elimiter for the block.
   * @PARAM string $lineDelim is the line delimiter for the block.
   */
  public function addBlock($block, $delim=',', $lineDelim = PHP_EOL) {
    $tok = strtok($block, $lineDelim);
    while ($tok !== false) {
      list($colName, $required, $type, $max, $val) = explode($delim,rtrim($tok).$delim.$delim.$delim.$delim);
      if ($val=='') $val = null;
      $this->defColumn($colName, $required, $type, $max, $val);

      $tok = strtok($lineDelim);
    }
  }
  /**
   * getSQL() produces the SQL with marked variables.
   * @return program returns a SQL  statement.
   */
  public function getSQL() {
    $rv = '';
    $delim = '';
    $inSetDef= ($this->command == 'u')? true : false;
    $valStr = ''; // used on INSERT and REPLACE

    if ($this->command == 'd') {
      $rv = ($this->whereStr != '')? '' : 'DELETE FROM '.$this->table.' WHERE ';
    } elseif ($this->command == 'u') {
      $rv = 'UPDATE '.$this->table.' SET ';
    } elseif ($this->command == 'i') {
      $rv = 'INSERT INTO '.$this->table.' (';
      $valStr = ') VALUES (';
    } elseif ($this->command == 'r') {
      $rv = 'REPLACE INTO '.$this->table.' (';
      $valStr = ') VALUES (';
    } else {
      msgError('Invalid Command '.$this->command);
    }
    // it is cleaner to process INSERT and UPDATE in different loops.
    if ($this->command == 'i' or $this->command == 'r') {
      for ($i=0; $i<$this->colCnt; $i++) {
        $rv .= $delim.$this->colArr[$i][0];
        $valStr .= $delim.':'.$this->colArr[$i][0];
        if ($delim == '') $delim = ', ';
      }
      $valStr .= ')';
    } elseif ($this->command == 'u' or $this->command == 'd') {
      $delim = '';
      for ($i=0; $i<$this->colCnt; $i++) {
        if ($this->colArr[$i][0] == 'where') {
          $inSetDef = false;
          $delim = '';
          $rv .= ' WHERE ';
        } else {
          $rv .= $delim.$this->colArr[$i][0].' = '.':'.$this->colArr[$i][0];
          if ($delim == '') {
            $delim = ($inSetDef)? ', ' : ' and ';
          }
        }
      }
      // There was no where clause in this statement.
      // to avoid whole table operations set $sql to '';
      if ($delim != ' and ') {
         msgError('No Where Clause in SQL: <pre>'.$rv.'</pre>');
      }
    }

    return $rv.$valStr;
  }

  /**
   * exec() calls sqlExec with built statement to execute the SQL
   * @param string $successMsg is displayed on success.
   * @param string $errMsg is displayed on error.
   * @param string $logFile will hold error messages for admin.
   */
  public function exec($successMsg='',$errMsg='',$logFile='') {
    $rv = -1;
    if (msgOkay()) {
      try {
        $sql = $this->getSQL();
        $st = dbConn($this->dbi,$sql);
        if ($st === false) {
          msgError('PDO Prepare Failed');
          msgError(dbConn($this->dbi,DB_ERRORS));
        } else {
          // bind values
          for ($i=0; $i<$this->colCnt; $i++) {
            if ($this->colArr[$i][4] == 'v') {
              $pdoType = ($this->colArr[$i][2] == 'int')? PDO::PARAM_INT : PDO::PARAM_STR;
              $st->bindValue($this->colArr[$i][0],$this->colArr[$i][5],$pdoType);
            } elseif ($this->colArr[$i][0] == 'where') {
              // skip
            } elseif ($this->colArr[$i][4] == 'p') {
              if ($this->colArr[$i][6]) {
                $st->bindValue($this->colArr[$i][0],strip_tags($_POST[$this->colArr[$i][0]]));
              } else {
                $st->bindValue($this->colArr[$i][0],$_POST[$this->colArr[$i][0]]);
              }
            } // skip this row.
          }
          // msgNote('Executing SQL');
          $stx = $st->execute();
          if ($stx===false) {
            msgError('PDO Statement Failed.');
            msgComment(implode('<br />',$st->errorInfo()));
            msgComment('SQL: '.$sql);
            // $st->debugDumpParams();
          } else {
            $exId = dbConn($this->dbi,DB_INSERT_ID);
            $exCnt = $st->rowCount();
            if ($successMsg != '') msgNote(str_replace(['%ID','%CNT'],[$exId,$exCnt],$successMsg));
            $rv = ($this->command == 'i')? $exId : $exCnt;
          }
        }
      } catch (PDOException $e) {
        msgError('PDO Error '.$e->getMessage());
      }
    }
    // if (strtolower($log) != '') $this->log($logFile);
    return $rv;
  }
}
?>