klasa sql Opcje

[kayman]

w zasadzie 2 klasy bo connect jest oddzielnie

nie staram się wynaleźć koła na nowo tylko uprościć sobie robotę (IMG:style_emoticons/default/smile.gif)

ale pewnie jest tu parę błędów więc oceniajcie

btw. PDO do mnie nie przemawia jakoś a może przyzwyczaiłem się do starego?

pozdrawiam

[PHP] pobierz, plaintext 
<?php
 
class DBConnect {
 
    private static $_instance;
    public static $db;
    private static $host = 'localhost';
    private static $user = 'root';
    private static $pass = '';
    private static $database = 'ukcrm';
 
    private function __construct() {
        $this->connect();
    }
 
    public function setConnect() {
        if (!isset(self::$_instance)) {
            self::$_instance = new DBConnect();
        }
    }
 
    private static function connect() {
        self::$db = mysqli_connect(self::$host, self::$user, self::$pass, self::$database) or die('Connection fail');
    }
 
    public function changeConnect($host, $user, $pass, $db) {
        if (isset(self::$_instance)) {
            mysqli_close(self::$db);
            self::$host = $host;
            self::$user = $user;
            self::$pass = $pass;
            self::$database = $db;
            self::connect();
        } else {
            self::$_instance = new DBConnect();
            self::ChangeConnect($host, $user, $pass, $db);
        }
    }
 
}
 
class sql {
 
    private $table;
    private $params;
    private $where;
    private $columns;
    private $result;
    private $sql;
    private $order;
    private $orderMode = 'ASC';
    private $limit;
 
    function __construct() {
        DBConnect::setConnect();
        $this->resetSql();
    }
 
    public function resetSql() {
        $this->table = '';
        $this->params = array();
        $this->where = array();
        $this->result = array();
        $this->columns = array();
        $this->order = array();
        $this->sql = '';
        $this->limit = '';
    }
 
    public function setTable($table) {
        $this->table = $table;
    }
 
    public function setSqlString($string) {
        $this->sql = $string;
    }
 
    public function crearteSQL($mode) {
        if ($mode == 'insert') {
            $this->sql = 'INSERT INTO ' . $this->table . ' SET ' . implode(', ', $this->params);
        }
        if ($mode == 'update') {
            $this->sql = 'UPDATE ' . $this->table . ' SET ' . implode(', ', $this->params) . ' WHERE ' . implode(' ', $this->where);
        }
        if ($mode == 'delete') {
            $this->sql = 'DELETE FROM ' . $this->table . ' WHERE ' . implode(' ', $this->where);
        }
        if ($mode == 'select') {
            $this->sql = 'SELECT ' . implode(', ', $this->columns) . ' FROM ' . $this->table;
            if (!empty($this->where))
                $this->sql .= ' WHERE ' . implode(' ', $this->where);
            if (!empty($this->order)) {
                $this->sql .= ' ORDER BY ' . implode(', ', $this->order) . ' ' . $this->orderMode;
            }
            if ($this->limit) {
                $this->sql .= ' LIMIT ' . $this->limit;
            }
        }
    }
 
    public function setLimit($value) {
        $this->limit = $value;
    }
 
    public function setOrderMode($value) {
        $this->orderMode = $value;
    }
 
    public function addOrder($column) {
        $this->order[] = $column;
    }
 
    public function addParam($name, $value, $filter) {
        if ($value) {
            switch ($filter) {
                case 'int':
                    $value = $this->filterInt($value);
                    break;
                case 'double':
                    $value = $this->filterDouble($value);
                    break;
                case 'text':
                    $value = $this->filterText($value);
                    break;
                case 'like':
                    $value = $this->filterLike($value);
                    break;
            }
        } else {
            $value = "NULL";
            if ($filter == 'double')
                $value = '0';
        }
        $this->params[] = $name . '=' . $value;
    }
 
    public function addWhere($column, $value, $filter, $sign = '=') {
        if ($value) {
            switch ($filter) {
                case 'int':
                    $value = $this->filterInt($value);
                    break;
                case 'double':
                    $value = $this->filterDouble($value);
                    break;
                case 'text':
                    $value = $this->filterText($value);
                    break;
                case 'like':
                    $value = $this->filterLike($value);
                    $sign = ' LIKE ';
                    break;
            }
        } else {
            $value = "NULL";
        }
        $this->where[] = $column . $sign . $value;
    }
 
    public function addTextWhere($value) {
        $this->where[] = $value;
    }
 
    public function addColumn($value) {
        $this->columns[] = $value;
    }
 
    public function addColumns($val = array()) {
        if (!empty($val)) {
            foreach ($val as $value) {
                $this->addColumn($value);
            }
        }
    }
 
    public function sqlQuery() {
        $this->result = mysqli_query(DBConnect::$db, $this->sql) or die(mysqli_error());
    }
 
    public function getResult() {
        return $this->result;
    }
 
    public function getRow() {
        return mysqli_fetch_assoc($this->result);
    }
 
    public function numRows() {
        return mysqli_num_rows($this->result);
    }
 
    public function testSQL() {
        echo $this->sql . '<br />';
        exit();
    }
 
    private function filter($value, $type) {
        $value = mysqli_real_escape_string(DBConnect::$db, $value);
        switch ($type) {
            case "text":
                $value = ($value != "") ? "'" . $value . "'" : "NULL";
                break;
            case "int":
                $value = ($value != "") ? intval($value) : "NULL";
                break;
            case "double":
                $value = ($value != "") ? "'" . doubleval($value) . "'" : "NULL";
                break;
        }
        return $value;
    }
 
    public function filterInt($value) {
        return $this->filter($value, 'int');
    }
 
    public function filterText($value) {
        return $this->filter($value, 'text');
    }
 
    public function filterDouble($value) {
        $value = str_replace(',', '.', $value);
        return $this->filter($value, 'double');
    }
 
    public function filterLike($value) {
        return $this->filter('%' . $value . '%', 'text');
    }
 
}
 
//przykładowe wywołanie
 
$sql = new sql();
$sql->setTable('kkk k left join iii i on k.kkk_id = i.id');
$cols = array('k.id', 'k.number', 'k.name', 'k.preson');
$sql->addColumns($cols);
$cols = array('i.id as invid', 'i.number as inumber', 'i.name as iname', 'i.preson as iperson');
$sql->addColumns($cols);
$sql->addWhere('k.id', $_GET['id'], 'int');
$sql->crearteSQL('select');
$sql->sqlQuery();
if ($sql->numRows() == 1) {
    $row = $sql->getRow();
}
 
 
//inne wywolanie
 
if (isset($_POST['action'])) {
    $sql = new sql();
    $sql->setTable('anytable');
 
    $sql->addParam('flue_sizing', $_POST['flue_sizing'], 'text');
    $sql->addParam('flue_route', $_POST['flue_route'], 'text');
    $sql->addParam('based_on_client_info', $_POST['based_on_client_info'], 'text');
    $sql->addParam('instalation_time1', $_POST['instalation_time1'], 'text');
    $sql->addParam('instalation_time1_combo', $_POST['instalation_time1_combo'], 'text');
    $sql->addParam('instalation_time2', $_POST['instalation_time2'], 'text');
    $sql->addParam('instalation_time2_combo', $_POST['instalation_time2_combo'], 'text');
    $sql->addParam('lead_time', $_POST['lead_time'], 'text');
    $sql->addParam('lead_time_combo', $_POST['lead_time_combo'], 'text');
 
    $sql->addParam('zone', $_POST['zone'], 'text');
    $sql->addParam('terrain_category', $_POST['terrain_category'], 'text');
    $sql->addParam('basic_wind_speed', $_POST['basic_wind_speed'], 'text');
    $sql->addParam('reliability_level', $_POST['reliability_level'], 'text');
 
    $sql->addParam('lorry_length', $_POST['lorry_length'], 'text');
    $sql->addParam('crane_radius_of_loading', $_POST['crane_radius_of_loading'], 'text');
    $sql->addParam('building_height', $_POST['building_height'], 'text');
    $sql->addParam('crane_capacity', $_POST['crane_capacity'], 'text');
    $sql->addParam('crane_radius_for_lifting', $_POST['crane_radius_for_lifting'], 'text');
 
    $sql->addParam('nr_of_appliances', $_POST['nr_of_appliances'], 'text');
 
    if ($_POST['action'] == 'edit') {
                $sql->addWhere('id', $_POST['id'], 'int');
                $sql->addParam('data_mod', date('Y-m-d'), 'text');                                
                $sql->crearteSQL('update');
                //$sql->testSQL();
                $sql->sqlQuery();
            }
}
?>
[PHP] pobierz, plaintext