<?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> [<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;
}
}
?>
|
Download
