Community Color - Source Code

sql

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 ?>