28
January
January
Database is usually the slowest part of PHP application or web site. Most developers solve that problem using Memcached or similar caching software.
But if you have a small web site or a application that doesn’t have much traffic you fall back to a default database functions like mysql_query and mysqli_query and so on. But at that point we usually hit a problem how to support different databases.
Some people prefer MySQL database while others prefer MSSQL database. So most developers create a wrapper class that will make transfer to a different database as easy as possible.
Mine looks like this:
<?php
/*************************************************************************************************************
* Database Abstraction/Wrap class
*
* @author Jaka Prasnikar - https://prahec.com/
* @version 2.0 (Updated 17.10.2012)
************************************************************************************************************ */
/**
* Class Database
*/
class Database {
/**
* Array of options for this class
*
* @var array
*/
private $options;
/**
* MySQLi connection resource, required for every other action
*
* @var resource
*/
private $link;
/**
* Cache object (passed from Caching class)
*
* @var bool|object
*/
private $cache = false;
/**
* Database connection status
*
* @var bool
*/
public $connected = false;
/**
* @var string
*/
public $error;
/**
* @var string
*/
public $lastQuery;
/**
* Database constructor
* We do not connect to database unless required
*
* @param $mysql_info_array
* @param bool|object $cache
* @param bool $persistent
*/
function __construct( $mysql_info_array, $cache = false, $persistent = false ) {
$this->options = $mysql_info_array + array(
// Basic configuration
'host' => '127.0.0.1',
'socket' => false,
'port' => 3306,
'user' => 'root',
'pass' => '',
'db' => 'database',
// Additional settings
'compression' => false,
'charset' => 'UTF8',
'timeout' => '5', ## How long does MySQL have to respond (timeout)
'logging' => array(
'enabled' => false,
'file' => 'mysqli.log',
'level' => 1
)
);
// Caching object passed to the class
if ( $cache !== false && is_object( $cache ) && method_exists( $cache, 'set' ) && method_exists( $cache, 'get' ) ) $this->cache = $cache;
if ( $persistent === true ) $this->options[ 'host' ] = "p:{$this->options['host']}"; ## Enable persistent connection
}
/**
* This function requires no parameters, its pretty simple. It checks for existing database connection
* If one does not exist, we create it. On error throws exception to make sure nothing else goes wrong
*
* @return bool
* @throws \Exception
*/
private function connect() {
if ( $this->connected == false ) {
// Create MySQLi Object
$this->link = mysqli_init();
// Set timeout to X seconds, we don't want endless connection attempts from PHP to MySQL
$this->link->options( MYSQLI_OPT_CONNECT_TIMEOUT, $this->options[ 'timeout' ] );
// Now finally attempt to connect with pre-defined settings
$this->link->real_connect(
$this->options[ 'host' ], $this->options[ 'user' ], $this->options[ 'pass' ], $this->options[ 'db' ],
$this->options[ 'port' ], $this->options[ 'socket' ], ( ( $this->options[ 'compression' ] == true ) ? MYSQLI_CLIENT_COMPRESS : 0 )
);
// Check if connection was successful
if ( $this->link->connect_error ) {
if ( $this->options[ 'logging' ][ 'enabled' ] != false ) // If logfile is set, log to file
file_put_contents( $this->options[ 'logging' ][ 'file' ], "[" . date( "j.n.Y-G:i" ) . "] Could not connect to database! ERROR: {$this->link->error}\n", FILE_APPEND );
// Throw exception
throw new Exception( 'Connection to the database failed! ' . $this->link->connect_error, $this->link->connect_errno );
} else { // Only set MySQLi Option if connection was successful
// We use UTF-8 encoding for connection between class/database
$this->link->set_charset( $this->options[ 'charset' ] );
}
return $this->connected = true;
}
return $this->connected;
}
/**
* Database query function, this is direct no cache or anything
*
* @param $command
*
* @return object|bool $result
*/
public function query( $command ) {
// Check if we can use cache or not
if ( !empty( $command ) ) {
// Benchmark queries
$bench = microtime( true );
// Only connect to database if required & execute query
$this->connect();
$result = $this->link->query( $command );
// Catch database errors (don't throw exceptions here)
if ( $this->link->error ) {
if ( $this->options[ 'logging' ][ 'enabled' ] != false AND $this->options[ 'logging' ][ 'level' ] == 1 | 2 )
file_put_contents( $this->options[ 'logging' ][ 'file' ], "[" . date( "j.n.Y-G:i" ) . "] Query: {$command}; ERROR: {$this->link->error}\n", FILE_APPEND );
$this->error = $this->link->error;
} else {
// If Log enabled Log to File (including benchmark of the query)
if ( $this->options[ 'logging' ][ 'enabled' ] != false AND $this->options[ 'logging' ][ 'level' ] == 2 )
file_put_contents( $this->options[ 'logging' ][ 'file' ], "[" . date( "j.n.Y-G:i" ) . "] Query: {$command} (" . number_format( ( ( microtime( true ) - $bench ) * 1000 ), 2 ) . "ms)\n", FILE_APPEND );
// Its important we set error to false if all went through
$this->error = false;
/* Store last query command to object */
$this->lastQuery = $command;
}
// Return whole MySQLi result object
return $result;
}
return false;
}
/**
* This only returns array, take care not to use with while!
* Note: This function may slow down greatly or run out of memory at large results
*
* @param $command
* @param bool $cache
*
* @return array|bool
*/
public function fetch( $command, $cache = false ) {
// Initial definition
$output = false;
// If caching enabled, attempt to get cached result
if ( $cache !== false && $this->cache !== false )
$output = $this->cache->get( ( ( $cache === true ) ? md5( $command ) : $cache ) );
// There is no cached data yet, create it
if ( $output === false ) {
// Query
$result = $this->query( $command );
// If result has more than one row else sql failed
if ( $result->num_rows >= 1 ) {
// Loop through assoc fetch
while ( $data = $result->fetch_assoc() ) : $output[] = $data; endwhile;
}
// Attempt to cache if enabled
if ( $cache !== false && $this->cache !== false )
$this->cache->set( ( ( $cache === true ) ? md5( $command ) : $cache ), $output );
}
return $output;
}
/**
* This is just a simple helper function to get single result, very simple
*
* @param $command
* @param bool $cache
*
* @return bool|mixed
*/
public function fetchRow( $command, $cache = false ) {
$data = $this->fetch( $command, $cache );
return ( ( $data == false ) ? false : $data[ 0 ] );
}
/**
* Simple function to count amount of rows in specified table
*
* @param $table
* @param string $row
*
* @return int
*/
public function num( $table, $row = '*' ) {
// Execute Query
$do = $this->fetchRow( "SELECT COUNT({$row}) as num FROM `{$table}`", true );
list( $num ) = ( ( $do ) ? $do[ 'num' ] : 0 );
// Return
return $num;
}
/**
* MySQLi manual escape function shortage
*
* @param $input
*
* @return string
*/
public function s( $input ) {
$this->connect(); // Connect if no connection is established so far
return mysqli_real_escape_string( $this->link, $input );
}
/**
* Get the identification of the latest inserted ID
*
* @return string
*/
public function lastID() {
// Only connect to database if required
$this->connect();
// Get last insert_id
$lastID = $this->link->insert_id;
if ( empty( $lastID ) OR $lastID == "0" ) {
return "1";
} else {
return $lastID;
}
}
/**
* Function to insert data into database, this function manually escapes strings.
* its recommended to use prepared statements instead, better security
*
* @param $table
* @param $array
*
* @return bool
*/
public function insert( $table, $array ) {
// Exit function if table is empty or array isn't passed properly
if ( empty( $table ) OR !is_array( $array ) ) {
return false;
}
// First escape whole array
$array = array_map( array( $this, 's' ), $array );
// Build query :)
$this->query( "INSERT INTO `{$table}` (`" . implode( '`,`', array_keys( $array ) ) . "`) VALUES ('" . implode( "' , '", $array ) . "')" );
// Try insert, return false if it fails
if ( !$this->error ) {
return true;
} else {
return false;
}
}
/**
* Update a row in database table, simple function that also manually escapes data.
* its recommended to use prepared statements instead, better security
*
* @param $table
* @param $where
* @param $array
*
* @return bool
*/
public function update( $table, $where, $array ) {
// Exit function if table is empty or array isn't passed properly
if ( empty( $table ) OR !is_array( $array ) ) return false;
// First escape whole array using array map
$array = array_map( array( $this, 's' ), $array );
// Count amount of data to update
if ( count( $array ) > 0 ) {
foreach ( $array as $key => $value ) {
$value = "'$value'";
$implodedArray[] = "`$key` = $value";
}
}
// Build query :)
$this->query( "UPDATE `{$table}` SET " . implode( ', ', $implodedArray ) . " WHERE {$where}" );
// Try insert, return false if it fails
if ( !$this->error ) {
return true;
} else {
return false;
}
}
/**
* Simple helper function to use with "while" and on "query" method
*
* @param $resource
*
* @return mixed
*/
public function assoc( $resource ) {
return $resource->fetch_assoc();
}
/**
* Database class destructor (called just before PHP execution stops)
*/
function __destruct() {
if ( is_resource( $this->link ) ) {
$this->link->close();
$this->connected = false;
}
}
}The wrapper also has following functions:
Escape String:
<?php
$db->s(“my string”);
?>Display Last inserted ID:
<?php
$db->lastID();
?>Count all rows:
<?php
$db->num(‘tablename’); // Much faster then mysql_num_rows();
?>Associative result array:
<?php
$db->assoc();
?>Numeric and Assoc array:
<?php
$db->arr();
?>Its also extremely easy to add more functions to work with.
I’m going to add support for prepared queries one day as well ?
12
September
September
Web Development
Why I decided for SCSS
Few weeks ago I’ve been struggling between SCSS / CSS and LESS. I...
28
September
September
RC
How I Got Into RC World: A Winter Sunday Tale
These RC cars are almost like toys, but they're not actually toys...
05
July
July
Web Development
WordPress Plugin for Live Icecast Info
Few years ago I wrote a simple plugin for WordPress which allows...
04
October
October
Web Development
Simple Vue Preloader
When working with Vue.js you quickly come to a need for a preloader...
Leave A Comment