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: sql.php PDO Wrapper - Test

Demonstration of a function that wraps the PDO database.

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.

sql.php PDO Wrapper - Test

001 <!DOCTYPE html> 002 <html lang="en"> 003 <head> 004 <meta charset="UTF-8" /> 005 <meta name="viewport" content="width=device-width, initial-scale=1" /> 006 <meta name="Author" content="Kevin Delaney" /> 007 <meta name="keywords" content="pdo connection, php database connection" /> 008 <meta name="description" content="This is the test page for the sql.php object which I use to contain the PHP PDO Object." /> 009 <title>Test Page for sql.php</title> 010 <link rel="canonical" href="https://yintercept.com/resources/sqlTest.php" /> 011 <link rel="stylesheet" href="rm.css" type="text/css"> 012 </head> 013 <body> 014 <div class="main"> 015 <h2>sql Test Page</h2> 016 <p>People often confuse the term "object" and "class." Originally, the term "object design" simply meant grouping procedures and data into a cohesive structures called "objects." The "class" contruct was created for an implementation of Object Oriented Programming called "C for Classes" by Bjarne Stroustrup. C++ was optimized for creating GUI interfaces on desktop computers.</p> 017 <p>The World Wide Web is not a desktop computer. The rigid class structure adopted by PHP impedes development; So, I created a free form object which I placed in the sql.php file. I call this approach <a href="https://yintercept.com/resources/">The Resource Model</a>.</p> 018 <p>This is a test page for sql.php. Use the Code Viewer to see the test. The Test Results are below the dotted line.</p> 019 <hr style="border: 2px dotted #000"> 020 <h3>Test Results</h3> 021 <p>These are tests results for the free form sql object. NOTE: this object requires the <a href="https://yintercept.com/resources/msgTest.php">msg Notification Object</a>.</p> 022 <?php 023 /** 024 * sqlTest.php is a test script for the sql.php object. 025 * Yes, even though it doesn't use a rigid class structure, it is an object. 026 * This link shows the test results: 027 * @see https://yintercept.com/resources/sqlTest.php 028 * 029 * The sql.php object uses the msg.php notification object to report errors. 030 * This scription requires the msg.php object: 031 * @see https://yintercept.com/resources/msgTest.php 032 * 033 * @copyright 2015 kd 034 */ 035 include('/var/www/php/msg.php');
require('/var/www/php/sql.php');
001 <?php 002 003 /** 004 * The sql functions connect with the database using PDO and PDOStatement. 005 * NOTE, you will need to manually edit dbConn to configure the connections. 006 * 007 * @package ResourceModel 008 * @copyright 2002-2016 Kevin Delaney 009 * @license https://yintercept.com/resources/license.html 010 * @see https://yintercept.com/resources 011 * @see https://yintercept.com/resources/view.php?script=1 Source Code 012 * @version 0.0.10 013 * 014 * These are the primary functions: 015 * sqlValue() returns a single value from the database 016 * sqlRow() returns a single row from the database. 017 * sqlArr() returns a 2D array with full result set. 018 * sqlLoop @see https://yintercept.com/resources/view.php?script=10 019 * dbConn() holds the PDO Connect object. It has some additional functions: 020 * Call it with DB_BEGIN, DB_ROLLBACK & DB_COMMIT for transactions 021 * "+dbname" will run an 'ATTACH DATABASE' command 022 * DB_INSERT_ID will return the last insert id 023 * The function returns a PDOStatement if you want one of those. 024 * 025 */ 026 027 const SDB_PATH = '/var/www/db/'; // Location of my SQLite3 databases. 028 const DB_MAIN = 'main'; // name of your primary database 029 // dbConn will process these transactions 030 const DB_INSERT_ID =1; // returns last insert id 031 const DB_BEGIN = 3; // Begin a transaction 032 const DB_ROLLBACK = 4; // rollback a database transaction 033 const DB_COMMIT = 5; // commit a database transaction 034 const DB_CLOSE = 6; // closes connection, logs stats & returns dbCnt. 035 const DB_CNT = 7; // dbConn returns statement count. 036 const DB_ERRORS = 666; // dbConn returns error info. 037 038 // used by sqlRow() 039 const DB_CHK = 100; 040 const DB_MAX_ROWS = 2000; 041 042 // directives for sqlExec 043 const DB_ONE_MAX = 64; // rollback if more than one row updated 044 const DB_ROWCOUNT = 10; // return the row count 045 const DB_LAST_ID = 1; // same as DB_INSERT_ID 046 // include('/var/www/php/cnx.php'); 047 /** 048 * dbConn is a database connection factory that maintains an array of 049 * connections and returns PDOStatements to sql requests. 050 * Currently, I am hard coding the connectson in deConn. 051 * in a future release, connection information will be in an array 052 * 053 * @param string $sql is either SQL command or a short cut code. 054 * @param string $dbi identifies the database to use. 055 * @return mixed[] returns a PDOStatement for SQL command or info related to call. 056 */ 057 058 function dbConn($dbi,$sql) { 059 static $dbh = array(); // array holds the PDO objects. 060 static $dbCnt = 0; // counts calls to the database 061 static $dbTrace = ''; // holds a trace string. 062 /** register your conections here. The values of the array are: 063 * @param string dbnam -- give each db a unique name. Use 'main' for default. 064 * @param integer status starts as 0. Is 1 if connected and -1 if failed. 065 * @param string dsn is the Data Name Source for the connection 066 * @param string user is the database user name 067 * @param string pwd is the password. 068 */ 069 static $connArr = array( 070 DB_MAIN=>['status'=>0,'dsn'=>'sqlite:/var/www/db/main.db','user'=>'','pwd'=>''], 071 'log'=>['status'=>0,'dsn'=>'sqlite:/var/www/db/log.db','user'=>'','pwd'=>''], 072 'dir'=>['status'=>0,'dsn'=>'sqlite:/var/www/db/dir.db','user'=>'','pwd'=>''] 073 ); 074 // I've defined four databases. ele is hosted by http://www.elephantsql.com 075 // the last database is a mistake ... used to test db failures. 076 077 $rv = false; 078 $dbCnt++; 079 $stmt = false; 080 081 try { 082 // verify dbi exists in the index. 083 if (isset($connArr[$dbi])) { 084 if ($connArr[$dbi]['status'] == -1) throw new Exception('No Database Connection.'); 085 } else { 086 throw new Exception('Connection "'.$dbi.'" does not exist.'); 087 } 088 if ($connArr[$dbi]['status']==0) { 089 // connect to the database 090 try { 091 $dbh[$dbi] = new PDO($connArr[$dbi]['dsn'],$connArr[$dbi]['user'],$connArr[$dbi]['pwd']); 092 $connArr[$dbi]['status'] = 1; 093 } catch(PDOException $e) { 094 // record error and set DB_ACTIVE to DB_NONE. 095 $connArr[$dbi]['status'] = -1; 096 // log database connection faxlure in a file 097 file_put_contents(SDB_PATH.'pdoerr.txt', $dbi.' //'.$_SERVER['REQUEST_TIME'].' '.$e->getMessage(), FILE_APPEND | LOCK_EX); 098 throw new Exception('Database connection '.$dbi.' failed.'); 099 } 100 } 101 // We can add little short cuts here. such as +str Attaches a database 102 if (substr($sql,0,1)=='+') { 103 if (substr_count($sql,' ')==0) { 104 $asql = 'ATTACH DATABASE '.$dbh[$dbi]->quote(SDB_PATH.substr($sql,1).'.db').' AS '.$dbh[$dbi]->quote(substr($sql,1)); 105 $dbh[$dbi]->exec($asql); 106 $dbTrace+='A'; 107 $rv = true; 108 } 109 } elseif (substr($sql,0,1) == '^') { 110 $rv = $dbh[$dbi]->quote(substr($sql,1)); 111 } elseif ($sql==DB_INSERT_ID) { 112 $rv = $dbh[$dbi]->lastInsertId(); 113 } elseif ($sql==DB_ERRORS) { 114 $rv = implode('|',$dbh[$dbi]->errorInfo()); 115 } elseif ($sql == DB_CNT) { 116 $rv = --$dbCnt; // return current dbCnt (minus this call) 117 } elseif ($sql==DB_CLOSE) { 118 $dbh[$dbi]=null; 119 $dbErrors=false; 120 $rv=$dbCnt; 121 // store a trace of page for later analysis. 122 // $page_id = 0; // will populate later. 123 msgLog('dbTrace',[0,$dbTrace]); 124 } elseif ($sql==DB_BEGIN) { 125 if ($dbh[$dbi]->inTransaction()) { 126 msgComment($dbi.' is already in transaction mode.'); 127 } else { 128 $dbh[$dbi]->beginTransaction(); 129 } 130 $rv = true; 131 } elseif ($sql==DB_ROLLBACK) { 132 if ($dbh[$dbi]->inTransaction()) { 133 $dbh[$dbi]->rollBack(); 134 } else { 135 msgComment('Rollback outside a transaction'); 136 } 137 $rv = true; 138 } elseif ($sql==DB_COMMIT) { 139 if ($dbh[$dbi]->inTransaction()) { 140 $dbh[$dbi]->commit(); 141 } else { 142 msgComment('Attempting to commit outside transaction.'); 143 } 144 $rv = true; 145 } else { 146 // return an unexecuted prepared statement to call procedure. 147 $dbTrace.=substr($sql,0,1); // add first letter of command to trace. 148 $rv = $dbh[$dbi]->prepare($sql); 149 } 150 } catch (Exception $e) { 151 msgError('PDO says: '.$e->getMessage()); 152 $rv = false; 153 } 154 return $rv; 155 } 156 157 /** 158 * sqlValue returns a single value from the database. 159 * @param string $sql is a SQL SELECT command 160 * @param array $arr holds parameters for the SQL command 161 * @return string The function returns the first column of first row of the result 162 */ 163 164 function sqlValue($sql,$arr=[],$dbi=DB_MAIN) { 165 $stmt = dbConn($dbi,$sql); 166 // return an array of zeros on failure. 167 $rv = ''; // returns a blank space on error 168 if (is_object($stmt)) { 169 if (!is_array($arr)) { 170 $tst = $arr; 171 // no need to get huffy is some forgot the brackets to make it an array. 172 if (is_string($tst) or is_numeric($tst)) { 173 $arr=[$tst]; 174 // msgComment('sqlValue - string to array'); 175 } else { 176 msgError('In valid parameter for sqlValue()'); 177 } 178 } 179 if ($stmt->execute($arr)) { 180 $row = $stmt->fetch(PDO::FETCH_NUM); 181 $rv = (isset($row[0]))? $row[0] :''; 182 } else { 183 msgError('sqlValue execute failed'); 184 msgComment($sql.'<br />Parameters = '.implode('|',$arr)); 185 } 186 } else { 187 // this should only happen with a bad SQL statement 188 msgError('sqlValue() call failed. call #'.dbConn(DB_MAIN,DB_CNT)); 189 msgComment($dbi.' '.$sql); 190 // msgComment('RV Datatype is '.gettype($stmt)); 191 } 192 return $rv; 193 } 194 195 /** sqlRow() retruns a row for a SQL command. The command buffers PDOStatement 196 * You can get multiple rows and use function in some loops 197 * Use sqlLoop for complex loops. 198 * @param string $sql is a SQL select command. If null; returns next row of last 199 * command. If $sql==DB_CHK it checks to see if their is a next row. 200 * @param array $arr holds variables for the SQL statement 201 * @param integer $fetchStyle determines the PDO fetch style 202 * @return mixed[] Function returns an array for sql calls or boolean for chk. 203 */ 204 205 function sqlRow($sql=null,$arr=null,$dbi=DB_MAIN,$fetchStyle=PDO::FETCH_NUM) { 206 static $stmt=null; 207 static $style = ''; 208 static $chkCnt = 0; 209 static $chkVal = false; 210 211 212 $rv = [false,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0]; 213 if ($sql == DB_CHK) { 214 $rv = ($chkCnt++ > DB_MAX_ROWS )? false : $chkVal; 215 } elseif ($sql == null) { 216 // get the next row from the existing counter. 217 $rv = $stmt->fetch($fetchStyle); 218 if ($rv===false) { 219 $rv = ($fetchStyle==PDO::FETCH_OBJ)? null : [false,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0]; 220 $chkVal = false; 221 } else { 222 $chkVal = true; 223 } 224 } else { 225 // get and execute a PDOStatement 226 $stmt = dbConn($dbi,$sql); 227 $style = $fetchStyle; 228 $chkVal=false; 229 if (gettype($stmt) == 'object') { 230 if ($stmt->execute($arr)) { 231 $rv = $stmt->fetch($fetchStyle); 232 if ($rv===false) { 233 $rv = ($fetchStyle==PDO::FETCH_OBJ)? null : [false,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0]; 234 } else { 235 $chkVal = true; 236 } 237 } else { 238 $stmt = null; 239 msgError('SQL execute failed'); 240 msgComment($sql); 241 msgComment('Parameters = '.implode('|',$arr)); 242 } 243 } else { 244 // this should only happen with a bad SQL statement 245 msgError('SQL call failed.'); 246 msgComment($sql); 247 msgComment('RV Datatype is '.gettype($stmt)); 248 } 249 } 250 return $rv; 251 } 252 253 254 /** 255 * sqlAll() returns the entire result set as a two dimensional array. 256 * @param string $sql is a SQL SELECT Statement 257 * @param array The $arr array holds values for for the SQL statement 258 * @param integer $fetchStyle is a PDO::FETCH style option 259 * @param array function always returns an array. 260 */ 261 262 function sqlAll($sql,$arr,$dbi=DB_MAIN,$fetchStyle=PDO::FETCH_NUM) { 263 $stmt = dbConn($dbi,$sql); 264 // return an array of zeros on failure. 265 $rv = array(); // returns a blank space on error 266 if (is_object($stmt)) { 267 if ($stmt->execute($arr)) { 268 // I break out of the function to avoid making an extra copy of results 269 return $stmt->fetchAll($fetchStyle); 270 } else { 271 msgError('SQL execute failed'); 272 msgComment($sql.'<br />Parameters: '.implode('|',$arr)); 273 } 274 } else { 275 // this should only happen with a bad SQL statement 276 msgError('SQL call failed.'); 277 msgComment($sql.'<br />RV Datatype is '.gettype($stmt)); 278 } 279 // successful calls return results straignt from driver. 280 return array(); // returns empty array on failure 281 } 282 /** 283 * sqlExec() execute a DML SQL command such as INSERT OR UPDATE 284 * @param string $sql is the SQL command to upddate 285 * @param array $arr contains variables for the SQL 286 * @param integer $directive determines the output of the command The default 287 is to return a row. DB_LAST_ID returns the last insert id. 288 DB_ONE_MAX rollsback transacation if it affects more than one row. 289 * @param string $successMsg is printed on cuccesful execution. 290 Program replaces %ID with insert id and %RS or %RC with row count. 291 * @param string $failureMsg is printed on failure of the statement 292 * @output db Updates the database 293 * @return either the row count or insert id based on $directive 294 */ 295 296 function sqlExec($sql,$arr,$dbi=DB_MAIN,$successMsg='',$failureMsg='',$directive=0) { 297 if ($directive==0) $directive = (substr($sql,0,6) == 'INSERT')? DB_INSERT_ID : DB_ROWCOUNT; 298 if ($directive == DB_ONE_MAX) dbConn($dbi,DB_BEGIN); 299 $stmt = (msgOkay())? dbConn($dbi,$sql) : 123; 300 $rv = 0; // returns rows affected. 301 if (is_object($stmt)) { 302 if ($stmt->execute($arr)) { 303 // prepare message. 304 $rv = $stmt->rowCount(); // rowcount is the default return value. 305 $rowStr = $rv.' rows'; 306 if ($directive == DB_ONE_MAX) { 307 if ($rv > 1) { 308 msgError('SQL Warning. '.$rowStr.' affected on single row query.<br />Rolling back transaction.'); 309 dbConn($dbi,DB_ROLLBACK); 310 msgComment('Rolled back: '.$sql); 311 312 } else { 313 dbConn($dbi,DB_COMMIT); 314 } 315 } 316 if ($rv == 0) { 317 $rowStr = 'no rows'; 318 } elseif ($rv == 1) { 319 $rowStr = '1 row'; 320 } 321 $insertId = dbConn($dbi,DB_INSERT_ID); 322 if ($successMsg != '') msgNote(str_replace(['%ID','%RS','%RC'],[$insertId,$rowStr,$rv],$successMsg)); 323 if ($directive == DB_INSERT_ID) $rv = $insertId; 324 } else { 325 msgError('SQL Exec: '.$failureMsg); 326 msgComment($sql.'<br />Parameters = '.implode('|',$arr).'<br />Error '.implode('|',$stmt->errorInfo())); 327 } 328 } elseif ($stmt== 123) { 329 // msgOkay reported an error. 330 } else { 331 // this should only happen with a bad SQL statement 332 msgError('SQL Call: '.$failureMsg); 333 msgComment($sql.'<br />'.implode(',',$arr).'<br />Return Datatype is '.gettype($stmt)); 334 msgComment('DB Error '.dbConn($dbi,DB_ERRORS)); 335 } 336 return $rv; 337 } 338 /** 339 * I use a fair number of sequences which I maintain in a file called Seq_Def 340 * @param string seq_nm is the name of the sequence. 341 * @param boolen if true, wrap calls in BEGIN/COMMIT Transaction 342 * @return integer is -1 on failure or incremented sequence 343 */ 344 function getSeq($seq_nm,$commit=true) { 345 $rv = -1; 346 if ($commit) dbConn(DB_MAIN,DB_BEGIN); 347 $seq=sqlValue('SELECT seq+1 FROM Seq_Def WHERE seq_nm=?',[$seq_nm]); 348 if ($seq>0) { 349 if (sqlExec('UPDATE Seq_Def SET seq=? WHERE seq_nm=?',[$seq,$seq_nm])==1) { 350 $rv=$seq; 351 } else { 352 msgError('Failed to fetch sequence <b>'.$seq_nm.'</b>'); 353 } 354 } else { 355 msgError('Sequence <b>'.$seq_nm.'</b> is undefined.'); 356 } 357 if ($commit) dbConn(DB_MAIN,DB_COMMIT); 358 return $rv; 359 } 360 361 362 363 ?>
// End Require
036 037 $timeArr=array(); 038 $timeArr[]=['Finish Setup', microtime(true)]; 039 $GLOBALS['msg']['debugMode'] = true; // display the debug messages. 040 echo '<h3>sqlValue Test</h3>'; 041 echo '<p>The first test will use sqlValue to draw this sites name from the main database. I will format the link in sqLite3:</p>'; 042 // Since sqlValue outputs a string I can embed it in a string. 043 echo '<p>My Site Name is: '.sqlValue('SELECT site_nm FROM Web_Site WHERE site_id=?',[0]).'. '; 044 // In this second test. I format a URL in SQL and display it inline. 045 // I have to escape the quotes for PHP 046 $sql="SELECT '<a href=\"'||domain_nm||'\">'||subject_nm||'</a>' as 'Link' 047 FROM Web_Site WHERE site_id=?"; 048 echo 'I was born in '.sqlValue($sql,36).'. '; 049 // Notice how I forgot to put braces around 36. sqlValue fixed my mistake. 050 // I will use sqlRow() to extract a two column arrry and build my own link. 051 $sql = 'SELECT domain_nm, subject_nm FROM Web_Site WHERE site_id = ?'; 052 list($site_url, $subject_nm) = sqlRow($sql,[9]); 053 echo 'I am currently serving time in <a href="'.$site_url.'">'.$subject_nm.'</a>.</p>'; 054 echo 'The function getSeq() draws a named sequence from the table. Let\' run getSeq(\'test\') to see if it works: '.getSeq('test').'</p>'; 055 echo '<p>I often make crazy typos when I am programming. I will make a bad SQL statement and use msgNote() to display the error. You should see an error message followed by debug note.</p>'; 056 $timeArr[]=['sqlValue Test', microtime(true)]; 057 sqlValue('SELECT important_info FROM Crazy_Typo WHERE id = ?',[1]); 058 msgHTML(); 059 $timeArr[]=['Typo Test [rem]', microtime(true)]; 060 // reset the the error toggle. 061 $GLOBALS['msg']['isOkay'] = true; 062 echo '<p>In the next test, I will attach the code database and see how many times people have viewed the <a href="https://yintercept.com/resources/view.php?script=3">sql Code Viewer Page</a>.'; 063 // the + call to dbConn attaches a local SQLLite3 database. 064 dbConn(DB_MAIN,'+code'); 065 echo ' It\'s been viewed '.sqlValue('SELECT hit_cnt FROM code.Code_Viewer WHERE code_id=?',[3],DB_MAIN).' times.</p>'; 066 msgHTML(); 067 $timeArr[]=['Attach DB Test', microtime(true)]; 068 echo '<h3>Test sqlAll() Against a Romote Database</h3>'.PHP_EOL; 069 echo '<p>The next code will test sqlAll(). This funnction selects an entire array. To make the test interesting, I created a remote postgres database with <a href="https://www.elephantsql.com/">elephantSQL.com</a>. This data is stored several hundred miles aways.</p>'; 070 // I registered a remode DB with elephantSQL.com which has the local name ele. 071 $arr=sqlAll('SELECT * FROM Test_Table ORDER BY test_id LIMIT 3',[],'ele'); 072 // let's make a quick table. 073 $rCnt = count($arr); 074 echo '<table style="margin: 4px auto; padding: 4px"> 075 <tr style="bottom-border: 2px solid #000"><th>test_id</th><th>test_nm</th><th>hit_cnt</th></tr>'.PHP_EOL; 076 for ($i=0; $i<$rCnt; $i++) { 077 echo '<tr><td>'.$arr[$i][0].'</td><td style="border-right: 2px solid #000; border-left: 2px solid #000">'.$arr[$i][1].'</td><td>'.$arr[$i][2].'</td></tr>'.PHP_EOL; 078 } 079 echo '</table>'; 080 echo '<h3>Updating with sqlExec()</h3>'; 081 echo '<p>Wow, that was fun. I can access data locally and across the country with the same driver. I know. In this next test, I will use sqlExec() to increment the counter in row two.</p>'; 082 // the directive tells sqlExec what to return. The default is to return rowcount 083 sqlExec('UPDATE Test_Table SET hit_cnt = hit_cnt+1 WHERE test_id=?',[2],'ele','Updated %RS on remote table.','Error updating remote table'); 084 echo '<p><b>msgNote</b>() will tell us if the operation was successful. I love msgNote. I run it after every sql statement when I am writing new code. It should say we updated one row.</p>'.PHP_EOL; 085 msgHTML(); 086 echo '<p>sqlValue() says the value of row two is now: '.sqlValue('SELECT hit_cnt FROM Test_Table WHERE test_id = ?',[2],'ele').'.</p>'.PHP_EOL; 087 $timeArr[]=['Remote Database Test', microtime(true)]; 088 echo '<p>The DB_ONE_MAX directive tells sqlExec to rullback if it updates more than one entry. I try to update three rows. It should rollback. The test should scream a warning at me, then a debug message showing my buggy script.</p>'; 089 sqlExec('UPDATE Test_Table SET hit_cnt = 1 WHERE test_id < ?',[4],'ele','Updated #RS.','multi-update',DB_ONE_MAX); 090 msgHTML(); 091 $GLOBALS['msg']['isOkay'] = true; 092 echo '<p>I will try to rollback and rollback without an active transaction. I will give a warning if the rollback fails and a debug comment if the commit fails.</p>'; 093 dbConn('ele',DB_CLOSE); // close resmote connection. 094 dbConn(DB_MAIN,DB_COMMIT); 095 dbConn(DB_MAIN,DB_ROLLBACK); 096 msgHTML(); 097 echo '<h2>dbConn() as a PDOStatement Factory</h2> 098 <p>The <b>sqlExec</b>() function is not really necessary. A more efficient approach is to draw a prepared PDOStatement directly from <b>dbConn</b>(). The following pseudo code uses two PDOStatements concurrently in a complex loop structure:</p> 099 <pre> 100 $sql = \'UPDATE Table ...\'; // some SQL Statement 101 $sql2 = \'INSERT INTO Table ... \'; // a statement for an inner loop 102 $arr = array(); // appropriate data for SQL. 103 $stmt = dbConn(\'main\',$sql); 104 $inner = dbConn(\'main\',$sql2); 105 while ($condition) { 106 // I create message for outer loop. 107 $arr = [ data for outer loop ]; 108 msgToggle($stmt->execute($arr,\'Success Message\',\'Failure Message\'); 109 while (inner condition) { 110 $innerArr = [ data for inner loop ]; 111 $inner->execute($innerArr); 112 } 113 } 114 </pre> 115 <p>I do not believe that functions are superior to objects. The problem I face is simply that database connection needs to be global; So, I encapsulate the PDO object in a function; so that I can generate PDOStatement Objects at will throughout the program.</p> 116 <h2>In Closing</h2> 117 <p>PHP closes all objects at the end of the script; However it is good practice to close connections.</p> 118 <p>I will now close the connections. This test session executed '.dbConn(DB_MAIN,DB_CLOSE).' sql commands. The next block shows script execution times. The calls to the remote server took up most of the time.'; 119 $timeArr[]=['DB_ONE_MAX & Rollback Test', microtime(true)]; 120 $cnt = count($timeArr); 121 $hold = $_SERVER["REQUEST_TIME_FLOAT"]; 122 echo '<table style="margin: 4px auto"> 123 <tr><th>Break</th><th>Script Time</th><th>Diff</th></tr>'.PHP_EOL; 124 for ($i=0;$i<$cnt;$i++) { 125 echo '<tr><td>'.$timeArr[$i][0]; 126 echo '</td><td style="left-border: 2px solid #000; border-right: 2px solid #000">'.($timeArr[$i][1] - $_SERVER["REQUEST_TIME_FLOAT"]); 127 echo '</td><td>'.(number_format($timeArr[$i][1] - $hold,7)).'</td></tr>'.PHP_EOL; 128 $hold= $timeArr[$i][1];; 129 } 130 echo '</table>'; 131 ?> 132 </div> 133 <p style="text-align: center"><a href="http://blog.yintercept.com" style="color: #ff8">blog</a> 134 ~ <a href="http://CommunityColor.com" style="color: #ff8">Community Color</a> 135 </p> 136 </body> 137 </html>

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 January 07 2016 17:04:40.. This page has been viewed 2386 Times.

Record of Revisions
RevbyDateDescription
0.3kd2016-01-13Changed default on sqlExec() to return last insert id on INSERT commands and row count on updates.
0.2kd2016-01-07Added the getDef Function to increment named sequences.

blog ~ Resource Model ~ links