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 

[kayman]

klasa powstała w związku z dużymi formularzami i pewnie dlatego singleton w connect bo zapytania w zasadzie dotyczą jednej bazy, jest ich najwyżej kilka ale kolumn duzo

co do private to cenna uwaga ale na dzień dzisiejszy nie bardzo widzę jej rozwój przez dziedziczenie

założenie było takie żeby ona wykonywała prawidłowe i przefiltrowane zapytanie i na tyle czytelne że w razie modyfikacji formularza/bazy/widoku (lub po prostu własnego błędu) można było szybko zmodyfikować duże zapytanie sql

Ten post edytował kayman 6.11.2013, 21:22:30