A MySQL PHP5 class

MySQL.5.class.php

<?php
/**
* @note MySQL class::PHP5::php-mysql-class
* @author Stefan Antonowicz
*/
class MySQL {    
    private static $db = '';
    /**
    * Acceptable connection options - only static and persistent in this case
    * @access private
    * @var array
    */
    private $acceptable_types = array( 'static', 'persistent' );
    
    /**
    * The tags which should not be stripped, passed to the PHP strip_tags() function
    * @access public
    * @var array
    */    
    public $clean_allowed = array( );

    /**
    * The DSN for the constructor i.e. <connection_type>://<username>:<password>@<host>/<db_name>
    * @access public
    * @var string
    */      
    public $dsn = '';
    /**
    * Whether or not magic_quotes is set
    * @access private
    * @var boolean
    */       
    private $quotes = FALSE;
    /**
    * The type of connector (either 'static' or 'persistent')
    * @link $acceptable_types
    * @access private
    * @var string
    */          
   private $connect_type = '';

    /**
    * The MySQL result passed in by mysql_query
    * @access public
    * @var string
    */ 
    
    public $result = '';

    /**
    * Constructor sets up {@link $dsn}, {@link $connect_type} and {@link $quotes}
    * @param string $dsn the DSN
    * @param string $type either static or dynamic 
    * @return boolean
    */        
    function __construct( $dsn, $type='static' ) {
        $this->connect_type = in_array( $type, $this->acceptable_types ) ? $type : 'static';
        $this->dsn = $dsn;
        $this->quotes = get_magic_quotes_gpc( );

        if(! $this->connect( ) ) return( FALSE );
        return( TRUE );
    }
        
    /**
    * Attempt to connect - die and return error message if unable to connect
    * @return boolean
    */         
    private function connect( ) {
        extract( parse_url( $this->dsn ) );
        switch( $this->connect_type ) {
            case 'static':
                if (! ( $conn = mysql_connect( $host,$user,$pass, true ) ) )
                die ( mysql_errno().' : '.mysql_error() );            
            break;
            
            case 'persistent':
                if (! ( $conn = mysql_pconnect( $host,$user,$pass, true ) ) )
                die ( mysql_errno().' : '.mysql_error() );            
            break;
        }
        
        $this->db = str_replace( '/', '', $path );
        if (! mysql_select_db( $this->db,$conn ) )
            die ( mysql_errno().' : '.mysql_error() );
            
        $this->conn = $conn;
        return( TRUE );
    }
    
    /**
    * Static function to cache a MySQL connection.
    * @return resource|string
    */       
    public static function staticDB( ) {
        if(! MySQL::$db ) {
            MySQL::$db = new MySQL( DSN );
        }
        
        return( MySQL::$db );
    }
        
     
    /**
    * Run a query against the mysql connection.  Sets mysql result in {@link $result}
    * @param string $sql a sql statement
    * @return resource|boolean
    * @example $this->query( "SELECT 1 FROM foo" );
    */        
     
    public function query( $sql='' ) {
        if( $sql ) $this->sql = $sql;
        if( empty( $this->sql ) ) 
            die( 'No SQL statement specified' );
            
        if( $this->conn && ! $this->checkResource( $this->conn ) )
            return( false );

        if(! ( $res = mysql_query( $this->sql,  $this->conn ) ) )
            $this->error( );
        
        $this->result = $res;
        $this->sql = NULL;
        return( TRUE );
    }

    /**
    * Check the MySQL resource to be sure it's valid.  Die if the connection is invalid.
    * @return boolean   
    */            
    private function checkResource( ) {
        if(! is_resource( $this->conn ) ) 
            die( "The database $this->db isn't a resource" );
                
        if ( get_resource_type( $this->conn ) !== 'mysql link' && get_resource_type( $this->conn ) !== 'mysql link persistent' )
            die ( "The resource $this->db isn't a mysql connection - it's a type: " . get_resource_type( $this->conn ) );
                
        return( TRUE );
    }
 
    /**
    * Simple error wrapper.  Die with error information.  Here in case one wants to handle errors differently.
    */                 
    private function error( ) {
        die( mysql_errno($this->conn ).' ) '. mysql_error( $this->conn ).
        '<hr />query was: ' . $this->sql . ' on DB '. $this->db );
    }

    /**
    * Cleaning function for data that might be written to the database
    *@link $clean_allowed
    *@param string
    */       
    private function clean( $str ) {
        $str = strip_tags( $str, $this->clean_allowed );
        if(! $this->quotes ) $str = mysql_real_escape_string( $str );
          
        return( $str );
    }
           
    /**
    * Submission processing - either UPDATE or INSERT, depending on whether the id is set.  Returns
    * the ID of the UPDATE or the mysql_insert_id().  Hint: if you want to make a really fast form, make
    * the name of the input elements the same name as the mysql fields, and just process the $_POST directly.
    * i.e. if you have a mysql field named foo in a table bar, make the input element like so: 
    * <input type="text" name="foo"> and run this method as follows on submit: 
    * $db->process_submission( 'bar', $_POST );
    *
    *@param string $table the name of the MySQL table you're writing data to
    *@param array $submission an array of fields and values you wish to insert into or update.  should
    *  be in format fieldname=>fieldvalue
    *@param array $id the name of the primary key field and it's value i.e. fooID=>7.  If set, assumes
    *   an UPDATE, if not set assumes an INSERT
    *@return integer
    */
    public function process_submission( $table, $submission, $id=array( ) ) {
        $fieldArray = array( );
        $this->sql = "SELECT * FROM $table LIMIT 0";         
        if(! $this->query( ) ) $this->error( );
       
        /**
        * Get the array of fields from the table, and free the result
        */
        for( $i = 0; $i < mysql_num_fields( $this->result ); ++$i ) {
            $info = mysql_fetch_field( $this->result );
            $fieldArray[$info->name] = 1; 
        }
            
        /**
        * Check the submission against this array of fields.  If a 
        * field in submission is in the array of fields, they match up
        * Using keys here because it's faster.  Also, cleaning
        * the values here.
        */
        foreach( $submission as $key=>$val ) {
            if( array_key_exists( $key, $fieldArray ) ) {
                $val = $this->clean( $val );
                $fields[$key] = "'$val'";
            }
        }
            
        if (! count( $fields ) ) {
            /** none of the fields match, so we can bail out **/
            return( FALSE );
            //die( var_dump( $fieldArray )."<hr />None of the submission fields match the table fields for table $table on database $db.  The table fields are listed above" );
        }
            
        if ( empty( $id ) ) {
            $this->sql = "INSERT INTO `$table` (" . implode(',', array_keys( $fields ) ). ") VALUES (". implode(',',array_values( $fields ) ). ")";
            $this->query( );
            if( mysql_affected_rows( $this->conn ) > 0 ) {
                return( mysql_insert_id( $this->conn ) );
            } else {
                return( 0 );
            }
        } else {
            $db_id = array_keys( $id );
            $db_val = array_values( $id );
            
            foreach ($fields as $key=>$val) {
                $update[] = "$key = $val";
            }
            
            $this->sql = "UPDATE `$table` SET " . implode( ',', $update ) . " where $db_id[0] = $db_val[0]";
            $this->query( );
            return( $db_val[0] );
        }
        return( false );
    }
    
    /** 
    table - name of table
    insert - the data to insert. should be field=>value
    update - the data to update on duplicate key.  should be field=>value
    **/
    public function insert_update( $table, $insert, $update ) {
        $fieldArray = array( );
        $this->sql = "SELECT * FROM $table LIMIT 0";         
        if(! $this->query( ) ) $this->error( );
       
        /**
        * Get the array of fields from the table, and free the result
        */
        for( $i = 0; $i < mysql_num_fields( $this->result ); ++$i ) {
            $info = mysql_fetch_field( $this->result );
            $fieldArray[$info->name] = 1; 
        }
            
        /**
        * Check the submission against this array of fields.  If a 
        * field in submission is in the array of fields, they match up
        * Using keys here because it's faster.  Also, cleaning
        * the values here.
        */
        $i_sql = array( );
        $u_sql = array( );
        
        foreach( $insert as $key=>$val ) {
            if( array_key_exists( $key, $fieldArray ) ) {
                $val = $this->clean( $val );
                $i_sql[$key] = "'$val'";
            }
        }
        
        foreach( $update as $key=>$val ) {
            if( array_key_exists( $key, $fieldArray ) ) {
                $val = $this->clean( $val );
                $u_sql[] = "$key='$val'";
            }
        }
        
        if(! sizeof( $i_sql ) || ! sizeof( $u_sql ) ) {
            return( FALSE );
            //die( var_dump( $fieldArray )."<hr />None of the submission fields match the table fields for table $table on database $db.  The table fields are listed above" );
        } 
        
        $this->sql = "INSERT INTO `$table` (" . implode(',', array_keys( $i_sql ) ). ") 
                      VALUES (". implode(',',array_values( $i_sql) ). ") 
                      ON DUPLICATE KEY UPDATE " . implode( ',', $u_sql );

        $this->query( );
        
        return( TRUE );

    }
    
    /** make an array from enum entries **/
    public static function make_array_from_enum( $table, $field, $keyed = FALSE ) {
        $db = MySQL::staticDB( );
        $sql = "SHOW COLUMNS FROM $table LIKE '$field'";
        $db->query( $sql );
        $row = mysql_fetch_assoc( $db->result );
        
        preg_match_all( "/'([^\']*)'/", $row['Type'], $matches ); 
        if(! $keyed ) {
            return( $matches[1] );
        }
        
        if(! $matches[1] ) {
            return( array( ) );
        }
        
        foreach( $matches[1] as $val ) {
            $arr[$val] = $val;
        }
        
        return( $arr );
    }
    
}
?>