Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 1 of 1
  1. #1
    New Coder
    Join Date
    Dec 2012
    Location
    USA
    Posts
    82
    Thanks
    3
    Thanked 17 Times in 17 Posts

    Thumbs up 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:
    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)

    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->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->'SELECT ';
            if(
    $fields!='*'){
                foreach(
    $fields as $field){
                    
    $this->.= '`'.$this->cleanDataIn($field).'`, ';
                }
                
    $this->substr($this->q0, -2);
            }else{
                
    $this->.= '*';
            }
            
    $this->.= ' FROM `'.$this->cleanDataIn($table).'` WHERE ';
            
    $i=0;
                foreach(
    $condition as $field => $value){
                    
    $isSearch false;
                    
    $this->.= ($i=='' : (count($conditionOps==1) ? $conditionOps[0] : $conditionOps[$i-1])).' '.(is_numeric($field) ? $this->cleanDataIn($field) : '`'.$this->cleanDataIn($field).'`').' ';
                    switch(
    substr($value02)){
                        case 
    "=="$this->.= '= ';    break;
                        case 
    "!="$this->.= '!= ';    break;
                        case 
    ">>"$this->.= '> ';    break;
                        case 
    "<<"$this->.= '< ';    break;
                        case 
    ">="$this->.= '>= ';    break;
                        case 
    "<="$this->.= '<= ';    break;
                        case 
    "%%":
                            
    $this->.= 'LIKE ';
                            
    $isSearch true;
                        break;
                        default: 
    $this->err .= 'Invalid conditional operator.'; break;
                    }
                
    $this->.= (is_numeric(substr($value2)) ? ($isSearch '\'%' '').$this->cleanDataIn(substr($value2)).($isSearch '%\'' '') : '\''.($isSearch '%' '').$this->cleanDataIn(($isSearch str_replace(' ''%'substr($value2)) : substr($value2))).($isSearch '%' '').'\'').' ';
                
    $i++;
            }
            
    $this->.= ' ORDER BY `'.$this->cleanDataIn($order).'` '.($orderDir=="DESC" 'DESC' 'ASC');

            
    $this->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->'UPDATE `'.$this->cleanDataIn($table).'` SET ';
                foreach(
    $fields as $key => $value){
                    
    $this->.= '`'.$this->cleanDataIn($key).'` = '.(is_numeric($value) ? $this->cleanDataIn($value) : '\''.$this->cleanDataIn($value).'\'').', ';
                }
            
    $this->substr($this->q0, -2);
            
    $this->.= ' WHERE ';
            
    $i=0;
            foreach(
    $condition as $field => $value){
                
    $isSearch false;
                
    $this->.= ($i=='' : (count($conditionOps==1) ? $conditionOps[0] : $conditionOps[$i-1])).' '.(is_numeric($field) ? $this->cleanDataIn($field) : '`'.$this->cleanDataIn($field).'`').' ';
                switch(
    substr($value02)){
                    case 
    "=="$this->.= '= ';    break;
                    case 
    "!="$this->.= '!= ';    break;
                    case 
    ">>"$this->.= '> ';    break;
                    case 
    "<<"$this->.= '< ';    break;
                    case 
    ">="$this->.= '>= ';    break;
                    case 
    "<="$this->.= '<= ';    break;
                    case 
    "%%":
                        
    $this->.= 'LIKE ';
                        
    $isSearch true;
                    break;
                    default: 
    $this->err .= 'Invalid conditional operator.'; break;
                }
                
    $this->.= (is_numeric($value) ? ($isSearch '\'%' '').$this->cleanDataIn(substr($value2)).($isSearch '%\'' '') : '\''.($isSearch '%' '').$this->cleanDataIn(($isSearch str_replace(' ''%'substr($value2)) : substr($value2))).($isSearch '%' '').'\'').' ';
                
    $i++;
            }

            
    $this->mysql_query($this->q$this->db['conn']) or $this->err .= mysql_error();
        }

        public function 
    insert($table$fields){
            
    $keys '';
            
    $values '';
            
    $this->'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->.= substr($keys0, -2).') VALUES ('.substr($values0, -2).')';

            
    $this->mysql_query($this->q$this->db['conn']) or $this->err .= mysql_error();
        }

        public function 
    delete($table$fields){
            
    $this->'DELETE FROM '.$this->cleanDataIn($table).' WHERE ';
                foreach(
    $fields as $field => $value){
                    
    $isSearch false;
                    
    $this->.= '`'.$this->cleanDataIn($field).'` ';
                    switch(
    substr($value02)){
                        case 
    "=="$this->.= '= ';    break;
                        case 
    "!="$this->.= '!= ';    break;
                        case 
    ">>"$this->.= '> ';    break;
                        case 
    "<<"$this->.= '< ';    break;
                        case 
    ">="$this->.= '>= ';    break;
                        case 
    "<="$this->.= '<= ';    break;
                        case 
    "%%":
                            
    $this->.= 'LIKE ';
                            
    $isSearch true;
                        break;
                        default: 
    $this->err .= 'Invalid conditional operator.'; break;
                    }
                
    $this->.= (is_numeric($value) ? ($isSearch '\'%' '').$this->cleanDataIn(substr($value2)).($isSearch '%\'' '') : '\''.($isSearch '%' '').$this->cleanDataIn(($isSearch str_replace(' ''%'substr($value2)) : substr($value2))).($isSearch '%' '').'\'').' ';
            }

            
    $this->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 */
    ?>
    Last edited by TFlan; 12-20-2012 at 08:34 PM.


 

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •