Location: PHPKode > scripts > MySQL-class > mysql-class/mysql-class.php
<?php
/***************************************************************************
 * GS MySQL Class v 1.0
 * Class developed by Gabriel Solomon ( solomongaby at yahoo.com ) bassed on a class by   Carlos Falo Herv�s ( hide@address.com )
 * Last Update on 15 october 2007
 **************************************************************************/

class GS_Mysql {

	// Database server information
    public $host;                 // (string)  Host server of database
    public $user;                 // (string)  User
    public $password;             // (string)  Password
    public $db_name;              // (string)  Database that will be selected
    public $port;                 // (int)     Server port
    public $connection = false;   // (link identifier)   MySQL connection link identifier
    public $result;               // (link identifier)   MySQL result link identifier

    // Class operation setup


    // 0 LOG_NONE
    // 1 ECHO
    // 2 HIDDEN ECHO
    // 3 LOG FILE
    public $debugType  = 0 ;
    // 0 LogAll
    // 1 LogOnly Bad & Noresults Querrys
    // 2 LogOnly Bad Querrys
    public $debugLevel  = 1 ;
    public $error_level  = 0 ;
    public $error_desc   = "No errors" ;
    public $logfile      = "datalog" ;
    public $filehdl      = 0 ;
    public $messsages    = array() ;

    public $affected_rows = 0 ;
    public $num_rows      = 0 ;
    public $recordcount   = 0 ;
    public $lastid        = 0 ;
    public $sqlString;

    public $query_no     = 0 ;



	public function mysql($host = "", $user = "", $password = "", $db_name = "", $port = "")
	{
        $this->host     = ( !empty( $host ) )      ?  (string)$host      :  "localhost";
        $this->user     = ( !empty( $user ) )      ?  (string)$user      :  "root";
        $this->password = ( !empty( $password ) )  ?  (string)$password  :  "";
        $this->db_name  = ( !empty( $db_name ) )   ?  (string)$db_name   :  "";
        $this->port     = ( !empty( $port ) )      ?  (int)$port         :  3306;
	}


    public function connect($is_persistent = false)
    {
    	$this->logfile_init() ;

        if (!$is_persistent) {
            $this->connection = @mysql_connect($this->host.':'.$this->port, $this->user, $this->password);
        } else {
            $this->connection = @mysql_pconnect($this->host.':'.$this->port, $this->user, $this->password);
        }

        $this->error_report() ;

        if (!$this->connection) {
            // Conection failed
            $this->add_debug_message ( date("d/m/Y - H:i:s") . " - ERROR " . $this->error_level . ": " . $this->error_desc . "\r\n" ) ;
            $this->release_db() ;
        } else {
            $this->select_db();
        }
    }

	public function  select_db($db_name=false)
    {
        if ($db_name !== false) $this->db_name=$db_name;

        // Select a database...
        if (@mysql_select_db($this->db_name,$this->connection)) {
            // Selecting Database OK
            $this->add_debug_message ( date("d/m/Y - H:i:s") . " - OPERATION O.K.: Connected to database " . $this->db_name .  "\r\n" );
        } else {
            // Failed to select the database... abort connection process
            $this->error_report() ;
            $this->add_debug_message ( date("d/m/Y - H:i:s") . " - ERROR " . $this->error_level . ": " . $this->error_desc . "\r\n" ) ;
            $this->release_db() ;
        }
	}

    // Releasing database connection
    public function release_db()
    {
        // Checking if a conection is open?
        if ($this->connection) {
            // Trying to close the connection ...
            if (mysql_close($this->connection)) {
                $this->add_debug_message ( date("d/m/Y - H:i:s") . " - OPERATION O.K.: Database " . $this->db_name . " released" . "\r\n" );
            } else {
                // Failed to liberate the database...
                $this->error_report() ;
                $this->add_debug_message ( date("d/m/Y - H:i:s") . " - ERROR " . $this->error_level . ": " . $this->error_desc . "\r\n" );
            }
        } else {
            // No database open
            $this->add_debug_message ( date("d/m/Y - H:i:s") . " - OPERATION CANCELLED: No database open" . "\r\n" );
        }
        // LOG the operation and close logging operations
        $this->debug() ;
        $this->logfile_close() ;
    }

    // Error reporting auxiliary method
    public function error_report()
    {
        $this->error_level = mysql_errno() ;
        $this->error_desc = mysql_error() ;
    }

    // Log operations initialization
    public function logfile_init()
    {
        if ($this->debugType==3) {
            $this->add_debug_message ( date("d/m/Y - H:i:s") . " ===== SESSION STARTED BY " . $GLOBALS["PHP_SELF"] . " =====" .  "\r\n" );
            $this->logfile = $this->logfile . "-" . date("m") . "-" . date("Y") ;
            $this->filehdl = fopen($this->logfile,'a') ;

            if (!$this->filehdl) {
                echo "<!-- UNABLE TO OPEN SPECIFIED LOG FILE " . $this->logfile . " -->" ;
                $this->debugType-- ;
                $this->logfile_init() ;
            }
            break ;
        }
        $this->debug() ;
    }

    // Closing log operations
    public function logfile_close()
    {
        if ($this->filehdl) {
            // If we opened a file to log operations need to close it
            fclose($this->filehdl) ;
        }
    }

    public function add_debug_message($message)
    {
        $this->messsages[]=$message;
    }

    // Debugging operations
    public function debug()
    {
        switch ($this->debugType) {
            case 0: // NO LOG OPERATIONS
                break ;
            case 1: // SCREEN OUTPUT
                foreach ($this->messsages as $m) {
                    echo '<BR>DEBUG: ' . $m . '<BR>' ;
                }
                break ;
            case 2: // SILENT OUTPUT (<!-- -->)
                foreach ($this->messsages as $m) {
                    echo "\n<!-- DEBUG: " . $m . "-->\n" ;
                }
                break ;
            case 3: // FILE OUTPUT
                foreach ($this->messsages as $m) {
                    fwrite($this->filehdl,$this->msg) ;
                }
                break ;
        }
    }


    // Destructor
    public function destroy()
    {
        $this->release_db() ;
    }


    // performes an sqlQuery
    public function query($sqlString)
    {
        $this->sqlString=$sqlString;
        $this->query_no++;

        if ($this->connection !== false) {
            $this->result = mysql_query($sqlString,$this->connection) ;
            $this->error_report() ;
            // Affectected rows...
            if ($this->result) {
                // Execution was o.k.
                $this->affected_rows = mysql_affected_rows( $this->connection );
                if (is_resource($this->result)) {
                    $this->num_rows = mysql_num_rows( $this->result );
                } else  {
                    $this->num_rows = 0;
                }

                $this->lastid = mysql_insert_id( $this->connection );
                if ( ($this->debugLevel==1 && ($this->affected_rows+$this->num_rows)<1 ) OR $this->debugLevel == 0 ) {
                    $this->add_debug_message( date("d/m/Y - H:i:s") . " - OPERATION O.K.: Executed [" . $this->sqlString ."] [affected " . $this->affected_rows . " rows] [rows in result " . $this->num_rows . " ]" . "\r\n" );
                }
                return true;
            } else {
                // Execution Failed
                $this->affected_rows = 0 ;
                $this->num_rows = 0 ;
                $this->add_debug_message( date("d/m/Y - H:i:s") . " - OPERATION FAILED: Executed [" . $this->sqlString . "] got " . $this->error_level . " " . $this->error_desc . "\r\n" );
                return false;
            }
        } else {
            // No database ready to query
            $this->affected_rows = 0 ;
            $this->num_rows = 0 ;
            $this->add_debug_message( date("d/m/Y - H:i:s") . " - OPERATION FAILED: No database open OR no SQL command provided" . "\r\n"  );
            return false;
        }
    }

    public function mysql_insert_id()
    {
        return mysql_insert_id ( $this->connection );
    }

    public function fetch_assoc()
    {
        return mysql_fetch_assoc( $this->result );
    }

    public function clean_data($data)
    {
        return mysql_real_escape_string($data,$this->connection);
    }

    public function fetch_data_array ($key = false)
    {
        $data=array();
        while( $row = $this->fetch_assoc() )
        {
            if ($key && isset($row[$key]))
            {
                $data[$row[$key]]=$row;
            } else {
                $data[]=$row;
            }
        }
        return $data;
    }

    // grabs a list of rows from a tabel ... returnes an array of data
    public function list_table( $table_name, $where = false, $parameters = array () )
    {
        $range       = ( isset($parameters['range'])       && !empty($parameters['range']) )       ? $parameters['range']       : " * " ;
        $key         = ( isset($parameters['key'])         && !empty($parameters['key']) )         ? $parameters['key']         : false ;
        $sortColumn  = ( isset($parameters['sortColumn'])  && !empty($parameters['sortColumn']) )  ? $parameters['sortColumn']  : false ;
        $sortType    = ( isset($parameters['sortType'])    && !empty($parameters['sortType']) )    ? $parameters['sortType']    : "ASC" ;
        $limitOffset = ( isset($parameters['limitOffset']) && !empty($parameters['limitOffset']) ) ? $parameters['limitOffset'] : false ;
        $rowCount    = ( isset($parameters['rowCount'])    && !empty($parameters['rowCount']) )    ? $parameters['rowCount']    : false ;

        $queryString= "SELECT $range FROM $table_name ";

        if ( $where !== false ) $queryString .= " WHERE ".$where;
        if ( $sortColumn !== false ) $queryString .= " ORDER BY `$sortColumn` $sortType ";
        if ( $rowCount !== false ) {
            $queryString .= " LIMIT ";

            if ( $limitOffset !== false ) {
                $queryString .= " $limitOffset, ";
            }

            $queryString .= " $rowCount ";
        }

        $this->query($queryString);
        if( $this->num_rows < 1 ) {
            return false;
        } else {
            return $this->fetch_data_array($key);
        }
    }


    public function listDistinct( $table_name, $where = false,$group = false, $parameters = array () )
    {
        $range       = ' COUNT( * ) AS `Rows` , ';
        $range      .= ( isset($parameters['range'])       && !empty($parameters['range']) )       ? $parameters['range']       : " * " ;
        $sortColumn  = ( isset($parameters['sortColumn'])  && !empty($parameters['sortColumn']) )  ? $parameters['sortColumn']  : false ;
        $sortType    = ( isset($parameters['sortType'])    && !empty($parameters['sortType']) )    ? $parameters['sortType']    : "ASC" ;
        $limitOffset = ( isset($parameters['limitOffset']) && !empty($parameters['limitOffset']) ) ? $parameters['limitOffset'] : false ;
        $rowCount    = ( isset($parameters['rowCount'])    && !empty($parameters['rowCount']) )    ? $parameters['rowCount']    : false ;

        $queryString= "SELECT $range FROM $table_name ";
        if ( $where !== false ) $queryString .= " WHERE ".$where;
        if ( $group !== false ) $queryString .= " GROUP BY `$group` ";
        if ( $sortColumn !== false ) $queryString .= " ORDER BY `$sortColumn` $sortType ";
        if ( $rowCount !== false ) {
            $queryString .= " LIMIT ";

            if ( $limitOffset !== false ) {
                $queryString .= " $limitOffset, ";
            }

            $queryString .= " $rowCount ";
        }

        $this->query($queryString);
        if( $this->num_rows < 1 ) {
            return false;
        } else {
            return $this->fetch_data_array();
        }
    }

    // fetch a row from a table
    public function fetch_row( $table_name, $where = false , $parameters = array () )
    {
        $range       = ( isset($parameters['range'])       && !empty($parameters['range']) )       ? $parameters['range']       : " * " ;
        $order       = ( isset($parameters['order'])       && !empty($parameters['order']) )       ? $parameters['order']       : false ;


        $queryString= "SELECT $range FROM $table_name ";
        if ( $where != false ) $queryString .= " WHERE $where";

        if ( $order != false ) {
            $orderBy = array();
            foreach ($order as $field => $type) {
                $orderBy[] = $field . " " . strtoupper($type);
            }
            $queryString .= " ORDER BY " . implode(",", $orderBy);
        }

        $queryString .= " LIMIT 1";

        $this->query($queryString);

        if( $this->num_rows < 1 ) { return false; }
        else { return $this->fetch_assoc(); }
    }

    public function count_records( $table_name, $where =false , $parameters = array() )
    {
        $queryString= "SELECT COUNT(*) as rNumber FROM $table_name ";
        if ( $where != false ) $queryString .= " WHERE $where ";

        if ($this->query($queryString) == true ) {
            $row=$this->fetch_assoc();
            return $row["rNumber"];
        } else return false;
    }

    public function increment_field( $table_name, $field, $where, $parameters = array() )
    {
        $queryString= "UPDATE $table_name SET `$field`=`$field`+1  WHERE $where ";
        $this->query($queryString);
    }

    public function record_update( $table_name, $data, $where, $parameters = array() )
    {
        $queryString="UPDATE ".$table_name." SET ";
        $fields=array();

        foreach ($data as $key=>$value)	{
            $fields[] = " `$key`='".$this->clean_data( $value )."' ";
        }

        $queryString .= implode(',',$fields)." WHERE ".$where;

        return $this->query($queryString);
    }

    public function record_insert( $table_name, $data, $parameters = array() )
    {
        $queryString="INSERT INTO ".$table_name." (";
        $columns=array();
        $values=array();

        foreach ($data as $key=>$value)
        {
            $columns []= '`'.$key.'`';
            $values  []= "'".$this->clean_data( $value )."'";
        }

        $queryString .= implode(',',$columns) .") VALUES (". implode(',',$values) .") ";

        return $this->query($queryString);
    }

    public function record_delete( $table_name, $where, $parameters = array() )
    {
        $queryString = "DELETE FROM ". $table_name ." WHERE ". $where;
        return $this->query($queryString);
    }

    public function table_info($table_name)
    {
        $this->query(" SELECT * FROM $table_name LIMIT 1");
        $fields = mysql_num_fields($this->result);

        for ($i=0; $i <= $fields; $i++) {
            $fields[$i]['type'] = mysql_field_type($result, $i);
            $fields[$i]['name'] = mysql_field_name($result, $i);
            $fields[$i]['len']  = mysql_field_len($result, $i);
        }

        return $fields;
    }

    public function table_max_value( $table, $field)
    {
        $this->query(" SELECT max($field) as max_value FROM $table ");
        $data=$this->fetch_assoc();

        return $data["max_value"];
    }

    public function listTables() {
        $this->query("SHOW TABLES;");
        $data = $this->fetch_data_array();

        return $data;
    }

} // de la clasa
?>
Return current item: MySQL-class