Your New MySQL Abstraction Layer Class

T

TFlan

I could change this in my User CP.
Reaction score
64
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: 
<?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 */
?>
 
You must log in or register to reply here.
General chit-chat
Help Users
  • No one is chatting at the moment.
  • Ghan Ghan:
    Still lurking
     +3
  • The Helper The Helper:
    I am great and it is fantastic to see you my friend!
     +1
  • The Helper The Helper:
    Here is a great appetizer recipe Texas style! https://www.thehelper.net/threads/recipe-chicken-stuffed-poblano-peppers.194086/
  • The Helper The Helper:
    If you are new to the site please check out the Recipe and Food Forum https://www.thehelper.net/forums/recipes-and-food.220/
  • Monovertex Monovertex:
    How come you're so into recipes lately? Never saw this much interest in this topic in the old days of TH.net
  • Monovertex Monovertex:
    Hmm, how do I change my signature?
  • tom_mai78101 tom_mai78101:
    Signatures can be edit in your account profile. As for the old stuffs, I'm thinking it's because Blizzard is now under Microsoft, and because of Microsoft Xbox going the way it is, it's dreadful.
  • The Helper The Helper:
    I am not big on the recipes I am just promoting them - I use the site as a practice place promoting stuff
     +2
  • Monovertex Monovertex:
    @tom_mai78101 I must be blind. If I go on my profile I don't see any area to edit the signature; If I go to account details (settings) I don't see any signature area either.
  • The Helper The Helper:
    You can get there if you click the bell icon (alerts) and choose preferences from the bottom, signature will be in the menu on the left there https://www.thehelper.net/account/preferences
  • The Helper The Helper:
    I think I need to split the Sci/Tech news forum into 2 one for Science and one for Tech but I am hating all the moving of posts I would have to do
  • The Helper The Helper:
    New dessert for Summer - https://www.thehelper.net/threads/recipe-lemon-blueberry-layer-cheesecake.194113/
  • The Helper The Helper:
    What is up Old Mountain Shadow?
  • The Helper The Helper:
    Happy Thursday!
     +1
  • Varine Varine:
    Crazy how much 3d printing has come in the last few years. Sad that it's not as easily modifiable though
  • Varine Varine:
    I bought an Ender 3 during the pandemic and tinkered with it all the time. Just bought a Sovol, not as easy. I'm trying to make it use a different nozzle because I have a fuck ton of Volcanos, and they use what is basically a modified volcano that is just a smidge longer, and almost every part on this thing needs to be redone to make it work
  • Varine Varine:
    Luckily I have a 3d printer for that, I guess. But it's ridiculous. The regular volcanos are 21mm, these Sovol versions are about 23.5mm
  • Varine Varine:
    So, 2.5mm longer. But the thing that measures the bed is about 1.5mm above the nozzle, so if I swap it with a volcano then I'm 1mm behind it. So cool, new bracket to swap that, but THEN the fan shroud to direct air at the part is ALSO going to be .5mm to low, and so I need to redo that, but by doing that it is a little bit off where it should be blowing and it's throwing it at the heating block instead of the part, and fuck man
  • Varine Varine:
    I didn't realize they designed this entire thing to NOT be modded. I would have just got a fucking Bambu if I knew that, the whole point was I could fuck with this. And no one else makes shit for Sovol so I have to go through them, and they have... interesting pricing models. So I have a new extruder altogether that I'm taking apart and going to just design a whole new one to use my nozzles. Dumb design.
  • Varine Varine:
    Can't just buy a new heatblock, you need to get a whole hotend - so block, heater cartridge, thermistor, heatbreak, and nozzle. And they put this fucking paste in there so I can't take the thermistor or cartridge out with any ease, that's 30 dollars. Or you can get the whole extrudor with the direct driver AND that heatblock for like 50, but you still can't get any of it to come apart
  • Varine Varine:
    Partsbuilt has individual parts I found but they're expensive. I think I can get bits swapped around and make this work with generic shit though
  • Ghan Ghan:
    Heard Houston got hit pretty bad by storms last night. Hope all is well with TH.
  • The Helper The Helper:
    Power back on finally - all is good here no damage
     +2
  • V-SNES V-SNES:
    Happy Friday!
     +1
  • The Helper The Helper:
    New recipe is another summer dessert Berry and Peach Cheesecake - https://www.thehelper.net/threads/recipe-berry-and-peach-cheesecake.194169/

      The Helper Discord

      Members online

      Online now:
      372 (members: 1, guests: 371)

      Share this page

      Affiliates

      Hive Workshop NUON Dome World Editor Tutorials

      Network Sponsors

      Apex Steel Pipe - Buys and sells Steel Pipe.
      Top