RM - Source Code View

Source Code Viewer

This page lets you view source code from my server. The program uses a brute force code formatter to color code elements. NOTE: I wrote this program while trying to learn the vim text editor. This is not my usual coding style.

Use this select box to select a file.

Hide Lines Numbers

View: sqlBlock

This function produces a SQL statement from an array such as $_POST and a block of validation text.

Formatted Code

Below is the code all formatted with bright colors. The program links to files opened with include() and expands those opened with require(). Clicking on the require line should change visibility.

sqlBlock

001 <!DOCTYPE> 002 <html> 003 <?php 004 /** 005 * This page tests the sqlBlock object. This object creates SQL from a 006 * block of text and an array such as $_POST or $_GET. 007 * The program executes the SQL with the sqlExec() program found in 008 * sql.php which referenced in rmHead. 009 * @see https://yintercept.com/resources/sqlTest.php 010 * This page will create and display a variety SQL statements. 011 */ 012 include('/var/www/php/rmHead.php'); // links to 1
require('/var/www/php/sqlBlock.php');
001 <?php 002 define('LOG_DIR','/var/log/'); 003 /** 004 * @package ResourceModel 005 * sqlBlock will create an INSERT, UPDATE, REPLACE or DELETE SQL statement. 006 * You can define the columns and validation in a single block or add 007 * items one by one. 008 * 009 * @copyright 2007-2016 kd 010 * The test script is found here: 011 * @see https://yintercept.com/resources/blockTest.php 012 * The program executes the SQL with sqlExec() from the sql.php file. 013 * @see https://yintercept.com/resources/sqlTest.php 014 * 015 * Program by Kevin Delaney ( https://yintercept/resources/ ) 016 * 2007-04-30 : kd. Alpha Release. 017 * 2007-05-04 : kd. Made log private, it is now called from execSQL. 018 * 2016-01-30 : kd. Updated for Resource Model 019 */ 020 class sqlBlock { 021 private $db; // reference to the database holding the Table. 022 private $table; // name of table to update 023 private $command; // a single character (i,r,u,d) denotes the command. 024 private $sqlStr; // main body of the SQL string. 025 private $valStr; // the Value part of an INSERT Statement. 026 private $whereStr; // the WHERE clause for UPDATE and DELETE 027 private $limit; // optional limit on rows updated. 028 private $colCnt=0; // number of columns in the query. 029 030 // private $colArr = array(); // Multidimensional Array defines columns and validation. 031 private $dataArr = array(); // holds the data for the statement. 032 // translates the $command character into a SQL command. 033 private $actions = array('i'=>'INSERT INTO ','u'=>'UPDATE ', 'd'=>'DELETE FROM ','r'=>'REPLACE INTO '); 034 035 /** 036 * __construct defines the table and command for the SQL Statement. 037 * @PARAM string $cmd is 1st letter of 'I'NSERT, 'R'EPLACE, 'U'PDATE or 'D'ELETE 038 * NOTE: I like pass the whole word to make my program clear. 039 * @PARAM string $table is the name of the Table to modify. 040 * @PARAM string $db is the database containing the table. 041 */ 042 function __construct ($cmd, $table, $db=DB_MAIN) { 043 $this->command = strtolower(substr($cmd,0,1)); 044 if (!isset($this->actions[$this->command])) msgError('Invalid command '.$cmd); 045 $this->table = $table; 046 $this->db = $db; 047 $sqlStr = ''; // This is the main SQL Statement 048 $valStr = ''; // The VALUES section for INSERT and REPLACE. 049 $whereStr=''; // The WHERE string for an UPDATE Statement. 050 $this->colCnt=0; 051 $this->limit=null; // You can limit affected rows of an UPDATE statementi 052 } 053 /** 054 * The first step is to add data to the object. 055 * addValue() is the 1st of 4 similar functions. It adds a single data item. 056 * NOTE: addColumn() function defines and adds a column in the same step. 057 * @PARAM string $col is the key to the internal array & name of SQL Column. 058 * @PARAM mixed $val is the value of the column. 059 */ 060 public function addValue($col,$val) { 061 $this->dataArr[$col] = $val; 062 } 063 /** 064 * addArray() will add each element of an associative array to the query 065 * @PARAM array $arr is an associative array. 066 */ 067 public function addArray($arr) { 068 reset($arr); 069 while (list($key, $nval) = each($arr)) { 070 $this->dataArr[$key] = $nval; 071 } 072 } 073 /** 074 * addPost() adds the value in the $_POST variable to the internal data store. 075 * it is equivalent to addArr($_POST). 076 */ 077 public function addPost() { 078 reset($_POST); // make sure we are at beginning of $_POST 079 while (list($key, $nval) = each($_POST)) { 080 $this->dataArr[$key] = $nval; 081 } 082 } 083 084 /** 085 * defColumn() defines a column for query and validates the data. 086 * NOTE, you must add the data before defining the column! 087 * @PARAM string $colName is the name of the column in the Table. 088 * @PARAM string $type is the data type of the column 089 * @PARAM boolean $required indicates the field must not be empty. 090 * @PARAM mixed $w_or_len is either 'w' indicating that variable is part of 091 * the key in WHERE clause or maximum length of the column. 092 */ 093 public function defColumn($colName, $type, $required=false, $w_or_len=0) { 094 // type can be str, int, float, dec, date, ts (datetime) or lob (blob). 095 // translates to int, str, dec, float 096 if ($type == 'blob') $type = 'lob'; 097 if ($type == 'datetime') $type = 'ts'; 098 099 // make sure the variable is set. 100 if (!isset($this->dataArr[$colName])) $this->dataArr[$colName] = ''; 101 if ($required and $this->dataArr[$colName] == '') { 102 // msgError returns an error message and sets the hasError flag to true. 103 msgError($colName.' is a required field'); 104 } 105 // Older databases used fixed length strings (fixed as in number of bytes). 106 // UTF8 is a multibyte character set making string length confusing. 107 // Many databases, like SQLite3, ignore defined string length. 108 109 if ($w_or_len === 'w') { 110 // This is part of key in WEHRE clause. I don't have to check maxlen. 111 $maxLen = 0; 112 $this->whereStr .= ($this->whereStr == '')? ' WHERE ' : ' and '; 113 $this->whereStr .= $colName.'=:'.$colName; 114 // raise error if you add a where clause to insert. 115 if (in_array($this->command,['i','r'])) msgError('Defined WHERE clause on INSERT'); 116 } else { 117 // $w_or_len has a length value. 118 $maxLen = (int) $w_or_len; 119 $delimiter = ($this->sqlStr == '')? '' : ', '; 120 if ($maxLen > 0) { 121 // NOTE MySQL uses VARBINARY and BLOB data types for UTF8 multibyte strings. 122 $len = ($type=='lob')? mb_strlen($this->dataArr[$colName]) : strlen($this->dataArr[$colName]); 123 if ($len > $maxLen) msgError('The length of <b>'.$colName.'</b> ("'.$this->dataArr[$colName].'") exceeds its maximum length of '.$maxLen); 124 } 125 // build the SQL. 126 if ($this->command == 'd') { 127 msgError('A DELETE Statement should only have a WHERE clause'); 128 } elseif ($this->command == 'u') { 129 $this->sqlStr .= $delimiter.$colName.'=:'.$colName; 130 } else { 131 $this->sqlStr .= $delimiter.$colName; 132 $this->valStr .= $delimiter.':'.$colName; 133 } 134 } 135 } 136 /** 137 * addColumn() will call defColumn and add a value to dataArr. 138 * @param string $colName is the name of the column. 139 * @param string $val is the value of the colunn 140 * @param string $type is the datatype of the colunm 141 * @param boolean indicates if the column is required. 142 * @param mixed $w_len is either max_len for column or "w" to indicate where/ 143 */ 144 public function addColumn($colName, $val, $type='str', $required=false, $w_or_len=0) { 145 if ($colName != '') { 146 $this->dataArr[$colName] = $val; 147 $this->defColumn($colName, $type, $required, $w_or_len); 148 } 149 } 150 /** 151 * addBlock() will take a delimited string and add a block of columns. 152 * The program calls defColum() with each row in the block. 153 * @PARAM string $block is a formatted block defining multiple columns 154 * @PARAM string $delim is the data elimiter for the block. 155 * @PARAM string #lineDelim is the line delimiter for the block. 156 */ 157 public function addBlock($block,$delim=',', $lineDelim = "\n") { 158 $tok = strtok($block, $lineDelim); 159 while ($tok !== false) { 160 list($colName, $type, $req, $w_or_len) = explode($delim,rtrim($tok)); 161 $required = in_array(strtolower($req),['y','t','1','true','yes','required'.'req']); 162 $this->defColumn($colName, $type, $required, $w_or_len); 163 $tok = strtok($lineDelim); 164 } 165 } 166 /** 167 * getSQL() produces the SQL with marked variables. 168 * @return program returns a SQL statement. 169 */ 170 public function getSQL() { 171 $rv = ''; 172 if ($this->command == 'd') { 173 $rv = ($whereStr != '')? '' : 'DELETE FROM '.$this->table.' WHERE '.$whereStr; 174 } elseif ($this->command == 'u') { 175 if ($this->whereStr != '' and $this->sqlStr != '') { 176 $rv = 'UPDATE '.$this->table.' SET '.$this->sqlStr.$this->whereStr; 177 } 178 } elseif ($this->command == 'i') { 179 if ($this->sqlStr != '') { 180 $rv = 'INSERT INTO '.$this->table.' ('.$this->sqlStr.') VALUES ('.$this->valStr.')'; 181 } 182 } elseif ($this->command == 'r') { 183 if ($this->sqlStr != '') { 184 $rv = 'REPLACE INTO '.$this->table.' ('.$this->sqlStr.') VALUES ('.$this->valStr.')'; 185 } 186 } 187 return $rv; 188 } 189 /** 190 * getSQLwithData() will get the SQL with corresponding data. 191 * @return program returns SQL statement loaded with data. 192 */ 193 function getSQLWithData() { 194 $sql = $this->getSQL().' '; 195 $rv = ''; 196 $cnt = strlen($sql); 197 $inSQL = true; 198 $key = ''; 199 // parser loops through the sql character by character. 200 for ($i=0; $i<$cnt; $i++) { 201 if ($inSQL) { 202 if ($sql[$i] == ':') { 203 // start of data definition. 204 $inSQL = false; 205 } else { 206 $rv .= $sql[$i]; 207 } 208 } else { 209 // a key def might end in a space, colon or closed parathesis. 210 if (in_array($sql[$i],[',',' ',')'])) { 211 // end of column data. 212 if (isset($this->dataArr[$key])) { 213 // kludge: "^" tells dbConn to called PDO::quote. 214 $rv .= dbConn($this->db,'^'.$this->dataArr[$key]).$sql[$i]; 215 } else { 216 // data not found. This should not happen. 217 $rv .= ':'.$key; 218 } 219 $key = ''; 220 $inSQL = true; 221 } else { 222 $key .= $sql[$i]; 223 } 224 } 225 } 226 return $rv; 227 } 228 229 /** 230 * checkLimit() will check to see how many rows will be updated by the 231 * WHERE clause. 232 * @param integer $limit is the maximum rows to update. 233 * @param string $errMsg is the error message to produce if limit exceeded. 234 * 'def' produces a default message. Blank produces no message. 235 */ 236 function checkLimit($limit=1, $errMsg='def') { 237 $rv = -1; 238 if (!in_array($this->command,['d', 'u'])) { 239 msgNote('This is not an UPDATE statement.'); 240 } elseif ($this->whereStr != '') { 241 msgNote('There is no WHERE clause defined.'); 242 } else { 243 $sqlCnt = sqlValue('SELECT count(*) FROM '.$this->table.$this->where,$whereArr,$this->db); 244 if ($rv > $limit) { 245 if ($errMsg=='def') $errMsg = 'SQL Error: WHERE clause affects too many rows: ('.$sqlCnt.')'; 246 if ($errMsg != '') msgError($errMsg); 247 } else { 248 $rv = $sqlCnt; 249 } 250 } 251 return $rv; 252 } 253 // use "#ID" in success message to include rows affected in message. 254 private function log($logFile) { 255 if ($logFile == 'y') { 256 $logFile = 'y'.substr($table,0,12).'.log'; 257 file_put_contents (LOG_DIR.$logFile, strtr($this->command.$this->innards,NL,' ').NL, FILE_APPEND); 258 } else { 259 file_put_contents (LOG_DIR.$logFile, strtr($this->command.' '.$this->table.' '.$this->innards,NL,' ').NL, FILE_APPEND); 260 } 261 } 262 /** 263 * exec() calls sqlExec with built statement to execute the SQL 264 * @param string $successMsg is displayed on success. 265 * @param string $errMsg is displayed on error. 266 * @param string $logFile will hold error messages for admin. 267 */ 268 public function exec($successMsg='',$errMsg='',$logFile='') { 269 270 $rv = -1; 271 if (isOkay()) { 272 $rv = sqlExec($this->getSQL(),$this->dataArr,$this->db,$successMsg, $errMsg); 273 } 274 // if (strtolower($log) != '') $this->log($logFile); 275 return $rv; 276 } 277 } 278 ?>
// End Require
013 014 ?> 015 <head> 016 <meta charset="UTF-8" /> 017 <meta name="viewport" content="width=device-width, initial-scale=1" /> 018 <meta name="Author" content="Kevin Delaney" /> 019 <meta name="keywords" content="dynamic sql in php" /> 020 <meta name="description" content="sqlBlock builds a SQL statement and validates data from the $_POST array." /> 021 <title>Test Page for sqlBlock</title> 022 <link rel="canonical" href="https://yintercept.com/resources/sqlBlock.php" /> 023 <link rel="stylesheet" href="rm.css" type="text/css"> 024 </head> 025 <h1 id="pageTitle"><a href="https://yintercept.com/resources/">Resource Model</a></h1> 026 <!-- The most critical part of any web page is the Ad!, without the ad we perish --> 027 <div class="ad"> 028 <script async src="//pagead2.googlesyndication.com/pagead/js/adsbygoogle.js"></script> 029 <!-- yintercept - responsive goodle ad --> 030 <ins class="adsbygoogle" 031 style="display:block" 032 data-ad-client="ca-pub-7057064824800338" 033 data-ad-slot="4504818749" 034 data-ad-format="auto"></ins> 035 <script> 036 (adsbygoogle = window.adsbygoogle || []).push({}); 037 </script> 038 </div> 039 <body> 040 <div class="main"> 041 <h2>sqlBlock Test Page</h2> 042 <p>This page shows tests of the sqlBlock Object (<a href="https://yintercept.com/resources/view.php?script=14">View Source</a>). NOTE, this public test prints out the SQL produced from the statement. I decided not to put the page that tests the execution of this SQL on this page.</p> 043 <?php 044 /** 045 * Let's test some of the features of sqlBlock. We will create some test data. 046 * These tests only produce statements. 047 */ 048 $testArr = ['one'=>1, 'two'=>2,'three'=>'Third Item','four'=>'Fourth Item']; 049 echo '<h3>Test INSERT</h3>'; 050 echo '<p>This is the test of an INSERT statement:</p>'; 051 // Step One: Define statement type and Table in constructor 052 $sb = new sqlBlock('i','Test_Table'); 053 // Add the Data: 054 $sb->addArray($testArr); 055 // Define the columns 056 $sb->addBlock('one,int,y,10 057 two,int,y,10 058 three,str,y,24 059 four,str,y,24'); 060 // Get the Resulits: 061 echo '<p>'.$sb->getSQLWithData().'</p>'; 062 $sb=null; 063 msgHTML(); // show any errors 064 // Let's Try Same Thing with REPLACE 065 echo '<h3>Test REPLACE</h3>'; 066 echo '<p>This is the test of a REPLACE statement:</p>'; 067 // Step One: Define statement type and Table in constructor 068 // Note, I decided to spell out the who word. It should still work. 069 $sb = new sqlBlock('REPLACE','Test_Table'); 070 // Add the Data: 071 $sb->addArray($testArr); 072 // Define the columns 073 $sb->addBlock('one,int,y,10 074 two,int,y,10 075 three,str,y,24 076 four,str,y,24'); 077 // Get the Resulits: 078 echo '<p>'.$sb->getSQLWithData().'</p>'; 079 $sb=null; 080 msgHTML(); 081 // Let's Try Same Thing with UPDATE 082 echo '<h3>Test UPDATE</h3>'; 083 echo '<p>This is the test of a UPDATE statement:</p>'; 084 // Step One: Define statement type and Table in constructor 085 // Note, I decided to spell out the who word. It should still work. 086 $sb = new sqlBlock('u','Test_Table'); 087 // Add the Data: 088 $sb->addArray($testArr); 089 // Define the columns 090 // NOTE, I changed the block to indicate the first columns are part of the 091 //where clause. 092 $sb->addBlock('one,int,y,w 093 two,int,y,w 094 three,str,y,24 095 four,str,y,24'); 096 // Let's add a fifth column for old times sake. 097 $sb->addColumn('five','Fifth Column','str',true); 098 099 // Get the Resulits: 100 echo '<p>Statement: '.$sb->getSQLWithData().'</p>'; 101 $sb = null; 102 msgHTML(); 103 // Let's Delete some data and trigger an error. 104 echo '<h3>Test Required</h3>'; 105 echo '<p>This is the test of the required constraint. The test creates the SQL with a missing piece of data. There should be an error preventing its execution.</p>'; 106 // Step One: Define statement type and Table in constructor 107 // Note, I decided to spell out the who word. It should still work. 108 $sb = new sqlBlock('u','Test_Table'); 109 $testArr['two'] = null; // delete some data 110 // Add the Data: 111 $sb->addArray($testArr); 112 // Define the columns 113 // NOTE, I changed the block to indicate the first columns are part of the 114 //where clause. 115 $sb->addBlock('one,int,y,w 116 two,int,y,w 117 three,str,y,24 118 four,str,y,24'); 119 120 // Get the Resulits: 121 echo '<p>Statement: '.$sb->getSQLWithData().'</p>'; 122 msgHTML(); // This should print an error message. 123 echo '<pre> 124 This var_dump() shows the data from the object 125 '; 126 var_dump($sb); 127 echo '</pre>'; 128 msgHTML(); 129 ?> 130 </div> 131 </html> 132 </body>

Use "view source" from your browser to grab the output. Feel free to link to this project and check out the Resource Model for information on PHP coding or my tumblr blog for picture of Arizona, Colorado or Utah.

File last modified at February 05 2016 22:03:54.. This page has been viewed 1160 Times.

blog ~ Resource Model ~ links