php SQLite数据库类 用法

sqlite.class.php

<?php

class SQLite {
    public $file;
    public $db;
    public $query;
    public $prepare;
    //
    const IS_INT = 1;
    const IS_STR = 2;
    const ASSOC = 1;
    const NUM = 2;
    const BOTH = 3;

    function __construct($base, $mode = 0666, $auto = true) {
        $this->db_file = $base;
        //
        return $auto?$this->open($mode):true;
    }
    
    function open($mode) {
        if($this->db) return $this->db;
        //
        if(!$this->db_file) return FALSE;
        //
        if(!is_file($this->db_file)) return FALSE;
        //
        if($this->db = sqlite_open ($this->db_file, $mode, $error)) {
            //
            return $this->db;
        }
        else{
            $this->error($error);
            return FALSE;
        }
    }
    
    function close() {
        $this->error = '';
        sqlite_close($this->db);
        return true;
    }    
    
    function query($query) {
        $query = preg_replace("/escape_string\((.*?)\)/", $this->escape_string("$1"), $query);
        $this->query = sqlite_query($this->db, $query);
        return $this;
    }
    
    function fetch($type=SQLITE_BOTH) {
        if($type == 1) {
            $type = SQLITE_ASSOC;
        }
        elseif($type == 2) {
            $type = SQLITE_NUM;
        }
        elseif($type == 3 || $type != SQLITE_BOTH) {
            $type = SQLITE_BOTH;
        }
        //
        return sqlite_fetch_array($this->query, $type);
    }    
    
    function fetchAll($type=SQLITE_BOTH) {
        if($type == 1) {
            $type = SQLITE_ASSOC;
        }
        elseif($type == 2) {
            $type = SQLITE_NUM;
        }
        elseif($type == 3 || $type != SQLITE_BOTH) {
            $type = SQLITE_BOTH;
        }
        //
        return sqlite_fetch_all($this->query, $type);
    }    
    
    
    function prepare($query) {
        $this->prepare = $query;
        return $this;
    }
    
    function bindParam($bind, $value, $types = '') {
        //
        if($types == 1) {
            if(preg_match("/^\d+$/", $value)) $to_prepare = $value;
        }
        elseif($types == 2) {
            if(is_string($value)) $to_prepare = '"'.$this->escape_string($value).'"';
        }
        elseif(!empty($types)) {
            if(preg_match($types, $value)) '"'.$this->escape_string($value).'"';
        }
        //
        if(empty($to_prepare)) $to_prepare = '""';
        
        //
        $this->prepare = str_replace($bind, $to_prepare, $this->prepare);
        //
        return $this;
    }
    
    function execute() {
        //
        $this->query = sqlite_query($this->db, $this->prepare);
        return $this;
    }
    
    function last_insert_id() {
        return sqlite_last_insert_rowid ($this->db);
    }    
    
    function rows() {
        return sqlite_num_rows($this->query);
    }
    
    function getColumns($table) {
        return $this->query("PRAGMA table_info($table)")->fetchAll(SQLite::ASSOC);
    }
    
    function getTables() {
        return $this->query("SELECT name FROM sqlite_master WHERE type='table' ORDER BY name")->fetchAll(SQLite::ASSOC);
    }
    
    function escape_string($string, $quotestyle='both') {

        if( function_exists('sqlite_escape_string') ){
            $string = sqlite_escape_string($string);
            $string = str_replace("''","'",$string); #- no quote escaped so will work like with no sqlite_escape_string available
        }
        else{
            $escapes = array("\x00", "\x0a", "\x0d", "\x1a", "\x09","\\");
            $replace = array('\0',   '\n',    '\r',   '\Z' , '\t',  "\\\\");
        }
        switch(strtolower($quotestyle)){
            case 'double':
            case 'd':
            case '"':
                $escapes[] = '"';
                $replace[] = '\"';
                break;
            case 'single':
            case 's':
            case "'":
                $escapes[] = "'";
                $replace[] = "''";
                break;
            case 'both':
            case 'b':
            case '"\'':
            case '\'"':
                $escapes[] = '"';
                $replace[] = '\"';
                $escapes[] = "'";
                $replace[] = "''";
                break;
        }
        return str_replace($escapes,$replace,$string);
    } 
    
    function error($error) {    
        if(!$this->db) {
            return '[ERROR] No Db Handler'; 
        }
        if(empty($error)) {
            return sqlite_last_error ($this->db);
        }
        return $error;
    }
}
?>

用法；

$mydb = new SQLite($file_path [, $auto=true]);

$file_path — the path to the DB Returns false if:

数据库已经打开，未设置数据库的路径，让数据库不是一个文件，打开数据库失败$ auto选项，设置，创建类（true）或打开函数SQLite :: open后立即访问数据库 （[$ mode]）;

SQLite::open([$mode]);

$mode — the optional parameter sets the mode of access rights on the file.

SQLite::close();

Closes the database.

SQLite::query($query);

Performs a query against the database $query — request (for example: SELECT * FROM table)

SQLite::fetch($type);

Fetches the next record from the query result and returns an array $type — type of indexing of the returned object. is of several types:

SQLite::ASSOC — associative array SQLite::NUM — a numeric array SQLite::BOTH — numeric and associative array

SQLite::fetchAll($type);

Selects all records from the query result and returns a multidimensional array $type — type of indexing of the returned object. is of several types:

SQLite::ASSOC — an associative array SQLite::NUM — an array of numbers SQLite::BOTH — numeric and associative array

SQLite::last_insert_id();

Returns the ID of the last inserted record.

SQLite::rows();

Returns the number of records in the query result

SQLite::getColumns($table);

Returns the mass of table columns $table

SQLite::getTables();

Returns all tables in a given database as an array.

escape_string()
a Function to escape characters that can be used as:

SQLite::escape_string($query);

and in the query:

$query = "SELECT * FROM table WHERE text='escape_string($text)'";
SQLite::prepare($query);

the Binding of the parameter with the specified variable. Works in conjunction with the function:

SQLite::bindParam($bind, $string[, $type]);

$bind — $string — the text that will replace the $type — type Types are of various kinds:

SQLite::IS_INT — numeric SQLite::IS_STR — the string regular expression — you can also substitute regular expression

SQLite::execute();

executes the query, if the query is specified using bundles of options.

examples for each function.

Connect to database, create tables, add records, most recent ID, delete the last line and closing the connection.

$mydb = new SQLite(ENGINE.'/files/history.db');
$mydb->query("CREATE TABLE history(id INTEGER AUTOINCREMENT, name VARCHAR(128) NOT NULL, PRIMARY KEY(id))");
//
$mydb->query("INSERT INTO history VALUES(NULL,'name')");
//
$last_id = $mydb->last_insert_id();
echo $last_id;
//
$mydb->query("DELETE FROM history WHERE id='{$last_id}'");
//
$mydb->close();

the database Connection, create table, add an entry with the parameter, the output of all records, close the connection.

$mydb = new SQLite(ENGINE.'/files/history.db');
$mydb->query("CREATE TABLE history(id INTEGER AUTOINCREMENT, name VARCHAR(128) NOT NULL, PRIMARY KEY(id))");
//
$query = $mydb->prepare("INSERT INTO history VALUES(NULL, :name)");
$query->bindParam(':name', 'John', SQLite::IS_STR);
$query->execute();
 
$result = $mydb->query("SELECT * FROM history")->fetchAll(SQLite::ASSOC);
 
var_dump($result);
//
$mydb->close();
the database Connection, create table, retrieving all the tables, retrieving all columns in the table closing the connection.

$mydb = new SQLite(ENGINE.'/files/history.db');
$mydb->query("DROP TABLE history");
$mydb->query("CREATE TABLE history(id INTEGER AUTOINCREMENT, name VARCHAR(128) NOT NULL, PRIMARY KEY(id))");
//
 
$tables = $mydb->getTables();
$columns = $mydb->getColumns('history');
 
var_dump($tables);
var_dump($columns);
//
$mydb->close();
the database Connection, create table, add an entry + escaping quotes special function escape_string(), closing the connection

$mydb = new SQLite(ENGINE.'/files/history.db');
$mydb->query("DROP TABLE history");
$mydb->query("CREATE TABLE history(id INTEGER AUTOINCREMENT, name VARCHAR(128) NOT NULL, adress VARCHAR(128) NOT NULL, PRIMARY KEY(id))");
//
$name = 'Jogn1"';
$adress = "Street 10'1";
//
$mydb->query("INSERT INTO history VALUES(NULL, 'escape_string($name)', '".SQLite::escape_string($adress)."')");
 
var_dump($mydb->query("SELECT * FROM history")->fetchAll(ASSOC));
//
$mydb->close();

赏 支付宝打赏 微信打赏

如果文章或资源对您有帮助，欢迎打赏作者。一路走来，感谢有您！