Common-PDO-Model-Library/lib.php

<?php

use \PDO as PDO;
use \PDOException as PDOException;

$cfg = "config.json";
foreach (json_decode(file_get_content($cfg)) as $k => $v)
    define($k, $v);

class Db
{
    private static $pdo = null;

    public static function pdo()
    {
        if (self::$pdo !== null) {
            return self::$pdo;
        }

        try {
            $dsn    = sprintf('mysql:host=%s;dbname=%s;charset=utf8', DB_HOST, DB_NAME);
            $option = [
                PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_OBJ,
                PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
            ];

            self::$pdo = new PDO($dsn, DB_USER, DB_PASS, $option);

            if (self::$pdo === null)
            {
                die("Failed to create database object");
            }

            return self::$pdo;
        } catch (PDOException $e) {
            die("Database connection error");
        }
    }
}

class SqlResult
{
    public $result;
    public $error;
    public $isOk;

    public function __construct($res, $err)
    {
        $this->result = $res;
        $this->error  = $err;
        $this->isOk   = empty($this->error);
    }
}

// this class based on and modified from: https://www.awaimai.com/128.html
class Sql
{
    public $table;
    private $filter = '';
    private $param = [];
    private $special_cols = [];
    public $error = null;
    private $distinct = "";
    private $debug = 0;

    /**
     * changing the table to $t
     */
    public function table($t, $debugLevel = 0)
    {
        if (strpos($t, '.') !== false)
            $t = implode('`.`', explode('.', $t));
            
        $this->table = $t;

        $this->filter = '';
        $this->param = [];
        $this->error = "";
        $this->distinct = "";
        $this->debug = $debugLevel;
        
        return $this;
    }

    /**
     * enable/disable the distinction while query
     */
    public function distinct($t = true)
    {
        $this->distinct = $t ? "DISTINCT " : "";
        return $this;
    }

    /**
     * execute SQL directly with only affected row count returned.
     * 
     * @param $statment : SQL statment that want to be executed.
     * @param $param    : inputs that used in the statment. will be filtered by PDO.
     * @param $fetchMode: [0: none, 1: fetch, 2: fetchAll, 3: rowCount]
     * @param $fetchType: only if fetchMode in [1, 2]
     * @return SqlResult: execution result
     */
    public function exec($statment, $param = [], $fetchMode = 0, $fetchType = PDO::FETCH_OBJ)
    {
        $sth = Db::pdo()->prepare($statment);
        $sth = $this->formatParam($sth, $param);

        if ($sth->execute() == false)
        {
            // have error!
            $this->error = $sth->errorInfo();
        }

        switch ($fetchMode)
        {
            case 1:
                $mode_result = $sth->fetch($fetchType);
            break;
            case 2:
                $mode_result = $sth->fetchAll($fetchType);
            break;
            case 3:
                $mode_result = (int)$sth->rowCount();
            break;
            default:
                $mode_result = true;
        }

        return new SqlResult($mode_result, $this->error);
    }

    /**
     * newly write, me hea ar, 5 write desc la :(
     * 
     * ie:
     *      CAST(... AS ...) as sp_col
     * 
     * ... -> group(...) -> having("decrypted LIKE '%?%'", ["keyword"], 'CAST(... ?) as decrypted', ["value"])
     */
    public function having(string $having_stmt, array $val, string $col, array $param = [])
    {
        array_unshift($this->special_cols, $col);
        $this->param = array_merge($param, $this->param); // prepend merge

        $this->filter .= " HAVING $having_stmt ";
        $this->param = array_merge($this->param, $val);

        return $this;
    }

    /**
     * in function (SQL: IN):
     * 
     * ie:
     * // "where" must be used before "in"
     * 1. $this->table('log')->where(['status = ?'], [$status])->in('AND', 'sid', $staffIDarray)->fetch();
     * // OR without "where"
     * 2. $this->table('log')->in('', 'sid', $staffIDarray)->fetch();
     *
     * @param string $connector : AND / OR
     * @param string $colName   : column name
     * @param array  $param     : value set
     * @param string $ending    : [Optional] (default: ""), after "in", put what at the end of SQL (currently)
     * @return $this : this Object
     */
    public function in(string $connector, string $colName, array $param, string $ending = "")
    {
        if (!empty($param))
        {
            if (empty($this->filter) || strpos($this->filter, 'WHERE') === false)
                $this->filter .= ' WHERE ';
            else if (!empty($this->filter))
                $this->filter .= " " . $connector . " ";

            if (strpos($colName, "`") === false)
            {
                $colName = '`' . $colName . '`';
            }

            $this->filter .= " " . $colName . ' IN (';
            $this->filter .= implode(',', array_fill(0, count($param), '?'));
            $this->filter .= ') ';

            if (!empty($ending))
                $this->filter .= $ending;
    
            $this->param = array_merge($this->param, $param);
        }

        return $this;
    }

    /**
     * where function (SQL condition):
     * 
     * ie:
     * 1. $this->table('login')->where(['id = :id'], [':id' => $id])->fetch();
     * 2. $this->table('login')->where(['id = ?'], [$id])->fetch();
     * 
     * // multiple conditions
     * 3. $this->table('login')->where(['id = :id', 'AND uname = :uname'], [':id' => $id, ':uname' => $uname])->fetch();
     * 4. $this->table('login')->where(['id = ?', 'OR uname = ?'], [$id, $uname])->fetch();
     *
     * @param array $where : condition
     * @param array $param : values
     * @return $this : this Object
     */
    public function where(array $where = [], array $param = [])
    {
        if ($where) {
            $this->filter .= ' WHERE ';
            $this->filter .= implode(' ', $where);

            $this->param = array_merge($this->param, $param);
        }

        return $this;
    }

    /**
     * order function (SQL: ORDER BY):
     * 
     * ie:
     * 1. $this->order(['id DESC'])->fetch();
     * 2. $this->order(['id DESC', 'username ASC'])->fetch();
     *
     * @param array $order : order condition
     * @return $this : this Object
     */
    public function order($order = [])
    {
        if ($order) {
            $this->filter .= ' ORDER BY ';
            
            if (!is_array($order))
                $this->filter .= $order;
            else
                $this->filter .= implode(',', $order);
        }

        return $this;
    }

    /**
     * group function (SQL: GROUP BY):
     *
     * ie:
     * 1. $this->group(['id'])->fetch();
     * 2. $this->group(['id', 'username'])->fetch();
     *
     * @param array $group
     * @return $this
     */
    public function group($group = [])
    {
        if($group) {
            $this->filter .= ' GROUP BY ';
            $this->filter .= implode(',', $group);
        }

        return $this;
    }

    /**
     * fetchAll function:
     * 
     * ie:
     * 1. $this->table('login')->where(['username=?', 'AND hashed=?'], [$username, $hashed])->fetchAll();
     * 
     * @param array $cols     : [Optional] (default: all columns), the required column names
     * @param array $dataType : [Optional] (default: PDO::FETCH_OBJ), the required data type (PDO::FETCH_OBJ, PDO::FETCH_NUM, PDO::FETCH_BOTH, ...)
     * 
     * @return array $return:
     *    @ if success:
     *      ["result"] => true
     *      ["data"]   => $sth->fetchAll() : same as PDOStatement::fetchAll()
     * 
     *    @ if failed:
     *      ["result"] => false
     *      ["error"]  => $this->error (raw error message)
     */
    public function fetchAll($cols = ["*"], $dataType = PDO::FETCH_OBJ)
    {
        $sql = sprintf("SELECT %s %s FROM `%s` %s", $this->distinct, implode(',', array_merge($cols, $this->special_cols)), $this->table, $this->filter);
        $sth = Db::pdo()->prepare($sql);
        $sth = $this->formatParam($sth, $this->param);
        if ($this->debug)
            $this->debugDump($sth);

        if ($sth->execute() == false)
        {
            // have error!
            $this->error = $sth->errorInfo();
        }

        return new SqlResult($sth->fetchAll($dataType), $this->error);
    }

    /**
     * fetch function:
     * 
     * ie:
     * 1. $this->table('login')->where(['username=?', 'AND hashed=?'], [$username, $hashed])->fetch();
     * 
     * @param array $cols     : [Optional] (default: all columns), the required column names
     * @param array $dataType : [Optional] (default: PDO::FETCH_OBJ), the required data type (PDO::FETCH_OBJ, PDO::FETCH_NUM, PDO::FETCH_BOTH, ...)
     * 
     * @return array $return:
     *    @ if success:
     *      ["result"] => true
     *      ["data"]   => $sth->fetch() : same as PDOStatement::fetch()
     * 
     *    @ if failed:
     *      ["result"] => false
     *      ["error"]  => $this->error (raw error message)
     */
    public function fetch($cols = ["*"], $dataType = PDO::FETCH_OBJ)
    {
        $sql = sprintf("SELECT %s %s FROM `%s` %s", $this->distinct, implode(',', array_merge($cols, $this->special_cols)), $this->table, $this->filter);
        $sth = (Db::pdo())->prepare($sql);
        $sth = $this->formatParam($sth, $this->param);

        if ($sth->execute() == false)
        {
            // have error!
            $this->error = $sth->errorInfo();
        }

        return new SqlResult($sth->fetch($dataType), $this->error);
    }

    /**
     * rowCount function:
     * 
     * ie:
     * 1. $this->table('login')->where(['username=?', 'AND hashed=?'], [$username, $hashed])->rowCount();
     * 
     * @return int $sth->fetchColumn()
     */
    public function rowCount()
    {
        $sql = sprintf("SELECT %s COUNT(*) FROM `%s` %s", $this->distinct, $this->table, $this->filter);
        $sth = Db::pdo()->prepare($sql);
        $sth = $this->formatParam($sth, $this->param);

        if ($sth->execute() == false)
        {
            // have error!
            $this->error = $sth->errorInfo();
        }

        return new SqlResult((int)$sth->fetchColumn(), $this->error);
    }

    /**
     * delete function:
     * 
     * ie:
     * 1. $this->table('login')->where(['username=?', 'AND hashed=?'], [$username, $hashed])->delete();
     * 
     * @return integer $sth->rowCount() : return the number of changed rows.
     *      return 0 when no changes.
     *      return -1 when trying to delete the whole database.
     */
    public function delete()
    {
        if (empty($this->filter))
            return -1;  // do not allow delete whole db.

        $sql = sprintf("DELETE FROM `%s` %s", $this->table, $this->filter);
        $sth = Db::pdo()->prepare($sql);
        $sth = $this->formatParam($sth, $this->param);

        if ($sth->execute() == false)
        {
            // have error!
            $this->error = $sth->errorInfo();
        }

        return new SqlResult($sth->rowCount(), $this->error);
    }

    /**
     * add function:
     * 
     * ie:
     * 1. $this->table('login')->add($data);
     * 
     * @param array $data : the data with column name & data
     *          ie:
     *              $data = [
     *                  "username" => "user01",
     *                  "hash"     => "678e82d907d3e6e71f81d5cf3ddacc3671dc618c38a1b7a9f9393a83d025b296" // plaintext: test01
     *              ];
     * 
     * @return integer $sth->rowCount() : return the number of changed rows.
     *      return 0 when insertion failed.
     */
    public function add($data)
    {
        $sql = sprintf("INSERT INTO `%s` %s", $this->table, $this->formatInsert($data));
        $sth = Db::pdo()->prepare($sql);
        $sth = $this->formatParam($sth, $data);

        if ($sth->execute() == false)
        {
            // have error!
            print_r($sth->errorInfo());
            $this->error = $sth->errorInfo();
        }

        return new SqlResult($sth->rowCount(), $this->error);
    }
    
    /**
     * update function:
     * 
     * ie:
     * 1. $this->table('login')->where(['id=1'])->update($data);
     * 
     * @param array $data : the data with column name & data
     *          ie:
     *              $data = [
     *                  "username" => "user01",
     *                  "hash"     => "678e82d907d3e6e71f81d5cf3ddacc3671dc618c38a1b7a9f9393a83d025b296" // plaintext: test01
     *              ];
     * 
     * @return integer $sth->rowCount() : return the number of changed rows.
     *      return 0 when update failed.
     */
    public function update($data)
    {
        $sql = "UPDATE `{$this->table}` SET {$this->formatUpdate($data)} {$this->filter}";
        $sth = Db::pdo()->prepare($sql);
        $sth = $this->formatParam($sth, $data);
        $sth = $this->formatParam($sth, $this->param);

        if ($sth->execute() == false)
        {
            // have error!
            $this->error = $sth->errorInfo();
        }

        return new SqlResult($sth->rowCount(), $this->error);
    }

    /**
     * paramesters binding
     * @param PDOStatement $sth PDOStatement that want to be binded
     * @param array $params value of params：
     * 1. if using "?" as binding symbol, then $params should be like:
     *    [$a, $b, $c]
     * 2. if using ":" as binding symbol, then $params should be like:
     *    2.1 without binding symbol:
     *        ['a' => $a, 'b' => $b, 'c' => $c]
     *    OR
     *    2.2 with binding symbol (":"):
     *        [':a' => $a, ':b' => $b, ':c' => $c]
     *
     * @return PDOStatement
     */
    private function formatParam(PDOStatement $sth, $params = [])
    {
        foreach ($params as $param => &$value) {
            $param = is_int($param) ? $param + 1 : ':' . ltrim($param, ':');
            if (gettype($value) == "array")
                $sth->bindParam($param, $value[0], $value[1], $value[2] ?? sizeof($value[0]));
            else
                $sth->bindParam($param, $value);
        }

        return $sth;
    }

    // parse data to insertion SQL format (value pairs)
    private function formatInsert($data)
    {
        $fields = [];
        $names = [];
        foreach ($data as $key => $value) {
            $fields[] = "`{$key}`";
            $names[] = ":{$key}";
        }

        $field = implode(',', $fields);
        $name = implode(',', $names);

        return "({$field}) values ({$name})";
    }

    // parse data to update SQL format (key-value pairs)
    private function formatUpdate($data)
    {
        $fields = [];
        foreach ($data as $key => $value) {
            $fields[] = "`{$key}` = :{$key}";
        }

        return implode(',', $fields);
    }

    // database DATETIME() function
    protected function DATETIME()
    {
        return date('Y-m-d H:i:s');
    }

    protected function debugDump($sth, $useOfficial = true)
    {
        $useOfficial ? $sth->debugDumpParams() : var_dump($sth, $this->param);
    }
}

$sql = new Sql();

function generateRandomString($length) {
    // src: https://stackoverflow.com/a/13212994
    return substr(str_shuffle(str_repeat($x='0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ', ceil($length/strlen($x)) )),1,$length);
}