Your New MySQL Abstraction Layer Class

[TFlan]

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:

1. That every table has an 'id' column (hopefully auto_inc + primary)
2. 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, seems to of deleted all my tabs.. )

<?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 = array('id')[, $orderDir = array('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= array('first-name');// OPTIONAL - default: 'id' (If you don't use 'id' for an index, gtfo, or... you could change this default I guess)
*$orderDir= array('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
protected$debug = true;// turn debuging off/on (false/true)
 
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 = array('id'), $orderDir = array('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';
$i = 0;
foreach($order as $key => $value){
$this->q .= ' `'.$this->cleanDataIn($value).'` '.(count($orderDir)==1 ? 'ASC' : $this->cleanDataIn($orderDir[$i])).',';
$i++;
}
$this->q = substr($this->q, 0, -1);
 
$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();
if($this->debug){ echo '<!-- MySQL Errors: '.($this->err=='' ? 'No Errors! :)' : $this->err).' -->'; }
}
}
/* DISCLAIMER: NOTHING IS IMPERVIOUS */
?>