PHP Classes

File: class.Database.php

Recommend this page to a friend!
  Classes of marcelo entraigas   AMD   class.Database.php   Download  
File: class.Database.php
Role: Class source
Content type: text/plain
Description: Auxiliary class
Class: AMD
Add, modify and delete MySQL table records
Author: By
Last change:
Date: 14 years ago
Size: 13,028 bytes
 

Contents

Class file image Download
<?php /** * It's an easy way to run SQL querys an retrieve the results as an array. * <br>Last update: September 20, 2009 * <br>License: BSD * <br>Author: Marcelo Entraigas <marcelo [at] entraigas.com.ar> */ class Database extends Error { //private var var $_connection = array(); //(array) database connection var $_object_syntax = true; //(boolean) last sql query was stored as object or as an associative array /** (boolean) return results as object or an associative array */ var $return_as_object = true; /** last query result array */ var $results = array(); /** last query metadata array */ var $metadata = array(); /** (integer) affected rows (INSERT, UPDATE, REPLACE) */ var $affected_rows = 0; /** (integer) last insert id */ var $insert_id = 0; /** * Class constructor. * * @param string $config configuration array index */ function Database($config = 'default') { global $cfg_resource; $this->_connection['link_id'] = false; $this->_connection['resource'] = false; $this->_connection['server'] = $cfg_resource[$config]['user']? $cfg_resource[$config]['server'] : ''; $this->_connection['database'] = $cfg_resource[$config]['user']? $cfg_resource[$config]['database'] : ''; $this->_connection['user'] = $cfg_resource[$config]['user']? $cfg_resource[$config]['user'] : ''; $this->_connection['password'] = $cfg_resource[$config]['pass']? $cfg_resource[$config]['pass'] : ''; } /** * Free (unset) the cached results and metadata */ function free() { $this->results = array(); $this->metadata = array(); $this->insert_id = 0; $this->affected_rows = 0; } /** * Return results as an object or as an array */ function return_as_object($On = true){ $this->return_as_object = ($On === true)? true: false; } /** * Private function. Returns the columns metadata (name, type, size...) * * @return array|false array|false */ function _get_metadata() { if (@mysql_num_fields($this->_connection['resource'])) { $column = 0; $metadata = array(); while ($column < @mysql_num_fields($this->_connection['resource'])) { $field = @mysql_fetch_field($this->_connection['resource']); $field->flag = @mysql_field_flags($this->_connection['resource'], $column); $field->column = $column; $metadata[$field->name] = get_object_vars($field); //set framework's format field switch ($metadata[$field->name]['type']) { case 'year': case 'int': case 'integer': case 'tinyint': case 'smallint': case 'mediumint': case 'bigint': $metadata[$field->name]['format'] = 'integer'; break; case 'real': case 'float': case 'double': case 'decimal': $metadata[$field->name]['format'] = 'float'; break; case 'date': case 'time': case 'datetime': case 'timestamp': $metadata[$field->name]['format'] = 'date'; break; default: $metadata[$field->name]['format'] = 'string'; } $column++; } return $metadata; } else $this->error('<b>Unable to get column\'s info</b>'); return false; } /** * Run a query and chache the results into an array. * * @param string $sql sql query * @return integer number of affected rows */ function query($sql) { $this->sql = trim($sql); if(empty($this->sql)) return false; //reset previous result $this->free(); $return = 0; //make db connection $this->_connection['link_id'] = @mysql_connect($this->_connection['server'], $this->_connection['user'], $this->_connection['password']); if(!$this->_connection['link_id']){ $this->error('<b>Unable to make a connection to the MySQL server.<br>Details:</b> check if the server is up, also check for the mysql library and user/password.'); return false; } if (!@mysql_select_db($this->_connection['database'], $this->_connection['link_id'])) { $this->error('<b>Unable to select/use the database.<br>Details:</b> ' . @mysql_error($this->_connection['link_id'])); return false; } //execute the sql query if(! $this->_connection['resource'] = @mysql_query($this->sql, $this->_connection['link_id'])){ $this->error('<b>SQL Query error.<br>Details:</b> ' . @mysql_error($this->_connection['link_id']) . '<br><b>sql</b>&nbsp;[<i>' . $this->sql . '</i>]'); } //get the results if (preg_match('/^(insert|delete|update|replace)\s+/i',$this->sql)) { //get the afected rows $this->affected_rows = @mysql_affected_rows($this->_connection['link_id']); $return = $this->affected_rows; //if it was an insert: get the last insert id if (preg_match('/^(insert|replace)\s+/i',$sql)) $this->insert_id = @mysql_insert_id($this->_connection['link_id']); } else { //this is a select sql query //get the metadata from database $this->metadata = $this->_get_metadata(); //save the last sql object/array state... $this->_object_syntax = $this->return_as_object; //get the results in the apropiated format if($this->return_as_object == true){ //return results as an object $eval = 'while ($row = @mysql_fetch_object($this->_connection["resource"])) $this->results[$return++] = $row;'; }else{ //return results as an associative array $eval = 'while ($row = @mysql_fetch_array($this->_connection["resource"], MYSQL_BOTH)) $this->results[$return++] = $row;'; } //get the results eval($eval); //get the total of affected rows $this->affected_rows = @mysql_num_rows($this->_connection["resource"]); //free mysql buffer @mysql_free_result($this->_connection['resource']); } //return the number of rows affected by the query return $return; } /** * return the all cached results in the apropiate way * * @return array */ function get_cached_data($field='', $key=''){ //by dafault return an empty array $return = array(); if($this->_object_syntax == $this->return_as_object){ //the results are stored using the right object/array syntax! //if the user want the results as they are sotred if($key=='' and $field=='') return $this->results; //return all the results //else, must retirve only what the user want... $key = ($key==='')? '$index' : ($this->return_as_object ? "\$values->$key" : "\$values['$key']"); $field = ($field==='')? '$values' : ($this->return_as_object ? "\$values->$field" : "\$values['$field']"); $eval = "\$return[$key] = $field;"; //no casting }else{ if($this->return_as_object == true){ //this mean the results are stored as array, but must return as object array $key = ($key==='')? '$index' : (is_integer($key)? "\$values[$key]" : "\$values['$key']"); $field = ($field==='')? '(object) $values' : (is_integer($field)? "\$values[$field]" : "\$values['$field']"); //only casting to object if returning all data $eval = "\$return[$key] = $field;"; }else{ //this mean the results are stored as object array, but must return as array $key = ($key==='')? '$index' : "\$values->$key"; $field = $field===''? 'get_object_vars($values)' : "\$values->$field"; //only casting to array if returning all data $eval = "\$return[$key] = $field;"; } } //create an array with the appropiate format and return it to user $eval = 'foreach($this->results as $index=>$values) ' . $eval; eval($eval); return $return; } /** * Run a query and chache the results into an array * * @param string $sql sql query * @param string $field only return this collumn. * @param string $key sql field that will be the index of the array's results. * @return array array */ function get_all($sql='', $field='', $key='') { $this->query($sql); return $this->get_cached_data($field, $key); } /** * Return the cached results (as an object or an asosiative array). * * @param string $sql optional sql query * @param integer $page * @param integer $page_size * @return array array */ function get_page($sql='', $page = 1, $page_size = 50) { if (!empty($sql)){ $sql .= " LIMIT $page, $page_size"; $this->query($sql); } return $this->get_cached_data(); } /** * Return a single row from cached results in the apropiate way * * @param string $sql optional sql query * @param integer $row number of row (starting at 0) * @return object|array object|array */ function get_row($sql='', $row=0) { if ($sql!='') $this->query($sql); if($this->_object_syntax == $this->return_as_object) //the results are stored using the right object/array syntax return $this->results[$row] ? $this->results[$row] : null; else{ //if the results are stored as an array, but must return an object if($this->return_as_object == true) return $this->results[$row] ? (object) $this->results[$row] : null; //else, the results are stored as an object, but must return an array return $this->results[$row] ? get_object_vars($this->results[$row]) : null; } } /** * Return a value from cached results * * @param string $sql optional sql query * @param string|integer $field field name or number (starting at 0) * @param integer $row number of row (starting at 0) * @return value|null value|null */ function get_value($sql='', $row=0, $field=0) { if ($sql!='') $this->query($sql); if($this->_object_syntax == true){ if(is_numeric($field)){ $c=0; foreach ($this->metadata as $key => $value){ if($c++==$field) $field = $key; } } return $this->results[$row]->$field ? $this->results[$row]->$field : null; }else return $this->results[$row][$field] ? $this->results[$row][$field] : null; } /** * Returns the columns metadata (name, type, size...) * * @return array|false array|false */ function get_metadata() { return $this->metadata; } /** * Returns the enum values of a column * * @param string $table * @param string $column * @return array */ function get_enum_values($table, $column){ $sql = sprintf("SHOW COLUMNS FROM %s LIKE '%s'",$table, $column); $data = $this->get_row($sql); $enum = $this->return_as_object? $data->Type : $data['Type']; preg_match('/^enum\(\'(.*)\'\)$/',$enum, $tmp); $tmp = explode("','", $tmp[1]); $return = array(); foreach ($tmp as $value){ $return[$value] = $value; } return $return; } } class CachedDB extends Database { var $is_cached = null; var $cache_id = ''; function CachedDB($config = 'default'){ parent::Database($config); global $cfg_resource; $this->_connection['cache_ttl'] = $cfg_resource[$config]['cache_ttl']? intval($cfg_resource[$config]['cache_ttl']) : 0; $this->_connection['cache_path'] = $cfg_resource[$config]['cache_path']? $cfg_resource[$config]['cache_path'] : ''; $this->_connection['cache_prefix'] = $cfg_resource[$config]['cache_prefix']? $cfg_resource[$config]['cache_prefix'] : 'db_'; } /** * Check is the Database object is cached and not expired * * @param string $sql sql query * @return boolean true|false */ function is_cached ($sql){ $this->cache_id = $this->_connection['cache_path'] . $this->_connection['cache_prefix'] . md5($sql); //is it cached? if($this->cached) return true; //is it not cached? if($this->_connection['cache_ttl'] <= 0 or !file_exists($this->cache_id)) return false; //is it expired? if(!($mtime = filemtime($this->cache_id))) return false; if(($mtime + $this->_connection['cache_ttl']) < time()) { //erase the cached template @unlink($this->cache_id); return false; } else { //cache the result of is_cached() call $this->cached = true; return true; } } /** * Reimplement the query method with caching system * * @param string $sql sql query * @return integer number of affected rows */ function query($sql, $ttl=''){ if($ttl>0) $this->_connection['cache_ttl'] = $ttl; $return = 0; if($this->is_cached($sql)){ //try to load object from disk $vars = unserialize(file_get_contents($this->cache_id)); foreach($vars as $key=>$val) eval("$"."this->$key = $"."vars['"."$key'];"); $return = $this->affected_rows; }else{ //execute the query $return = parent::query($sql); //try to save it to disk if($f = @fopen($this->cache_id,"w")){ $arr_tmp = array( 'results' => $this->results, 'metadata' => $this->metadata, 'insert_id' => $this->insert_id, 'affected_rows' => $this->affected_rows, ); @fwrite($f,serialize($arr_tmp)); @fclose($f); }else{ $this->error('Could not save db cache file'); } } return $return; } } ?>