| abiko |
02-27-2008 11:16 AM |
MySQL class
Here is my MySQL class - please tell me what do you think about it.
For basic usage:
PHP Code:
<?php
$db = new nnDB( mysql_host, mysql_user, mysql_pass, mysql_db);
// Select:
$db->Select( array( 'items' => 'items1, item3',
'from' => 'table1',
'where' => array( 'id' => '2'),
// Or
'wheres' => 'id > 1',
'order' => 'id DESC',
'limit' => '0, 12'
)
);
// Left Join
$db->leftJoin ( array( 'items' => 't1.item1, t2.item2, t2.item4',
'from' => 'table1 as t1',
'leftJoin' => array('table2 as t2 on t2.id = t1.sub_id'),
'where' => array( 'op' => 'active'),
'limit' => '0,12'
)
);
// Update table
$sql_prep = array ( 'item1' => 'value1', 'item15' => 'value2');
$update = array( 'table' => 'table1', 'set' => $sql_prep, 'where' => array( 'id' => '2');
$db->Update( $update );
// Delete
$data = array( 'table' => 'table1', 'id' => 'table_id', 'value' => '1');
$db->Delete( $data );
// Insert
$fields = array( 'id' => '', 'item1' => 'value 1', 'item2' => 'value2' );
$db->Insert( array( 'tableName' => 'table1', 'fields' => $fields ) );
?>
And the class at last:
PHP Code:
<?php
class nnDB {
protected $host;
protected $user;
protected $pass;
protected $db;
protected $query_id;
protected $connection_id = "";
protected $error;
protected $podaci;
protected $sql;
public function __construct($host, $user, $pass, $db) {
$this->host = $host;
$this->user = $user;
$this->pass = $pass;
$this->db = $db;
$this->connectToDB();
}
////==========================
/// Query the databaz0r
////==========================
public function &Query($query) {
$this->sql = $query;
if ( !empty( $query) ) {
$this->query_id = @mysql_query($query, $this->connection_id);
if ( $this->query_id ) {
return true;
} else {
return false;
}
}
}
////==========================
/// Select function
////==========================
//// wherecont = AND, OR ... default AND
//// whereop = =, >, < ... default =
public function &Select ( $stuff ) {
$query = "SELECT ". $stuff['items'] ."\n";
$query .= "FROM ". $stuff['from'] ."\n ";
$whereCond = $stuff['wherecond'];
$whereOp = $stuff['whereop'];
if ( empty($whereCond) ) $whereCond = '';
if ( empty( $whereOp) ) $whereOp = '';
if ( isset( $stuff['where'] ) ) $query .= "WHERE ". $this->__generateSQLWhere( $stuff['where'], $whereCond, $whereOp ) ."\n";
if ( isset( $stuff['wheres'] ) ) $query .= "WHERE ".$stuff['wheres']." \n";
if ( isset( $stuff['order']) ) $query .= "ORDER BY ".$stuff['order']." \n";
if ( isset( $stuff['limit'] ) ) $query .= "LIMIT ". $stuff['limit'] ."\n";
$this->Query( $query );
unset ($query);
}
////==========================
/// Left Join function
////==========================
/*
$db->LeftJoin( $type = 'Select', array( 'items' => 'T.ITEM',
'from' => 'TABLE as t',
//// Single LeftJoin record :)
'leftJoin' => 'nn_users AS u ON u.id = gal_author',
//// Multiple Left Join tables :)
'leftJoin' => array( 'TABLE2 AS 2 ON T.id = 2.id_o',
'TABLE3 AS 3 ON 2.z = 3.rr'
);
//// WHERE part is a bit modified :) ),
'where' => array( 'tableRow' => 'tableRowData',
'tableRow2' => 'tableRow2Data'
),
// WHERE OPERATOR - not necessary
'whereop' => 'LIKE',
//// And the last part of the SQL
///// orderBy is optional
////// orderType is a must when having orderBy
'orderBy' => 'TABLE STUFF ';,
'orderType' => ' ASC DESC ';,
////////// Also we have
// LIMIT ME BABY
'limit' => '0, 30'
)
);
*/
public function LeftJoin( $stuff, $type = 'Select', $debug=0) {
$query = "SELECT ". $stuff['items'] ." \n";
$query .= "FROM ". $stuff['from'] ." \n";
///// Start Left Join SQL generation :)
if ( is_array ($stuff['leftJoin'] ) ) {
foreach ( $stuff['leftJoin'] as $leftJoin ) {
$query .= "LEFT JOIN ".$leftJoin." \n";
}
} else {
$query .= "LEFT JOIN ".$stuff['leftJoin']." \n";
}
//// We go secure our data (WHERE) with qs function
$where = $stuff['where'];
if ( is_array( $where)) {
$whereCond = $stuff['wherecond'];
$whereOp = $stuff['whereop'];
if ( empty($whereCond) ) $whereCond = '';
if ( empty( $whereOp) ) $whereOp = '';
$query .= "WHERE ".$this->__generateSQLWhere( $stuff['where'], $whereCond, $whereOp ) ."\n";
} else {
$where2 = $stuff['wheres'];
if ( !empty( $where2) ) {
$query .= "WHERE ".$where2."\n";
}
}
//$query .= "WHERE ". $stuff['where'] ." \n";
//// End of the Left Join SQL generation is near :)
if ( !empty($stuff['orderBy']) && !empty($stuff['orderType']) ) $query .= "ORDER BY " .$stuff['orderBy'] ." " .$stuff['orderType'] ."\n";
if ( !empty($stuff['limit'] ) ) $query .= "LIMIT ". $stuff['limit'] ."\n";
if ( $debug == 1) {
echo $type ." <br /> \n";
print_r ( $stuff);
echo "<br /> \n";
die ( $query );
}
//// Query the Databaz0r
$this->Query( $query );
unset( $query);
}
////==========================
/// Fetch the result
////==========================
public function Fetch( $type='assoc') {
switch($type){
case 'array':
$this->row = mysql_fetch_array($this->query_id);
break;
case 'assoc':
$this->row = mysql_fetch_assoc($this->query_id);
break;
case 'object':
$this->row = mysql_fetch_object($this->query_id);
break;
case 'row':
$this->row = mysql_fetch_row($this->query_id);
break;
case 'fields':
if (isset($this->row)) unset ($this->row);
$i = 0;
while ($i < mysql_num_fields($this->query_id)) {
$this->row[] = mysql_fetch_field($this->query_id, $i);
$i++;
}
break;
default:
$this->row = mysql_fetch_row($this->query_id);
break;
}
return $this->row;
}
public function &vadi () {
$this->Fetch();
}
////==========================
/// Free stuff
////==========================
public function Free () {
mysql_free_result( $this->query_id );
}
////==========================
//// Vrši update polja u tablici
////==========================
public function Update( $stuff ) {
// Make teh query
$query = "UPDATE ". $stuff['table']." ";
$query .= "SET ". $this->__generateSQLWhere($stuff['set'], ','); //." ";
$query .= "WHERE ". $this->__generateSQLWhere($stuff['where']); //." ";
$query .= "LIMIT 1";
// Execute teh query
// $this->query_id = @mysql_query( $query, $this->connection_id);
if ( $this->Query( $query ) ) {
return true;
} else {
return false;
}
unset( $query);
}
public function Updater( $stuff ) {
// Make teh query
$query = "UPDATE ". $stuff['table']." ";
$query .= "SET ". $this->__generateSQL($stuff['set'], ','); //." ";
$query .= "WHERE ". $this->__generateSQL($stuff['where']); //." ";
$query .= "LIMIT 1";
// Execute teh query
// $this->query_id = @mysql_query( $query, $this->connection_id);
$this->Query( $query );
unset( $query);
}
////==========================
/// Insert - ubacujepodatke u tablicu
////==========================
public function Insert( $data) {
$sql = $this->sql_generate_insert( $data['fields'], $data['tableName'] );
$this->Query( $sql );
return true;
unset( $sql);
}
////==========================
/// Briše podatke iz tablice - 1 RED SAMO!!
////==========================
public function Delete( $data ) {
$sql = $this->sql_delete_row( $data['tableName'], $data['id'], $data['value'] );
$sql .= " LIMIT 1; ";
$this->Query( $sql );
}
////==========================
/// Izbacuje kolicinu podataka
////==========================
public function getSize () {
return mysql_num_rows($this->query_id);
}
public function getCount( $table ) {
}
////==========================
/// Zatvara konekciju sa bazom. I kraj!
////==========================
public function __deconstruct() {
mysql_close($this->connection_id);
}
//======================================
//
// P R I V A T E F U N C T I O N S
//
//======================================
private function connectToDB() {
if( !$this->connection_id = mysql_connect($this->host, $this->user, $this->pass) ) {
echo mysql_error();
trigger_error('Could not connect to server');
}
//// Setting the names UTF8
mysql_query ('SET NAMES "utf8"', $this->connection_id);
//// Selecting the database
if ( !@mysql_select_db($this->db, $this->connection_id) ) echo ("ERROR: Cannot find database ".$this->db);
}
private function sql_delete_row($tbl_name,$id_name,$id){
$sql = "DELETE FROM $tbl_name WHERE $id_name = '$id'";
return $sql;
}
private function sql_generate_insert(&$FIELDS,$tbl_name){
reset ($FIELDS);
$sql = "INSERT INTO $tbl_name (";
$delim = "";
foreach ($FIELDS as $field=>$value) {
$sql .= $delim.$field;
$val .= $delim.$this->qs($value);
$delim = ", ";
}
$sql=$sql.") VALUES (".$val.")";
return $sql;
}
private function qs($value) {
if (get_magic_quotes_gpc()) {
$value = stripslashes($value);
}
if (!is_numeric($value) || is_string($value)) {
$value = "'" . mysql_real_escape_string($value) . "'";
}
return $value;
}
private function __generateSQLWhere ( $arej, $zadnjiVezni = '', $sredina = '' ) {
if ( $zadnjiVezni == '') $zadnjiVezni = "AND";
if ($sredina == '' ) $sredina = '=';
if ( is_array( $arej ) ) {
$count = 0;
$c = count( $arej)-1;
foreach( $arej as $tName => $tVal ) {
if( $count == $c) {
$vali .= $tName." ".$sredina." ".$this->qs($tVal)." ";
} elseif( $count < $c ) {
$vali .= $tName." ".$sredina." ".$this->qs($tVal)." ".$zadnjiVezni." ";
}
$count++;
}
}
return $vali;
}
private function __generateSQL ( $arej, $zadnjiVezni = '', $sredina = '' ) {
if ( $zadnjiVezni == '') $zadnjiVezni = "AND";
if ($sredina == '' ) $sredina = '=';
if ( is_array( $arej ) ) {
$count = 0;
$c = count( $arej)-1;
foreach( $arej as $tName => $tVal ) {
if( $count == $c) {
$vali .= $tName." ".$sredina." ".$tVal." ";
} elseif( $count < $c ) {
$vali .= $tName." ".$sredina." ".$tVal." ".$zadnjiVezni." ";
}
$count++;
}
}
return $vali;
}
////==========================
/// Error stranica
////==========================
public function showError() {
$out = '
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>nnDB MySQL - Error Report </title>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
</head>
<body>
Error:<br />
<b> '. mysql_error() .'</b>
We have encoutered an error in your SQL code:<br/><br/><br/><br/><br/>
<div style="text-size:28px; font-family:Arial; color:#BC1213;">
'. $this->sql.'
</div>
<p></p>
<i>Vanilla - nnDB 0.1 </i>
</body>
</html>';
die($out);
}
}
?>
|
