markdown-wiki/application/model/MySqlite.php

<?php
namespace app\model;

use \Exception;

class MySqlite
{
    private $mydb;
    protected $tablename;

    public function __construct()
    {
        $mydb = new \SQLite3(DB . DIRECTORY_SEPARATOR . 'mysqlitedb.sqlite');
        if (!$mydb) {
            throw new \Exception("$mydb->lastErrorMsg()", 1);
        } else {
            // echo "Opened database successfully\n";
            $this->mydb = $mydb;
        }
    }

    /**
     * 执行sql
     * @param string $sql
     * @return mixd $res
     */
    public function exec($sql)
    {
        @$res = $this->mydb->exec($sql);
        return $res;
    }

    public function query($sql)
    {
        $result = $this->mydb->query($sql);
        return $result;
    }
    
    public function lastInsertRowID ()
    {
        $result = $this->mydb->lastInsertRowID();
        return $result;
    }

    public function lastErrorMsg()
    {
        return $this->mydb->lastErrorMsg();
    }

    /**
     * 查询数组列表
     */
    public function select($sql)
    {
        $result = $this->mydb->query($sql);
        $data = array();
        // var_dump($result);exit;
        while ($arr = $result->fetchArray(SQLITE3_ASSOC)) {
            $data[] = $arr;
        }
        return $data;
    }

    /**
     * 查询一条
     */
    public function getOneById($id, $tablename='')
    {
        $tablename = $tablename ? $tablename : $this->tablename;
        $sql = "SELECT * FROM `$tablename` WHERE `id`=$id;";
        $result = $this->mydb->query($sql);
        // var_dump($result);
        $data = $result->fetchArray(SQLITE3_ASSOC);
        // var_dump($data);
        return $data;
    }

    public function getOne($sql = "")
    {
        $result = $this->mydb->query($sql);
        // var_dump($result);
        $data = $result->fetchArray(SQLITE3_ASSOC);
        // var_dump($data);
        return $data;
    }

    /**
     * 单列合计
     */
    public function sumColumn($column, $tablename)
    {
        $tablename = $tablename ? $tablename : $this->tablename;
        $sql = "SELECT sum(`$column`) as sumData FROM `$tablename`;";
        $result = $this->mydb->query($sql);
        // $data = $result->fetchArray();
        // var_dump($data['sumData']);exit;
        if ($data = $result->fetchArray(SQLITE3_ASSOC)) {
            return $data['sumData'];
        }
        return 0;
    }

    /**
     * 列表结果集
     */
    public function dataList($where='', $order= '', $desc = false, $limit = 0)
    {
        if ($order) {
            $where .= " order by $order";
            if ($desc) {
                $where .= " desc";
            } else {
                $where .= " asc";
            }
        }

        if ($limit) {
            $where .= " limit " . $limit;
        }

        $sql = "select * from $this->tablename $where;";
        
        return $this->select($sql);
    }

    /**
     * 分页结果
     */
    public function pageList($where, $page = 1, $limit = 10)
    {
        $res = $this->query("select count(*) as total from $this->tablename $where;"); 
        $data = $res->fetchArray(SQLITE3_ASSOC);
        
        $offset = ($page - 1) * $limit;
        $sql = "select * from $this->tablename $where limit $offset, $limit;";
        $list = $this->select($sql);
        $data['list'] = $list;
        $data['page'] = $page;
        $data['limit'] = $limit;
        
        return $data;
    }
    
    public function listByName($name='', $order = '', $desc = false)
    {
        $where = "";
        if ($name) {
            $where = " where name like '%$name%'";
        }

        if ($order) {
            $where .= " order by $order";
            if ($desc) {
                $where .= " desc";
            } else {
                $where .= " asc";
            }
        }
        $sql = "select * from $this->tablename $where;";
        $res = $this->select($sql);

        return $res;
    }

    /**
     * save
     */
    public function save($data)
    {
        $columns = "";
        $values = "";
        foreach ($data as $key => $value) {
            $columns .=  "`" . $key . "`,";
            $values .=  "'" . $value . "',";
        }
        
        $columns = rtrim($columns, ',');
        
        $values = rtrim($values, ',');
        
        $sql = "INSERT INTO `$this->tablename`(" . $columns . ") VALUES(". $values . ");";

        if ($this->exec($sql)) {
            return true;
        } else {
            throw new Exception("Error SQL Request", 1);   
        }
    }
    
    /**
     * updateById
     */
    public function updateById($data)
    {
        $id = $data['id'];
        unset($data['id']);
        $columns = "";
        foreach ($data as $key => $value) {
            $columns .= "`" . $key . "`='" . $value ."',";
        }
        $columns = rtrim($columns, ',');
        
        $sql = "UPDATE `$this->tablename` SET $columns WHERE `id`=$id";

        if ($this->exec($sql)) {
            return true;
        } else {
            throw new Exception("Error SQL Request", 1);   
        }
    }

    /**
     * deleteByIds
     */
    public function deleteById($id)
    {
        if (is_array($id)) {
            $sql = "DELETE FROM `$this->tablename` WHERE `id` IN(";
            for ($i=0; $i < count($id); $i++) { 
                $sql .= $id[$i] . ',';
            }
            $sql = rtrim($sql, ',');
            $sql .= ");";
        } else {
            $sql = "DELETE FROM `$this->tablename` WHERE `id`=$id;";
        }
        return $this->exec($sql);
    }

    public function __destruct()
    {
        $this->mydb->close();
    }
}