PHP 之sqlite3封装与示例

一、sqlite3封装

<?php

class SQLiteDB extends SQLite3 {
    function __construct(){
        try {
            //打开数据库文件
            $this->open('./ccfcf4572e60f80522c1cf0f8e4b95c3.db');
        }catch (Exception $e){
            die($e->getMessage());
        }
    }
}

class DBUtils
{

    private static $db;

    private static function instance()
    {
        if (!self::$db) {
            self::$db = new SQLiteDB();
        }
    }

    /**
     * 创建表
     * @param string $sql
     */
    public static function create($sql)
    {
        self::instance();
        $result = @self::$db->query($sql);
        if ($result) {
            return true;
        }
        return false;
    }

    /**
     * 执行增删改操作
     * @param string $sql
     */
    public static function execute($sql)
    {
        self::instance();
        return @self::$db->exec($sql);
        $result = @self::$db->exec($sql);
        if ($result) {
            return true;
        }
        return false;
    }

    /**
     * 获取记录条数
     * @param string $sql
     * @return int
     */
    public static function count($sql)
    {
        self::instance();
        $result = @self::$db->querySingle($sql);
        return $result ? $result : 0;
    }

    /**
     * 查询单个字段
     * @param string $sql
     * @return void|string
     */
    public static function querySingle($sql)
    {
        self::instance();
        $result = @self::$db->querySingle($sql);
        return $result ? $result : '';
    }

    /**
     * 查询单条记录
     * @param string $sql
     * @return array
     */
    public static function queryRow($sql)
    {
        self::instance();
        $result = @self::$db->querySingle($sql, true);
        return $result;
    }

    /**
     * 获取插入id
     * @param $table
     * @return mixed
     */
    public static function getLastInsertId($table)
    {
        return self::$db->querySingle('select last_insert_rowid() from '.$table.' LIMIT 1');
    }

    /**
     * 查询多条记录
     * @param string $sql
     * @return array
     */
    public static function queryList($sql)
    {
        self::instance();
        $result = array();
        $ret = @self::$db->query($sql);
        if (!$ret) {
            return $result;
        }
        while ($row = $ret->fetchArray(SQLITE3_ASSOC)) {
            array_push($result, $row);
        }
        return $result;
    }
}

二、简单示例

//获取列表
DBUtils::queryList("select * from ay_content where scode=$old_scode");

//获取数量
DBUtils::count("select * from ay_content where acode='en' and title='{$v['title']}'");

//获取插入的id
DBUtils::querySingle('select last_insert_rowid() from ay_content LIMIT 1');

//获取单条数据
DBUtils::queryRow("select * from ay_content_ext where contentid={$id} LIMIT 1");

//执行增、删、改
DBUtils::execute(sql语句);