Recently, I started on a new venture in which many sites will be built off the same base files. Pretty much if I change one file it will affect all of my sites. This has its ups and downs, but since the ups outweigh the downs I am going to use it. For this to work in the end, I need to have a good starting point. I won't be able to change method names ect once I add more websites because it will take too much time to update each site.
This is the database class setup I have. It took some time to think up and code, but as of right now it works perfectly and does everything I need it to. I am just wondering what I should add/subtract from it so I won't run into problems later.
FYI: These classes are for PHP5 and will not work in PHP4.
CLASS - class.db.php:
abstract class db {
public static $_conn = null;
private static $_table = array();
public static function start() {
list( $host,$user,$pass,$name ) = config::get('database->' . ( config::get('production') ? 'limited' : 'full' ) . '->host,user,pass,name');
self::$_conn = mysql_connect( $host,$user,$pass ) or trigger_error("DB START() - Unable to connect to {$host}",E_USER_ERROR);
mysql_select_db( $name,self::$_conn ) or trigger_error("DB START() - Unable to select database '{$name}'",E_USER_ERROR);
destruct::add('database','close');
destruct::priority('database','after','session');
}
public static function close() {
mysql_close( self::$_conn ) or trigger_error("DB CLOSE() - Unable to terminate connection to database",E_USER_ERROR);
}
public static function changeDB( $name ) {
mysql_select_db( $name,self::$_conn ) or trigger_error("DB CHANGEDB() - Unable to change database to '{$name}'",E_USER_ERROR);
}
public static function clean( $data ) {
return mysql_real_escape_string( trim( $data ),self::$_conn );
}
public static function table( $name ) {
if ( !isset( self::$_table[$name] ) ) {
self::$_table[$name] = new table( $name );
}
return self::$_table[$name];
}
public static function execute( $sql ) {
$query = mysql_query( $sql,self::$_conn ) or trigger_error("DB EXECUTE() - SQL query ({$sql}) failed: " . mysql_error( self::$_conn ),E_USER_ERROR);
return new mysqlresult( $query );
}
}To use on your server, you need to replace the start() function with this one (since you don't have my other classes)
public static function start( $host,$user,$pass,$name ) {
self::$_conn = mysql_connect( $host,$user,$pass ) or trigger_error("DB START() - Unable to connect to {$host}",E_USER_ERROR);
mysql_select_db( $name,self::$_conn ) or trigger_error("DB START() - Unable to select database '{$name}'",E_USER_ERROR);
}CLASS - class.table.php:
class table {
private $pk = null;
private $fields = array();
private $table = null;
private $cache = array();
private $sql = array();
public function __construct( $table ) {
$this->table = $table;
$query = db::execute("DESCRIBE `{$this->table}`");
while( list( $field,,,$key ) = $query->fetch() ) {
$this->fields[] = $field;
if ( $key == 'PRI' ) {
$this->pk = $field;
break;
}
}
if ( is_null( $this->pk ) ) {
trigger_error("TABLE __CONSTRUCT - Table '{$this->table}' does not have a primary key",E_USER_ERROR);
}
}
public function column( $name ) {
if ( in_array( $name,$this->fields ) ) {
return true;
}
return false;
}
public function &cache( $index ) {
return $this->cache[$index];
}
public function bind() {
$args = func_get_args();
$types = array_shift( $args );
$this->sql['bind'] = array(
'types' => $types,
'data' => $args
);
return $this;
}
public function run( $index ) {
if ( !isset( $this->cache[$index] ) ) {
return false;
}
$this->sql = $this->cache[$index];
return $this->execute();
}
public function query() {
if ( !empty( $this->sql ) ) {
trigger_error("TABLE QUERY() - Run 'execute' function before trying to create another query",E_USER_ERROR);
}
$num = func_num_args();
$args = func_get_args();
$type = array_shift( $args );
switch( $type ) {
case "select":
$query['data'] = $args[0];
if ( isset( $args[1] ) ) {
$query['where'] = $args[1];
}
if ( isset( $args[2] ) && is_array( $args[2] ) ) {
$query['extra'] = $args[2];
}
break;
case "update":
$query['data'] = $args[0];
$query['where'] = "`{$this->pk}` = ?";
$this->bind( 's',$args[1] );
break;
case "update-multi":
$type = 'update';
$query['data'] = $args[0];
$query['where'] = $args[1];
if ( isset( $args[2] ) && is_array( $args[2] ) ) {
$query['extra'] = $args[2];
}
break;
case "insert":
$query['data'] = $args[0];
break;
case "delete":
$query['where'] = "`{$this->pk}` = ?";
$this->bind( 's',$args[0] );
if ( isset( $args[1] ) && is_array( $args[1] ) ) {
$query['extra'] = $args[1];
}
break;
case "delete-multi":
$type = 'delete';
$query['where'] = $args[0];
if ( isset( $args[1] ) && is_array( $args[1] ) ) {
$query['extra'] = $args[1];
}
break;
default:
trigger_error("TABLE QUERY() - Invaild query type '{$type}'",E_USER_ERROR);
break;
}
$query['type'] = $type;
if ( isset( $query['extra'] ) ) {
$extra = $query['extra'];
unset( $query['extra'] );
$query = array_merge( $query,$extra );
}
$this->sql = array_merge( $this->sql,$query );
return $this;
}
private static function readyInsert( $val ) {
return "'" . db::clean( $val ) . "'";
}
private static function readyUpdate( $key,$val ) {
return "`{$key}` = '" . db::clean( $val ) . "'";
}
private function build( $array ) {
$query = '';
switch( $array['type'] ) {
case "select":
$fields = ( is_array( $array['data'] ) ? implode( ',',$array['data'] ) : $array['data'] );
$query = "SELECT {$fields} FROM `{$this->table}`";
break;
case "insert":
$fields = implode( ',',array_keys( $array['data'] ) );
$values = implode( ',',array_map( array( 'table','readyInsert' ),array_values( $array['data'] ) ) );
$query = "INSERT INTO `{$this->table}` ({$fields}) VALUES ({$values})";
break;
case "update":
$fields = implode( ',',array_map( array( 'table','readyUpdate' ),array_keys( $array['data'] ),array_values( $array['data'] ) ) );
$query = "UPDATE `{$this->table}` SET {$fields}";
break;
case "delete":
$query = "DELETE FROM `{$this->table}`";
break;
default:
trigger_error("TABLE BUILD() - Invalid query type '{$array['type']}'",E_USER_ERROR);
break;
}
$str = '';
$parts = array( 'where','order by','group by','having','limit' );
foreach( $parts as $part ) {
if ( isset( $array[$part] ) ) {
$str .= ' ' . strtoupper( $part ) . ' ' . $array[$part];
}
}
$query = $query . $str;
if ( isset( $array['bind'] ) ) {
$types = str_split( $array['bind']['types'] );
foreach( $types as $i => $type ) {
$str = '';
switch( $type ) {
case "i":
$str = "%d";
break;
case "b":
case "d":
case "u":
case "o":
$str = "%{$type}";
break;
case "c":
case "e":
case "f":
case "s":
case "x":
case "X":
$str = "'%{$type}'";
break;
case "a":
$str .= "'" . serialize( $array['bind']['data'][$i] ) . "'";
unset( $array['bind']['data'][$i] );
break;
default:
trigger_error("TABLE BUILD() - Invalid data type '{$type}'",E_USER_ERROR);
break;
}
if ( $pos = strpos( $query,'?' ) ) {
$query = substr_replace( $query,$str,$pos,1 );
}
}
array_unshift( $array['bind']['data'],$query );
$query = call_user_func_array( 'sprintf',$array['bind']['data'] );
}
return $query;
}
public function save( $index ) {
if ( is_null( $this->sql ) ) {
trigger_error("TABLE SAVE() - Please build a query before trying to save it",E_USER_ERROR);
}
$this->cache[$index] = $this->sql;
$this->sql = array();
return true;
}
public function execute() {
$sql = $this->sql;
$query = $this->build( $sql );
$this->sql = array();
return db::execute( $query,$sql['type'] );
}
}CLASS - class.mysqlresult.php:
class mysqlresult {
private $query;
private $type = null;
public $insert_id = null;
public $num_rows = null;
public $affected_rows = null;
public function __construct( $query,$type ) {
$this->query = $query;
$this->type = $type;
switch( $this->type ) {
case "insert":
$this->insert_id = $this->insertID();
case "update":
case "delete":
$this->affected_rows = $this->affectedRows();
break;
case "select":
$this->num_rows = $this->numRows();
break;
}
}
public function __destruct() {
if ( is_resource( $this->query ) ) {
mysql_free_result( $this->query );
}
}
public function fetch() {
return mysql_fetch_array( $this->query );
}
public function fetchAll() {
$fetch = array();
while( $row = $this->fetch() ) {
$fetch[] = $row;
}
return $fetch;
}
public function fetchAssoc() {
return mysql_fetch_assoc( $this->query );
}
public function fetchRow() {
return mysql_fetch_row( $this->query );
}
public function insertID() {
return mysql_insert_id( db::$_conn );
}
public function numRows() {
return mysql_num_rows( $this->query );
}
public function affectedRows() {
return mysql_affected_rows( db::$_conn );
}
}I didn't use the mysqli class because it didn't work as expected. I like the the bind_param functionality, but the way you have to define variables and assign values later by reference was annoying and caused problems. Also, fetching results was a pain in the ***.
Here as some usage examples:
//Select all data from a table named 'test'
$query = db::table('test')->query('select','*')->execute();
//Select certain fields in table named 'test'
$query = db::table('test')->query('select',array('id','name'))->execute();
//Select statement with where clause, this selects the id and name columns from the row with the id of 1
$query = db::table('test')->query('select',array('id','name'),'`id` = 1')->execute();
//Update and delete examples. This uses the primary key of the table to delete records. This really helps simplify some database calls
$data = array(
'name' => 'new name'
);
$query = db::table('test')->query('update',$data,1)->execute(); //this will update the row with id of 1 with a new name value.
$query = db::table('test')->query('update-multi',$data,"`value` = 'something' AND `other` = 'something'")->execute(); //update with where clause (usually used when update multiple rows or a row without using the primary key
$query = db::table('test')->query('delete',1)->execute(); deletes a row with the primary key column value of 1
$query = db::table('test')->query('delete-multi',"`value` = 'asdfas'")->execute(); //same as the update-multi, just deletes
//Insert example. just create an array with column names as keys and the value you want
$data = array(
'name' => 'name',
'data' => 'data'
);
$query = db::table('test')->query('insert',$data)->execute();
//Extra query elements like 'limit', 'order by', 'group by', 'having', ect. can be added by array (for ease of use) as the last param of the query function. it works for select,update-multi,delete-multi queries. if there is no where clause please fill the param with 'null'
$query = db::table('test')->query('select','*','where clause or null',array('limit'=>5,'order by'=>'`id` DESC'))->execute();
//Using the bind functionality
$value = 'Testing';
$query = db::table('test')->query('select','*','`column` = ? AND `id` = ?')->bind('si',$value,1)->execute(); //works almost the same as mysqli bind_param.
//bind works for select,update-multi,delete-multi queries.
//Using the save function
//You can save any query for specific table, just replace execute() with save(). you must provide an id to save with.
db::table('test')->query('select','*','`column` = ?')->bind('s','value')->save('test_select');
//you can execute this with the run function
$query = db::table('test')->run('test_select');
//you can get saved queries from the cache using the cache function
$query =& db::table('test')->cache('test_select');
$query['limit'] = '0, 5';
//after this is done you can send this to other classes or functions to change query properties like limit and order by. its all done by reference so you want have to redo the query, just use the run function.
//Getting query data
$query->fetch(); //same things as mysql_fetch_array()
while( $row = $query->fetch() ) { //example usage
print_r( $row );
}
$query->fetchAll(); //gets all data at once
$query->fetchAssoc(); //same as mysql_fetch_assoc()
$query->fetchRow(); //same as mysql_fetch_row()
$query->affectedRows(); //same as mysql_affected_rows(). this only works on insert,update,and delete queries
//or
$query->affected_rows;
$query->numRows(); //same as mysql_num_rows(). this only works for select queries
//or
$query->num_rows
$query->insertID(); //same as mysql_insert_id(). this only works for insert queries
//or
$query->insert_id;
