Location: PHPKode > scripts > EasySQL classes > class.easysql.php
<?php



/**
*  EasySQL 1.0
*
*  @author: Carlos Reche
*  @email:  hide@address.com
*
*  Jan 4, 2005
*
*
*  Bugs fixed:
*  - Jan 29, 2005: "LIMIT" clause didn't work properly when using EasySQL::select() method. Now fixed.
*
*/
class EasySQL
{

    /*@#+
    *  @acess private
    */
    var $SQLDatabase; // (object)    Mixed type. Will have a different instance depending on database software
    var $SQLQuery;    // (object)    Creates SQL queries. Type: SQLQuery

    var $connection;  // (resource)  Connection with database
    var $result;      // (resource)  Resource identifier returned by SELECT query, or boolean if query was INSERT, UPDATE or DELETE
    var $total_rows;  // (int)       Number of rows selected or affected (inserted, updated or deleted) by the last query executed
    //@#+



    function EasySQL($SQLDatabase = "", $host = "", $user = "", $password = "", $db_name = "", $port = "")
    {

        // Configuration for default values (used when these parameters were NOT passed by constructor)

        $_default['SQLDatabase'] = "";          //  Default SQL software (E.g. "MySQL", "PostGreSQL", "SQLite")
        $_default['host']        = "localhost"; //  Host server of database
        $_default['user']        = "root";      //  User
        $_default['password']    = "";          //  Password
        $_default['db_name']     = "";          //  Database that will be selected
        $_default['port']        = "";          //  Server port. Leave empty for default.
        //--


        $SQLDatabase = ($SQLDatabase != "")  ?  (string)$SQLDatabase  :  $_default['SQLDatabase'];
        $host        = ($host != "")         ?  (string)$host         :  $_default['host'];
        $user        = ($user != "")         ?  (string)$user         :  $_default['user'];
        $password    = ($password != "")     ?  (string)$password     :  $_default['password'];
        $db_name     = ($db_name != "")      ?  (string)$db_name      :  $_default['db_name'];
        $port        = ($port != "")         ?  (int)$port            :  $_default['port'];



        // Defines this class' objects: SQLDatabase and SQLQuery

        $this->SQLQuery = new SQLQuery();

        if (preg_match("/^(0|mysql)$/i", trim($SQLDatabase)))
        {
            // MySQL

            if (!function_exists("mysql_connect"))
            {
                $this->error("MySQL library not found.");
                return false;
            }
            else if (!class_exists("EasyMySQL"))
            {
                $this->error("Could NOT load <strong>EasyMySQL</strong> class. Aborting <strong>" . __CLASS__ . "</strong> class execution.");
                return false;
            }

            $this->SQLDatabase = new EasyMySQL($host, $user, $password, $db_name, $port);
        }


        else if (preg_match("/^(1|pg|postgre(s|sql)?)$/i", trim($SQLDatabase)))
        {
            // PostGreSQL

            if (!function_exists("pg_connect"))
            {
                $this->error("PostGreSQL library not found.");
                return false;
            }
            else if (!class_exists("EasyPostGreSQL"))
            {
                $this->error("Could NOT load <strong>EasyPostGreSQL</strong> class. Aborting <strong>" . __CLASS__ . "</strong> class execution.");
                return false;
            }

            $this->SQLDatabase = new EasyPostGreSQL($host, $user, $password, $db_name, $port);
        }


        else if (preg_match("/^(2|sqlite)$/i", trim($SQLDatabase)))
        {
            // SQLite

            if (!function_exists("sqlite_open"))
            {
                $this->error("SQLite library not found.");
                return false;
            }
            else if (!class_exists("EasySQLite"))
            {
                $this->error("Could NOT load <strong>EasySQLite</strong> class. Aborting <strong>" . __CLASS__ . "</strong> class execution.");
                return false;
            }

            $this->SQLDatabase = new EasySQLite($host, $user, $password, $db_name, $port);
        }

        else
        {
            $this->error("No SQL databases were defined! Aborting <strong>" . __CLASS__ . "</strong> class execution.");
            return false;
        }



        // Sets default values

        $this->connection = false;
        $this->result     = false;
        $this->total_rows = 0;
    }



    /*@#+
    *  @acess public
    */
    function connect($db_name = "")
    {
        $db_name = ($db_name != "")  ?  $db_name  :  $this->SQLDatabase->db_name;

        $this->SQLDatabase->connect($this->connection, $db_name, false);

        return $this->connection;
    }



    function pconnect($db_name = "")
    {
        $db_name = ($db_name != "")  ?  $db_name  :  $this->SQLDatabase->db_name;

        $this->SQLDatabase->connect($this->connection, $db_name, true);

        return $this->connection;
    }



    function disconnect()
    {
        $this->connection = false;
        return $this->SQLDatabase->disconnect($this->connection);
    }



    function close()
    {
        return $this->disconnect();
    }



    function query($query)
    {
        $was_connected    = true;
        $this->result     = false;
        $this->total_rows = 0;


        if (!$this->connection)
        {
            $was_connected = false;

            if (!$this->connect())
            {
                $this->error();
                return false;
            }
        }


        $this->result = $this->SQLDatabase->query($query, $this->connection);


        if (!$this->result)
        {
            $this->error();
            return false;
        }

        if ($this->result === true)
        {
            $this->total_rows = $this->SQLDatabase->affectedRows($this->result, $this->connection);
        }

        if (!$was_connected)
        {
            $this->disconnect();
        }

        return $this->result;
    }



    function fetchArray($result = "")
    {
        if ($result == "")
        {
            $result =& $this->result;
        }

        return $this->SQLDatabase->fetchArray($result);
    }



    function fetchRow($result = "")
    {
        if ($result == "")
        {
            $result =& $this->result;
        }

        return $this->SQLDatabase->fetchRow($result);
    }



    function fetchAssoc($result = "")
    {
        if ($result == "")
        {
            $result =& $this->result;
        }

        return $this->SQLDatabase->fetchAssoc($result);
    }



    function error($message = "")
    {
        if ($message == "")
        {
            $message = ($this->connection)  ?  $this->SQLDatabase->error($this->connection)  :  "Could not connect to database.";
        }

        echo '<br /><span style="padding: 1px 7px 1px 7px; background-color: #ffd7d7; font-family: verdana; color: #000000; font-size: 13px;"><span style="color: #ff0000; font-weight: bold;">Error!</span> ' . $message . '</span><br />';
    }





    function useDatabase($db_name)
    {
        $this->SQLDatabase->db_name = (string)$db_name;
    }

    function useTable($table)
    {
        $this->SQLQuery->table = (string)$table;
    }



    function setFieldValue($field, $value, $is_sql_function = false)
    {
          $field = (string)$this->SQLDatabase->escape($field);
          $value = (string)$this->SQLDatabase->escape($value);

          $this->SQLQuery->values[$field]['value']           = $value;
          $this->SQLQuery->values[$field]['is_sql_function'] = (bool)$is_sql_function;
    }


    function setWhere($where)
    {
        $this->SQLQuery->where = (string)$where;
    }

    function setLimit($limit)
    {
        $this->SQLQuery->limit = (string)$limit;
    }

    function setSelection($selection)
    {
        $this->SQLQuery->selection = (string)$selection;
    }





    function select()
    {
        $no_limit_query = $this->SQLQuery->createSelect(false);
        $return_query   = $this->SQLQuery->createSelect();

        $result_total   = $this->query($no_limit_query);

        if (!$result_total)
        {
            return false;
        }

        $total_rows = $this->SQLDatabase->numRows($result_total);
        $result     = $this->query($return_query);

        $this->total_rows = $total_rows;

        return $result;
    }



    function insert()
    {
        return $this->query($this->SQLQuery->createInsert());
    }



    function update()
    {
        return $this->query($this->SQLQuery->createUpdate());
    }


    function delete()
    {
        return $this->query($this->SQLQuery->createDelete());
    }



    function truncate()
    {
        return $this->query($this->SQLQuery->createTruncate());
    }
    //@#+

}







class SQLQuery
{

    var $query;     // (string)  Last query created

    var $table;     // (string)  Table used
    var $values;    // (array)   Each element is a new array wich "keys" are the field names. Each new array has to elements: (string) "value" and (bool) "is_sql_function"
    var $where;     // (string)  "WHERE" clause
    var $limit;     // (string)  "LIMIT" clause
    var $selection; // (string)  Selection of select clause: "SELECT (selection) FROM table;"


    function SQLQuery()
    {
        $this->query = "";

        $this->table        = "";
        $this->values       = array();
        $this->where        = "";
        $this->limit        = "";
        $this->selection    = "*";
    }



    function createSelect($use_limit_clause = true)
    {
        if ($this->selection == ""  ||  $this->table == "")
        {
            if ($use_limit_clause)
            {
                if ($this->selection == "") {
                    $this->error('Could NOT create "SELECT" query. Parameter <strong>"selection"</strong> was empty.');
                }
                if ($this->table == "") {
                    $this->error('Could NOT create "SELECT" query. Parameter <strong>"table"</strong> was empty.');
                }
            }

            return false;
        }

        $this->query  = "SELECT " . $this->selection . " FROM " . $this->table . $this->returnWhere();
        $this->query .= ($use_limit_clause)  ?  $this->returnLimit()  :  "";

        return $this->query;
    }



    function createInsert()
    {
        if ($this->table == ""  ||  count($this->values) == 0)
        {
            if ($this->table == "") {
                $this->error('Could NOT create "INSERT" query. Parameter <strong>"table"</strong> was empty.');
            }
            if (count($this->values) == 0) {
                $this->error('Could NOT create "INSERT" query. Parameter <strong>"values"</strong> was empty.');
            }

            return false;
        }


        $values = $fields = array();

        foreach ($this->values as $fieldName => $fieldSettings)
        {
            $fields[] = $fieldName;

            if ($fieldSettings['value'] === NULL) {
                $values[] = "NULL";
            } else if ($fieldSettings['is_sql_function']) {
                $values[] = $fieldSettings['value'];
            } else {
                $values[] = "'" . $fieldSettings['value'] . "'";
            }
        }

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


        $this->query = "INSERT INTO " . $this->table . $values;
        return $this->query;
    }



    function createUpdate()
    {
        if ($this->table == ""  ||  count($this->values) == 0  ||  $this->where == "")
        {
            if ($this->table == "") {
                $this->error('Could NOT create "UPDATE" query. Parameter <strong>"table"</strong> was empty.');
            }
            if (count($this->values) == 0) {
                $this->error('Could NOT create "UPDATE" query. Parameter <strong>"values"</strong> was empty.');
            }
            if ($this->where == "") {
                $this->error('Safety procedure: "UPDATE" query was not created because <strong>"where"</strong> clause was empty.');
            }

            return false;
        }


        $values = $fields = array();

        foreach ($this->values as $fieldName => $fieldSettings)
        {
            if ($fieldSettings['value'] === NULL) {
                $values[] = $fieldName . " = NULL";
            } else if ($fieldSettings['is_sql_function']) {
                $values[] = $fieldName . " = " . $fieldSettings['value'];
            } else {
                $values[] = $fieldName . " = '" . $fieldSettings['value'] . "'";
            }
        }

        $values = " SET " .   implode(', ', $values);

        $this->query = "UPDATE " . $this->table . $values . $this->returnWhere() . $this->returnLimit();
        return $this->query;
    }



    function createDelete()
    {
        if ($this->table == ""  ||  $this->where == "")
        {
            if ($this->table == "") {
                $this->error('Could NOT create "DELETE" query. Parameter <strong>"table"</strong> was empty.');
            }
            if ($this->where == "") {
                $this->error('Safety procedure: "DELETE" query was not created because <strong>"where"</strong> clause was empty.');
            }

            return false;
        }

        $this->query = "DELETE FROM " . $this->table . $this->returnWhere() . $this->returnLimit();
        return $this->query;
    }




    function createTruncate()
    {
        if ($this->table == "")
        {
            $this->error('Could NOT create "TRUNCATE" query. Parameter <strong>"table"</strong> was empty.');
            return false;
        }

        $this->query = "TRUNCATE TABLE " . $this->table;
        return $this->query;
    }



    function returnWhere()
    {
        $where = ($this->where != "")  ?  (" WHERE (" . $this->where . ")")  :  "";
        return $where;
    }

    function returnLimit()
    {
        $limit = ($this->limit != "")  ?  (" LIMIT " . $this->limit)  :  "";
        return $limit;
    }

    function error($message = "")
    {
        echo '<br /><span style="padding: 1px 7px 1px 7px; background-color: #ffd7d7; font-family: verdana; color: #000000; font-size: 13px;"><span style="color: #ff0000; font-weight: bold;">Error!</span> ' . $message . '</span><br />';
    }

}







class EasyMySQL
{

    var $host;     // (string)  Host server of database
    var $user;     // (string)  User
    var $password; // (string)  Password
    var $db_name;  // (string)  Database that will be selected
    var $port;     // (int)     Server port



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



    function connect(&$connection, $db_name, $is_persistent = false)
    {
        $db_name = ($db_name != "")  ?  $db_name  :  $this->db_name;

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

        if (!$connection  ||  !@mysql_select_db($db_name, $connection))
        {
            return false;
        }

        return $connection;
    }



    function disconnect(&$connection)
    {
        if ($connection)
        {
            return @mysql_close($connection);
        }

        return true;
    }



    function query($query, $connection)
    {
        return @mysql_query($query, $connection);
    }



    function numRows($result)
    {
        return @mysql_num_rows($result);
    }



    function affectedRows($result, $connection)
    {
        return @mysql_affected_rows($connection);
    }



    function fetchArray($result)
    {
        return @mysql_fetch_array($result);
    }



    function fetchRow($result)
    {
        return @mysql_fetch_row($result);
    }



    function fetchAssoc($result)
    {
        return @mysql_fetch_assoc($result);
    }



    function escape($string)
    {
        return @mysql_escape_string($string);
    }



    function error($connection)
    {
        return @mysql_error($connection);
    }

}





class EasyPostGreSQL
{

    var $host;     // (string)  Host server of database
    var $user;     // (string)  User
    var $password; // (string)  Password
    var $db_name;  // (string)  Database that will be selected
    var $port;     // (int)     Server port



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



    function connect(&$connection, $db_name, $is_persistent = false)
    {
        $db_name = ($db_name != "")  ?  $db_name  :  $this->db_name;

        $connection_string = 'host=' . $this->host . ' port=' . $this->port . ' user=' . $this->user . ' password=' . $this->password . ' dbname=' . $db_name;


        if (!$is_persistent) {
            $connection = @pg_connect($connection_string, PGSQL_CONNECT_FORCE_NEW);
        } else {
            $connection = @pg_pconnect($connection_string, PGSQL_CONNECT_FORCE_NEW);
        }

        if (!$connection)
        {
            return false;
        }

        return $connection;
    }



    function disconnect(&$connection)
    {
        if ($connection)
        {
            return @pg_close($connection);
        }

        return true;
    }



    function query($query, $connection)
    {
        return @pg_query($connection, $query);
    }



    function affectedRows($result, $connection)
    {
        return @pg_affected_rows($result);
    }



    function numRows($result)
    {
        return @pg_num_rows($result);
    }



    function fetchArray($result)
    {
        return @pg_fetch_array($result);
    }



    function fetchRow($result)
    {
        return @pg_fetch_row($result);
    }



    function fetchAssoc($result)
    {
        return @pg_fetch_assoc($result);
    }



    function escape($string)
    {
        return @mysql_escape_string($string);
    }



    function error($connection)
    {
        return @pg_last_error($connection);
    }

}







class EasySQLite
{

    var $host;     // (string)  This parameter is not used on SQLite
    var $user;     // (string)  This parameter is not used on SQLite
    var $password; // (string)  This parameter is not used on SQLite
    var $db_name;  // (string)  Database that will be selected
    var $port;     // (int)     This parameter is not used on SQLite



    function EasySQLite($host = "", $user = "", $password = "", $db_name = "", $port = "")
    {
        $this->host     = NULL;
        $this->user     = NULL;
        $this->password = NULL;
        $this->port     = NULL;

        if ($db_name != "") {
            $this->db_name = (string)$db_name;
        } else if ($host != "") {
            $this->db_name = (string)$host;
        } else {
            $this->db_name = "";
        }
    }



    function connect(&$connection, $db_name, $is_persistent = false)
    {
        $db_name = ($db_name != "")  ?  $db_name  :  $this->db_name;

        if (!$is_persistent) {
            $connection = @sqlite_open($db_name, 0666, $error_message);
        } else {
            $connection = @sqlite_popen($db_name, 0666, $error_message);
        }

        if (!$connection)
        {
            echo $error_message;
            return false;
        }

        return $connection;
    }



    function disconnect(&$connection)
    {
        if ($connection)
        {
            return @sqlite_close($connection);
        }

        return true;
    }



    function query($query, $connection)
    {
        return @sqlite_query($connection, $query);
    }



    function affectedRows($result, $connection)
    {
        return @sqlite_changes($connection);
    }



    function numRows($result)
    {
        return @sqlite_num_rows($result);
    }



    function fetchArray($result)
    {
        return @sqlite_fetch_array($result, SQLITE_BOTH);
    }



    function fetchRow($result)
    {
        return @sqlite_fetch_array($result, SQLITE_NUM);
    }



    function fetchAssoc($result)
    {
        return @sqlite_fetch_array($result, SQLITE_ASSOC);
    }



    function escape($string)
    {
        return @sqlite_escape_string($string);
    }



    function error($connection)
    {
        return @sqlite_error_string(@sqlite_last_error($connection));
    }

}



?>
Return current item: EasySQL classes