mySQL DB Wrapper Class
Item 6 of 9
1 <?php 2 /** 3 * A database abstraction class 4 * 5 * Note: This class is set up to work with mySQL, however it could be replaced to connect to 6 * SQL Server, Oracle or even an XML datastore 7 * 8 * @author Dan Pupius 9 * @version 1.0 10 * @copyright ©2004 pupius.co.uk <http://pupius.co.uk> 11 * @package Utility 12 * @subpackage Classes 13 */ 14 class DB { 15 16 /** 17 * @var string The database host, e.g. localhost or db.mydomain.com 18 * @access private 19 */ 20 var $_host = ""; 21 22 /** 23 * @var string The name of the database we want to connect to 24 * @access private 25 */ 26 var $_database = ""; 27 28 /** 29 * @var string The username with which to connect to the database 30 * @access private 31 */ 32 var $_user = ""; 33 34 /** 35 * @var string The password used to connect to the database 36 * @access private 37 */ 38 var $_password = ""; 39 40 /** 41 * @var int mySQL resource identifiation for the current connection 42 * @access private 43 */ 44 var $_linkID = 0; 45 46 /** 47 * @var int mySQL resource identifiation for the current query 48 * @access private 49 */ 50 var $_queryID = 0; 51 52 /** 53 * @var array Storage for the current record 54 * @access private 55 */ 56 var $_record = array(); 57 58 /** 59 * @var array Storage for the current row 60 * @access private 61 */ 62 var $_row; 63 64 /** 65 * @var int mySql error number 66 * @access private 67 */ 68 var $_errorNumber = 0; 69 70 /** 71 * @var string mySql error message 72 * @access private 73 */ 74 var $_errorMsg= ""; 75 76 77 78 /** 79 * @var boolean Whether or not to automatically free up memory using mysql_free_result() 80 */ 81 var $autoFree = 1; 82 83 84 /** 85 * Constructor function that sets up the database connection settings 86 * 87 * @param string $host The database host, e.g. localhost or db.mydomain.com 88 * @param string $database The name of the database we want to connect to 89 * @param string $user The username with which to connect to the database 90 * @param string $password The password used to connect to the database 91 * @access public 92 */ 93 function DB($host,$database,$user,$password) { 94 $this->_host = $host; 95 $this->_database = $database; 96 $this->_user = $user; 97 $this->_password = $password; 98 } 99 100 101 102 /** 103 * Makes a connection to the database 104 * 105 * @access private 106 */ 107 function _connect() { 108 //only open a connection if there isn't already one open 109 if ($this->_linkID==0) { 110 $this->_linkID = @mysql_pconnect($this->_host, $this->_user, $this->_password); 111 if (!$this->_linkID) { 112 $this->_errrorNumber = mysql_errno(); 113 $this->_errrorMsg = mysql_error(); 114 $this->halt("mysql_pconnect() failed"); 115 } 116 if(!mysql_query("USE {$this->_database}", $this->_linkID)) { 117 $this->_errrorNumber = mysql_errno(); 118 $this->_errrorMsg = mysql_error(); 119 $this->halt("Cannot Use Database ". $this->_database); 120 } 121 } 122 } 123 124 /** 125 * Executes a SQL query 126 * 127 * @param string $sql The SQL string to be executed 128 * @return int Query ID 129 * @access public 130 */ 131 function query($sql) { 132 $this->_connect(); 133 134 $sql = ereg_replace(';','',$sql); 135 136 $this->_queryID = mysql_query($sql,$this->_linkID); 137 $this->_row = 0; 138 $this->_errrorNumber = mysql_errno(); 139 $this->_errrorMsg = mysql_error(); 140 141 if(!$this->_queryID) { 142 $this->halt("Invalid SQL: ".$sql); 143 } 144 145 return $this->_queryID; 146 } 147 148 149 /** 150 * Advances the DB object onto the next record, returns false if no record available 151 * 152 * Example: 153 * <code> 154 * while($db->next_record()) { 155 * ... 156 * } 157 * </code> 158 * 159 * @return boolean Whether or not a new record is available 160 * @access public 161 */ 162 function next_record() { 163 $this->_record = mysql_fetch_array($this->_queryID); 164 $this->_row += 1; 165 $this->_errrorNumber = mysql_errno(); 166 $this->_errrorMsg = mysql_error(); 167 168 $stat = is_array($this->_record); 169 if(!$stat && $this->autoFree) { 170 mysql_free_result($this->_queryID); 171 $this->_queryID = 0; 172 } 173 return $stat; 174 } 175 176 177 /** 178 * Moves the DB object onto a particular record 179 * 180 * @param int $rowNumber The requested row to move to 181 * @access public 182 */ 183 function seek($rowNumber) { 184 $status = mysql_data_seek($this->_queryID, $rowNumber); 185 if($status) $this->_row = $rowNumber; 186 } 187 188 189 190 /** 191 * Returns information about a particular table 192 * 193 * @param string $table The table name for which the metadata is wanted 194 * @return array A multi-dimentional array of metadata 195 * @access public 196 */ 197 function metadata($table) { 198 $count = 0; 199 $id = 0; 200 $res = array(); 201 202 $this->_connect(); 203 $id = @mysql_list_fields($this->_database, $table); 204 if($id < 0) { 205 $this->_errrorNumber = mysql_errno(); 206 $this->_errrorMsg = mysql_error(); 207 $this->halt("Metadata query failed."); 208 } 209 $count = mysql_num_fields($id); 210 211 for ($i=0; $i<$count; $i++) { 212 $res[$i]["table"] = mysql_field_table ($id, $i); 213 $res[$i]["name"] = mysql_field_name ($id, $i); 214 $res[$i]["type"] = mysql_field_type ($id, $i); 215 $res[$i]["len"] = mysql_field_len ($id, $i); 216 $res[$i]["flags"] = mysql_field_flags ($id, $i); 217 $res["meta"][$res[$i]["name"]] = $i; 218 $res["num_fields"]= $count; 219 } 220 221 mysql_free_result($id); 222 return $res; 223 } 224 225 /** 226 * Returns how many rows were affected by the last query 227 * 228 * @return int How many rows were affected 229 * @access public 230 */ 231 function affected_rows() { 232 return mysql_affected_rows($this->_linkID); 233 } 234 235 236 237 /** 238 * Returns how many rows were returned by the last query 239 * 240 * @return int How many rows were returned 241 * @access public 242 */ 243 function num_rows() { 244 return mysql_num_rows($this->_queryID); 245 } 246 247 /** 248 * Returns how many fields were returned with the last query 249 * 250 * @return int How many fields 251 * @access public 252 */ 253 function num_fields() { 254 return mysql_num_fields($this->_queryID); 255 } 256 257 258 /** 259 * Returns how many rows were returned by the last query 260 * 261 * @return int How many rows were returned 262 * @access public 263 * @deprecated Deprecated alias to {@link num_rows() num_rows()}. 264 */ 265 function nf() { 266 return $this->num_rows(); 267 } 268 269 270 /** 271 * Prints how many rows were returned by the last query 272 * 273 * @return int How many rows were returned 274 * @access public 275 * @deprecated Deprecated alternative to {@link num_rows() num_rows()} that outputs the number of rows returned. 276 */ 277 function np() { 278 print $this->num_rows(); 279 } 280 281 282 /** 283 * Returns the value of a particular field 284 * 285 * @param string $name The field whose value wants returning 286 * @return mixed The value of a particular field in the current recordset 287 * @access public 288 */ 289 function f($name) { 290 return $this->_record[$name]; 291 } 292 293 294 /** 295 * Prints the value of a particular field 296 * 297 * @param string $name The field whose value wants printing 298 * @access public 299 * @deprecated Deprecated alternative to {@link f() f()} that prints out the value of a field, use <code>echo $db->f("field")</code> instead. 300 */ 301 function p($name) { 302 print $this->_record[$name]; 303 } 304 305 306 307 308 /** 309 * An error occured so create an exception in the {@link Debug Debug} object. 310 * 311 * @param string $msg Error Message 312 * @access public 313 * @uses Debug::exception() 314 */ 315 function halt($msg) { 316 die("MySQL Error {$this->_errrorNumber}<br><br>$msg<br>".$this->_errrorMsg."<br>in file: " __FILE__); 317 } 318 319 320 321 /** 322 * Returns the id of the last row that was INSERTed. 323 * 324 * return int Database id of last INSERTed row 325 * @access public 326 */ 327 function last_insert_id(){ 328 return mysql_insert_id($this->_linkID); 329 } 330 } 331 332 333 334 ?> 335
