[ Index ] |
PHP Cross Reference of Web Application Component Toolkit |
[Summary view] [Print] [Text view]
1 <?php 2 //-------------------------------------------------------------------------------- 3 // Copyright 2003 Procata, Inc. 4 // Released under the LGPL license (http://www.gnu.org/copyleft/lesser.html) 5 //-------------------------------------------------------------------------------- 6 /** 7 * @package WACT_DB 8 * @version $Id: mysql.inc.php,v 1.34 2004/11/12 21:25:05 jeffmoore Exp $ 9 */ 10 //-------------------------------------------------------------------------------- 11 12 /** 13 * Check dataspace is loaded 14 */ 15 if (!class_exists('DataSpace')) { 16 require WACT_ROOT . 'util/dataspace.inc.php'; 17 } 18 19 /** 20 * Encapsulates a database connection. Allows for lazy connections. 21 * implements Connection interface 22 * @see Connection 23 * @see http://wact.sourceforge.net/index.php/Connection 24 * @access public 25 * @package WACT_DB 26 */ 27 class MySQLConnection { 28 29 /** 30 * Resource representing connection to MySQL database 31 * @var resource 32 * @access private 33 */ 34 var $ConnectionId; 35 36 /** 37 * Configuration information 38 * @var string 39 * @access private 40 */ 41 var $config; 42 43 /** 44 * Create a MySQL database connection. 45 * @param object Connection Configuration information 46 * @access private 47 */ 48 function MySQLConnection(&$config) { 49 $this->config =& $config; 50 } 51 52 /** 53 * Return connectionId for a mysql database. Allow a lazy connection. 54 * @return resource 55 * @access protected 56 */ 57 function getConnectionId() { 58 if (!isset($this->ConnectionId)) { 59 $this->connect(); 60 } 61 return $this->ConnectionId; 62 } 63 64 /** 65 * Connect to the the database 66 * @return void 67 * @access protected 68 */ 69 function connect() { 70 $this->ConnectionId = mysql_connect( 71 $this->config->get('host'), 72 $this->config->get('user'), 73 $this->config->get('password')); 74 if ($this->ConnectionId === FALSE) { 75 $this->RaiseError(); 76 } 77 78 if (mysql_select_db($this->config->get('database'), $this->ConnectionId) === FALSE) { 79 $this->RaiseError(); 80 } 81 } 82 83 /** 84 * Raises an error, passing it on to the framework level error mechanisms 85 * @param string (optional) SQL statement 86 * @return void 87 * @access private 88 */ 89 function RaiseError($sql = NULL) { 90 $errno = mysql_errno($this->getConnectionId()); 91 $id = 'DB_ERROR'; 92 $info = array('driver' => 'MySQL'); 93 if ($errno != 0) { 94 $info['errorno'] = $errno; 95 $info['error'] = mysql_error($this->getConnectionId()); 96 $id .= '_MESSAGE'; 97 } 98 if (!is_null($sql)) { 99 $info['sql'] = $sql; 100 $id .= '_SQL'; 101 } 102 RaiseError('db', $id, $info); 103 } 104 105 /** 106 * Convert a PHP value into an SQL literal. The type to convert to is 107 * based on the type of the PHP value passed, or the type string passed. 108 * @param mixed value to convert 109 * @param string (optional) type to convert to 110 * @return string literal SQL fragment 111 * @access public 112 */ 113 function makeLiteral($value, $type = NULL) { 114 if (is_null($value)) { 115 return 'NULL'; 116 } 117 if (is_null($type)) { 118 $type = gettype($value); 119 } 120 switch (strtolower($type)) { 121 case 'string': 122 return "'" . mysql_escape_string($value) . "'"; 123 case 'boolean': 124 return ($value) ? 1 : 0; 125 default: 126 return strval($value); 127 } 128 } 129 130 /** 131 * Factory function to create a Record object 132 * @see http://wact.sourceforge.net/index.php/NewRecord 133 * @param DataSpace or subclass (optional) 134 * used to initialize the fields of the new record prior to calling insert() 135 * @return Record reference 136 * @access public 137 */ 138 function &NewRecord($DataSpace = NULL) { 139 $Record =& new MySqlRecord($this); 140 if (!is_null($DataSpace)) { 141 $Record->import($DataSpace->export()); 142 } 143 return $Record; 144 } 145 146 /** 147 * Factory function used to retrieve more than one row from a MySQL database, 148 * applying a filter to the data if supplied as an argument 149 * @see http://wact.sourceforge.net/index.php/NewRecordSet 150 * @param string SQL statement 151 * @param object filter class (optional) 152 * @return RecordSet reference 153 * @access public 154 */ 155 function &NewRecordSet($query, $filter = NULL) { 156 $RecordSet =& new MySqlRecordSet($this, $query); 157 if (!is_null($filter)) { 158 $RecordSet->registerFilter($filter); 159 } 160 return $RecordSet; 161 } 162 163 /** 164 * Factory function used to retrieve more than one row from a MySQL database, 165 * applying a filter to the data if supplied as an argument, and applying a 166 * pager to the result set as well. 167 * @param string SQL statement 168 * @param object pager 169 * @param object filter class (optional) 170 * @return RecordSet reference 171 * @access public 172 */ 173 function &NewPagedRecordSet($query, &$pager, $filter = NULL) { 174 $RecordSet =& $this->NewRecordSet($query, $filter); 175 $RecordSet->paginate($pager); 176 return $RecordSet; 177 } 178 179 /** 180 * Retreive a single record from the database based on a query. 181 * @param string SQL Query 182 * @return Record object or NULL if not found 183 * @access public 184 */ 185 function &FindRecord($query) { 186 $Record =& new MySqlRecord($this); 187 $QueryId = $this->_execute($query); 188 $Record->properties =& mysql_fetch_assoc($QueryId); 189 mysql_free_result($QueryId); 190 if (is_array($Record->properties)) { 191 return $Record; 192 } 193 } 194 195 /** 196 * Get a single value from the first column of a single record from 197 * a database query. 198 * @param string SQL Query 199 * @return Value or NULL if not found 200 * @access public 201 */ 202 function getOneValue($query) { 203 $QueryId = $this->_execute($query); 204 $row = mysql_fetch_row($QueryId); 205 mysql_free_result($QueryId); 206 if (is_array($row)) { 207 return $row[0]; 208 } 209 } 210 211 /** 212 * Retreive an array where each element of the array is the value from the 213 * first column of a database query. 214 * @param string SQL Query 215 * @access public 216 */ 217 function getOneColumnArray($query) { 218 $Column = array(); 219 $QueryId = $this->_execute($query); 220 while (is_array($row = mysql_fetch_row($QueryId))) { 221 $Column[] = $row[0]; 222 } 223 mysql_free_result($QueryId); 224 return $Column; 225 } 226 227 /** 228 * Retreive an associative array where each element of the array is based 229 * on the first column as a key and the second column as data. 230 * @param string SQL Query 231 * @access public 232 */ 233 function getTwoColumnArray($query) { 234 $Column = array(); 235 $QueryId = $this->_execute($query); 236 while (is_array($row = mysql_fetch_row($QueryId))) { 237 $Column[$row[0]] = $row[1]; 238 } 239 mysql_free_result($QueryId); 240 return $Column; 241 } 242 243 /** 244 * Performs any query that does not return a cursor. 245 * @param string SQL query 246 * @return boolean TRUE if query is successful 247 */ 248 function execute($sql) { 249 return (Boolean) $this->_execute($sql); 250 } 251 252 /* 253 * For internal driver use only 254 * @param string SQL query 255 * @return resource MySQL result resource 256 * @access protected 257 */ 258 function _execute($sql) { 259 $result = mysql_query($sql, $this->getConnectionId()); 260 261 if ($result === FALSE) { 262 $this->RaiseError($sql); 263 } 264 return $result; 265 } 266 267 /** 268 * Disconnect from database 269 * @return void 270 * @access public 271 */ 272 function disconnect() { 273 mysql_close($this->ConnectionId); 274 $this->ConnectionId = NULL; 275 } 276 277 } 278 279 /** 280 * Encapsulates INSERT, UPDATE, or DELETE operations on a MySQL database. 281 * implements Record interface 282 * @see Record 283 * @see http://wact.sourceforge.net/index.php/Record 284 * @access public 285 * @package WACT_DB 286 */ 287 class MySqlRecord extends DataSpace { 288 /** 289 * Connection to MySQL 290 * @var resource 291 * @access private 292 */ 293 var $Connection; 294 295 /** 296 * Conecruct a record 297 * @param object Connection 298 */ 299 function MySqlRecord($Connection) { 300 $this->Connection = $Connection; 301 } 302 303 /** 304 * Build SQL fragment to assign values to columns 305 * @param array associative of field_name => type 306 * @param array associative (optional) of field_name => value 307 * @return string SQL fragment 308 * @access protected 309 */ 310 function buildAssignmentSQL($fields, $extrafields) { 311 $query = ' SET '; 312 $sep = ''; 313 foreach($fields as $fieldname => $type) { 314 if (!is_string($fieldname)) { 315 $fieldname = $type; // Swap if no type is specified 316 $type = NULL; 317 } 318 $query .= $sep . $fieldname . '=' . 319 $this->Connection->makeLiteral($this->get($fieldname), $type); 320 $sep = ', '; 321 } 322 if (!is_null($extrafields)) { 323 foreach($extrafields as $fieldname => $value) { 324 $query .= $sep . $fieldname . '=' . $value; 325 $sep = ', '; 326 } 327 } 328 return $query; 329 } 330 331 /** 332 * INSERT a record into a table with a primary key represented by a 333 * auto_increment/serial column and return the primary key of the 334 * inserted record. 335 * the field list parameter allows expressions to defined in the sql 336 * statements as well as field values defined in the record. 337 * requires MySQL Version 3.22.10 or better. 338 * @param string table name 339 * @param array associative of field_name => type 340 * @param string Name of primary key field field 341 * @param array associative (optional) of field_name => value 342 * @return integer Primary key of the newly inserted record or FALSE if no 343 * record was inserted. 344 */ 345 function insertId($table, $fields, $primary_key_field, $extrafields = NULL) { 346 $query = 'INSERT INTO ' . $table . $this->buildAssignmentSQL($fields, $extrafields); 347 $result = $this->Connection->execute($query); 348 if ($result) { 349 return mysql_insert_id($this->Connection->getConnectionId()); 350 } else { 351 return FALSE; 352 } 353 } 354 355 /** 356 * INSERTs the values of this record into a single table 357 * the field list parameter allows expressions to defined in the sql 358 * statements as well as field values defined in the record. 359 * @param string table name 360 * @param array associative of field_name => type 361 * @param string (default = null) Name of autoincrement field 362 * @param array associative (optional) of field_name => value 363 * @return Boolean True on success. 364 */ 365 function insert($table, $fields, $extrafields = NULL) { 366 $query = 'INSERT INTO ' . $table . $this->buildAssignmentSQL($fields, $extrafields); 367 return (Boolean) $this->Connection->execute($query); 368 } 369 370 /** 371 * Performs an UPDATE on a single table 372 * @param string table name 373 * @param array associative of field_name => type 374 * @param string (optional) SQL where clause 375 * @param array associative (optional) of field_name => value 376 * @return boolean true on success, false on failure 377 * @access public 378 */ 379 function update($table, $fields, $where = NULL, $extrafields = NULL) { 380 381 $query = 'UPDATE ' . $table . $this->buildAssignmentSQL($fields, $extrafields, 'update'); 382 383 if (!is_null($where)) { 384 $query .= ' WHERE ' . $where; 385 } 386 387 return (Boolean) $this->Connection->execute($query); 388 } 389 390 /** 391 * Gets the number of rows changed by an insert, delete or update query. 392 * @return int number of affected rows 393 * @access public 394 */ 395 function getAffectedRowCount() { 396 return mysql_affected_rows($this->Connection->getConnectionId()); 397 } 398 399 } 400 401 /** 402 * Encapsulates the cursor result of a SELECT, SHOW, DESCRIBE or EXPLAIN 403 * sql statement. Implements the Iterator interface. 404 * implements RecordSet and PagedDataSet interfaces 405 * @see RecordSet 406 * @see PagedDataSet 407 * @see http://wact.sourceforge.net/index.php/RecordSet 408 * @access public 409 * @package WACT_DB 410 */ 411 class MySqlRecordSet /* implements iterator */ extends MySqlRecord { 412 /** 413 * MySQL query resource 414 * @var resource 415 * @access private 416 */ 417 var $QueryId; 418 419 /** 420 * Pager 421 * @var object The current pager for this query. 422 * @access private 423 */ 424 var $pager; 425 426 /** 427 * SQL Statement 428 * @var string 429 * @access private 430 */ 431 var $Query; 432 433 /** 434 * Construct a record set. 435 * @param object MySQL Connection 436 * @param string SQL SELECT, SHOW, DESCRIBE, or EXPLAIN statement 437 * @return void 438 * @access public 439 */ 440 function MySqlRecordSet($Connection, $Query_String) { 441 $this->Connection = $Connection; 442 $this->Query = $Query_String; 443 } 444 445 /** 446 * Assign a query for this object to process. 447 * @param string SQL statement 448 * @return void 449 * @access public 450 */ 451 function query($Query_String) { 452 $this->freeQuery(); 453 $this->Query = $Query_String; 454 } 455 456 /** 457 * Assign a pager to this query for the purposes of breaking up the resulting 458 * cursor into paged chucks. 459 * @param interface Pager 460 * @return void 461 * @access public 462 */ 463 function paginate(&$pager) { 464 $this->pager =& $pager; 465 $pager->setPagedDataSet($this); 466 } 467 468 /** 469 * Frees up the Query resource. can be called even if no 470 * resource is set or if has already been freed. 471 * @return void 472 * @access private 473 */ 474 function freeQuery() { 475 if (isset($this->QueryId) && is_resource($this->QueryId)) { 476 mysql_free_result($this->QueryId); 477 $this->QueryId = NULL; 478 } 479 } 480 481 /** 482 * Move the current pointer to the first position in the cursor. 483 * @access public 484 * @return boolean TRUE if the query is valid. 485 */ 486 function reset() { 487 if (isset($this->QueryId) && is_resource($this->QueryId)) { 488 if (mysql_data_seek($this->QueryId, 0) === FALSE) { 489 $this->Connection->RaiseError(); 490 } 491 } else { 492 $query = $this->Query; 493 if (isset($this->pager)) { 494 $query .= ' LIMIT ' . 495 $this->pager->getStartingItem() . ',' . 496 $this->pager->getItemsPerPage(); 497 } 498 499 $this->QueryId = $this->Connection->_execute($query); 500 } 501 return TRUE; 502 } 503 504 /** 505 * Iterator next method. Load the data values from the next record 506 * in the query into the current data values. 507 * @return boolean TRUE if there are more results to fetch 508 * @access public 509 */ 510 function next() { 511 if (!isset($this->QueryId)) { 512 return FALSE; 513 } 514 515 $this->properties =& mysql_fetch_assoc($this->QueryId); 516 517 if (is_array($this->properties)) { 518 $this->prepare(); 519 return TRUE; 520 } else { 521 $this->freeQuery(); 522 return FALSE; 523 } 524 } 525 526 /** 527 * Returns the number of rows in a query 528 * @return int number of rows 529 * @access public 530 */ 531 function getRowCount() { 532 return mysql_num_rows($this->QueryId); 533 } 534 535 /** 536 * Returns the total number of rows that a query would return, ignoring paging 537 * restrictions. Query re-writing based on _adodb_getcount. 538 * @return int number of rows 539 * @access public 540 */ 541 function getTotalRowCount() { 542 if (!(preg_match("/^\s*SELECT\s+DISTINCT/is", $this->Query) && preg_match('/\s+GROUP\s+BY\s+/is',$this->Query))) { 543 $rewritesql = preg_replace( 544 '/^\s*SELECT\s.*\s+FROM\s/Uis','SELECT COUNT(*) FROM ',$this->Query); 545 $rewritesql = preg_replace('/(\sORDER\s+BY\s.*)/is','',$rewritesql); 546 547 $QueryId = $this->Connection->_execute($rewritesql); 548 $row = mysql_fetch_row($QueryId); 549 mysql_free_result($QueryId); 550 if (is_array($row)) { 551 return $row[0]; 552 } 553 } 554 555 // could not re-write the query, try a different method. 556 557 $QueryId = $this->Connection->_execute($this->Query); 558 $count = mysql_num_rows($QueryId); 559 mysql_free_result($QueryId); 560 return $count; 561 } 562 } 563 ?>
title
Description
Body
title
Description
Body
title
Description
Body
title
Body
Generated: Sun Nov 28 19:36:09 2004 | Cross-referenced by PHPXref 0.5 |