| TFlan |
12-20-2012 05:45 PM |
One Stop Shop: MySQL Abstraction Layer Class
I've been using this for years now, adding to it here and there, but mostly it hasn't changed since I first wrote it.
There are some bugs that I find throughout the course of using it, and I believe I've stomped most of them out, but obviously I haven't/can't use this class in every imaginable way, so disclaimer! It could not work.
Mainly looking for constructive feedback / sharing my class
(Yes I know that it is old code, when I'm not working on a new project I plan on rewriting it with PDO and/or MySQLi (preferably PDO))
There are a couple "givens/assumptions" with this class: - That every table has an 'id' column (hopefully auto_inc + primary)
- That you have PHP+MySQL properly installed
You can change the first assumption by changing the default value for the $order argument in the select function.
Make sure to change your database information in the __construct function!
Please feel free to comment, suggest, critic, praise, hate, love, insert-verb-here!
(PS: lame tab -> space, space -> tab form submission.. sorry for poor copy)
PHP Code:
<?php
/************************************************************************************************
* -= MySQL Class Library =-
* (c) date("Y") Tristian Flanagan
* Feel free to use and redistribute this code. But please keep this copyright notice and "manual"
*
* Provides a easy-to-use MySQL Database interface that is naturally resistant
* to SQL Injection and XSS without having to worry about anything!
*
* INSTALL:
* Just fill in your MySQL server login information in
* the _construct() function below, then require this
* file every time you need MySQL DB access!
*
* REQUIREMENTS:
* Every table must have an 'id' column, preferably a primary key, auto incremented column
* MySQL + PHP
*
*************************************************************************************************
* SELECT: select($table, $fields[, $condition = array('1' => '==1')[, $order = 'id'[, $orderDir = 'ASC'[, $process = 'ARRAY' [, $conditionOps = array('&&')]]]]]])
* $table = 'Table-Name';
* $fields = array('id', 'first-name', 'last-name'); // Must be an array for specific fields, or * for all
* $condition = array( // OPTIONAL - default: array('1' => '==1')
'first-name' => '==Tristian',
'last-name' => '==Flanagan');
* $order = 'first-name'; // OPTIONAL - default: 'id' (If you don't use 'id' for an index, gtfo, or... you could change this default I guess)
* $orderDir = 'DESC'; // OPTIONAL - default: 'ASC'
* $process = 'NUM'; // OPTIONAL - default: 'ASSOC' - mysql_fetch_[$process]
* $conditionOps = array('||'); // OPTIONAL - default: array('&&')
*
* $class_name->select($table, $fields, $condition, $order, $orderDir, $process);
*
*********
* UPDATE: update($table, $fields, $conditions [, $conditionOps = array('&&')]);
* $table = 'Table-Name';
* $fields = array('last-name' => 'Flanagan');
* $condition = array('first-name' => '==Tristian');
* $conditionOps = array('||'); // OPTIONAL - default: array('&&')
*
* $class_name->update($table, $fields, $condition);
*
*********
* INSERT: insert($table, $fields);
* $table = 'Table-Name';
* $fields = array('first-name' => 'Tristian', 'last-name' => 'Flanagan');
*
* $class_name->insert($table, $fields);
*
*********
* DELETE: delete($table, $fields);
* $table = 'Table-Name';
* $fields = array('first-name' => '==Tristian');
*
* $class_name->delete($table, $fields);
*********
* OPERATORS:
* == : equals to
* != : not equals to
* << : less than
* >> : greater than
* <= : greater than or equal
* >= : less than or equal
* %% : like - replaces all spaces with %
*
*********
* Extract Processed Results:
* 0 Rows: $r = false
* $rArr = false
* $err .= "0 rows affected"
*
* 1 Row: $r = mysql_query($q)
* $rArr['fieldname'] = mysql_result($r, 0, 'fieldname')
*
* +1 Rows: $r = mysql_query($q)
* $rArr[$i]['fieldname'] = mysql_result($r, $i, 'fieldname')
*
************************************************************************************************/
class mysql {
protected $db = array(); // database connection
public $q; // escaped query string
public $r; // raw query results
public $rArr = array(); // processed results
public $err; // all error messages
public function __construct(){
$this->db['host'] = 'localhost';
$this->db['user'] = 'root';
$this->db['pass'] = 'db_pass';
$this->db['name'] = 'db_name';
$this->db['conn'] = mysql_connect($this->db['host'], $this->db['user'], $this->db['pass']) or $this->err .= mysql_error();
mysql_select_db($this->db['name'], $this->db['conn']) or $this->err .= mysql_error();
}
protected function processResults($process){
$this->rArr = null;
if(mysql_num_rows($this->r)>0){
switch($process){
case 'ASSOC': $process = MYSQL_ASSOC; break;
case 'NUM': $process = MYSQL_NUM; break;
default: $process = MYSQL_BOTH; break;
}
if(mysql_num_rows($this->r)==1){
$results = mysql_fetch_array($this->r, $process);
foreach($results as $field => $value){
$this->rArr[$field] = $this->cleanDataOut($value);
}
}else{
$i = 0;
while($row = mysql_fetch_array($this->r, $process)){
foreach($row as $field => $value){
$this->rArr[$i][$field] = $this->cleanDataOut($value);
}
$i++;
}
}
}else{
$this->r = false;
$this->rArr = false;
$this->err .= "0 Rows Affected";
}
}
public function select($table, $fields, $condition = array('1' => '==1'), $order = 'id', $orderDir = 'ASC', $process = 'ASSOC', $conditionOps = array('&&')){
$this->q = 'SELECT ';
if($fields!='*'){
foreach($fields as $field){
$this->q .= '`'.$this->cleanDataIn($field).'`, ';
}
$this->q = substr($this->q, 0, -2);
}else{
$this->q .= '*';
}
$this->q .= ' FROM `'.$this->cleanDataIn($table).'` WHERE ';
$i=0;
foreach($condition as $field => $value){
$isSearch = false;
$this->q .= ($i==0 ? '' : (count($conditionOps==1) ? $conditionOps[0] : $conditionOps[$i-1])).' '.(is_numeric($field) ? $this->cleanDataIn($field) : '`'.$this->cleanDataIn($field).'`').' ';
switch(substr($value, 0, 2)){
case "==": $this->q .= '= '; break;
case "!=": $this->q .= '!= '; break;
case ">>": $this->q .= '> '; break;
case "<<": $this->q .= '< '; break;
case ">=": $this->q .= '>= '; break;
case "<=": $this->q .= '<= '; break;
case "%%":
$this->q .= 'LIKE ';
$isSearch = true;
break;
default: $this->err .= 'Invalid conditional operator.'; break;
}
$this->q .= (is_numeric(substr($value, 2)) ? ($isSearch ? '\'%' : '').$this->cleanDataIn(substr($value, 2)).($isSearch ? '%\'' : '') : '\''.($isSearch ? '%' : '').$this->cleanDataIn(($isSearch ? str_replace(' ', '%', substr($value, 2)) : substr($value, 2))).($isSearch ? '%' : '').'\'').' ';
$i++;
}
$this->q .= ' ORDER BY `'.$this->cleanDataIn($order).'` '.($orderDir=="DESC" ? 'DESC' : 'ASC');
$this->r = mysql_query($this->q, $this->db['conn']) or $this->err .= mysql_error();
$this->processResults($process);
}
public function update($table, $fields, $condition = array('1' => '==1'), $conditionOps = array('&&')){
$this->q = 'UPDATE `'.$this->cleanDataIn($table).'` SET ';
foreach($fields as $key => $value){
$this->q .= '`'.$this->cleanDataIn($key).'` = '.(is_numeric($value) ? $this->cleanDataIn($value) : '\''.$this->cleanDataIn($value).'\'').', ';
}
$this->q = substr($this->q, 0, -2);
$this->q .= ' WHERE ';
$i=0;
foreach($condition as $field => $value){
$isSearch = false;
$this->q .= ($i==0 ? '' : (count($conditionOps==1) ? $conditionOps[0] : $conditionOps[$i-1])).' '.(is_numeric($field) ? $this->cleanDataIn($field) : '`'.$this->cleanDataIn($field).'`').' ';
switch(substr($value, 0, 2)){
case "==": $this->q .= '= '; break;
case "!=": $this->q .= '!= '; break;
case ">>": $this->q .= '> '; break;
case "<<": $this->q .= '< '; break;
case ">=": $this->q .= '>= '; break;
case "<=": $this->q .= '<= '; break;
case "%%":
$this->q .= 'LIKE ';
$isSearch = true;
break;
default: $this->err .= 'Invalid conditional operator.'; break;
}
$this->q .= (is_numeric($value) ? ($isSearch ? '\'%' : '').$this->cleanDataIn(substr($value, 2)).($isSearch ? '%\'' : '') : '\''.($isSearch ? '%' : '').$this->cleanDataIn(($isSearch ? str_replace(' ', '%', substr($value, 2)) : substr($value, 2))).($isSearch ? '%' : '').'\'').' ';
$i++;
}
$this->r = mysql_query($this->q, $this->db['conn']) or $this->err .= mysql_error();
}
public function insert($table, $fields){
$keys = '';
$values = '';
$this->q = 'INSERT INTO `'.$this->cleanDataIn($table).'` (';
foreach($fields as $field => $value){
$keys .= (is_numeric($field) ? $this->cleanDataIn($field) : '`'.$this->cleanDataIn($field).'`').', ';
$values .= (is_numeric($value) ? $this->cleanDataIn($value) : '\''.$this->cleanDataIn($value).'\'').', ';
}
$this->q .= substr($keys, 0, -2).') VALUES ('.substr($values, 0, -2).')';
$this->r = mysql_query($this->q, $this->db['conn']) or $this->err .= mysql_error();
}
public function delete($table, $fields){
$this->q = 'DELETE FROM '.$this->cleanDataIn($table).' WHERE ';
foreach($fields as $field => $value){
$isSearch = false;
$this->q .= '`'.$this->cleanDataIn($field).'` ';
switch(substr($value, 0, 2)){
case "==": $this->q .= '= '; break;
case "!=": $this->q .= '!= '; break;
case ">>": $this->q .= '> '; break;
case "<<": $this->q .= '< '; break;
case ">=": $this->q .= '>= '; break;
case "<=": $this->q .= '<= '; break;
case "%%":
$this->q .= 'LIKE ';
$isSearch = true;
break;
default: $this->err .= 'Invalid conditional operator.'; break;
}
$this->q .= (is_numeric($value) ? ($isSearch ? '\'%' : '').$this->cleanDataIn(substr($value, 2)).($isSearch ? '%\'' : '') : '\''.($isSearch ? '%' : '').$this->cleanDataIn(($isSearch ? str_replace(' ', '%', substr($value, 2)) : substr($value, 2))).($isSearch ? '%' : '').'\'').' ';
}
$this->r = mysql_query($this->q, $this->db['conn']) or $this->err .= mysql_error();
}
protected function cleanDataIn($data){
return mysql_real_escape_string(trim($data));
}
protected function cleanDataOut($data){
return trim(htmlentities(strip_tags($data)));
}
public function close(){
is_resource($this->r) ? mysql_free_result($this->r) : '';
mysql_close($this->db['conn']) or $this->err .= mysql_error();
echo '<!--'.$this->err.'-->';
}
}
/* DISCLAIMER: NOTHING IS IMPERVIOUS */
?>
|
