Function based MySQL Convention for PHP

SQLer.example.php

<?php
$sql = new SQLer('localhost', 'user', 'pass'. 'my_db');
// query_fetch example
$name = '';
$fetch = $sql->query_fetch('SELECT name, content FROM my_table LIMIT 10 WHERE name LIKE CONCAT('%', ?, '%')', $name);
// do something
?><dl><?php
$fetch->exec(function($name, $content) {
    ?>
    <dt><?=$name?></dt>
    <dd><?=$content?></dd>
    <?php
}); ?></dl><?php
// query_result example (query_store too.)
$name = '';
$result = $sql->query_result('SELECT name, content FROM my_table LIMIT 10 WHERE name LIKE CONCAT('%', ?, '%')', $name);
// do something
?><dl><?php
$result->exec(function($result) {
    while($row = $result->fetch_array()){
    ?>
    <dt><?=$row['name']?></dt>
    <dd><?=$row['content']?></dd>
    <?php
    }
}); ?></dl><?php
// query example (query_single_row, query_single_array too. except return value.)
$name = 'some title';
$content = 'some content';
//execute immediately.
$count = $sql->query('INSERT INTO my_table (name, content) VALUES (?, ?)', $name, $content);
// end of example.

SQLer.php

<?php
class SQLer
{
    private $db;

    public function __construct($host, $user, $pass, $db, $port = 3306)
    {
        $this->db = new mysqli($host, $user, $pass, $db, $port);
        mysqli_query($this->db,"set names utf8");
        if ( mysqli_connect_errno() ) {
            throw new mysqli_sql_exception(mysqli_connect_error());
        }
    }

    /**
     * get bind type for mysqli_statement::bind_param.
     * @param mixed $var the binding variable.
     * @return string
     */
    private function get_bind_type($var){
        switch(true){
            case is_int($var): return 'i';
            case is_double($var): return 'd';
            //TODO b for blob
            default: return 's';
        }
    }

    /**
     * Initialize query and return callback for flexible fetch process.
     * @param string $query The statement query.
     * @param array ...$params The binding variables.
     * @return SQLer_Fetcher it'll be executed when calling this function with fetch function.
     * @throws Exception statement failed.
     */
    public function query_fetch(string $query, ...$params) {
        if($stmt = $this->db->prepare($query)){

            if(count($params) > 0){
                $args = array();
                $types = '';
                $args[] = &$types;
                foreach ($params as $i=>$v){
                    $types .= $this->get_bind_type($v);
                    $args[] = &$params[$i];
                }
                call_user_func_array(array($stmt, "bind_param"), $args);
            }

            return new SQLer_Fetcher($stmt);
        }else throw new Exception("Statement failed in query $query");
    }

    /**
     * Excute the query immediately and get single row as indexed array.
     * @param string $query The statement query.
     * @param array ...$params The binding variables.
     * @return array|bool if row exists, single row array returned. FALSE otherwise.
     * @throws Exception statement failed.
     */
    public function query_single_row(string $query, ...$params){
        if($stmt = $this->db->prepare($query)){

            if(count($params) > 0){
                $args = array();
                $types = '';
                $args[] = &$types;
                foreach ($params as $i=>$v){
                    $types .= $this->get_bind_type($v);
                    $args[] = &$params[$i];
                }
                call_user_func_array(array($stmt, "bind_param"), $args);
            }

            $stmt->execute();
            $meta = $stmt->result_metadata();
            $result = array();
            while ($field = $meta->fetch_field()) $result[] = &$field->name;
            call_user_func_array(array($stmt, "bind_result"), $result);
            if(!$stmt->fetch()) $result = FALSE;
            $stmt->close();

            return $result;
        }else throw new Exception("Statement failed in query $query");
    }

    /**
     * Excute the query immediately and get single row as indexed and assoc array.
     * @param string $query The statement query.
     * @param array ...$params The binding variables.
     * @return array|bool if row exists, single row array returned. FALSE otherwise.
     * @throws Exception statement failed.
     */
    public function query_single_array(string $query, ...$params){
        if($stmt = $this->db->prepare($query)){

            if(count($params) > 0){
                $args = array();
                $types = '';
                $args[] = &$types;
                foreach ($params as $i=>$v){
                    $types .= $this->get_bind_type($v);
                    $args[] = &$params[$i];
                }
                call_user_func_array(array($stmt, "bind_param"), $args);
            }

            $stmt->execute();
            $meta = $stmt->result_metadata();
            $result = array();
            $names = array();
            while ($field = $meta->fetch_field()){
                $result[] = &$field->name;
                $names[] = $field->name;
            }
            call_user_func_array(array($stmt, "bind_result"), $result);
            if($stmt->fetch()){
                foreach ($names as $i => $n) $result[$n] = $result[$i];
            }else $result = FALSE;
            $stmt->close();

            return $result;
        }else throw new Exception("Statement failed in query $query");
    }

    /**
     * Initialize query and return callback for handle result.
     * @param string $query The statement query.
     * @param array ...$params The binding variables.
     * @return SQLer_Result it'll be executed when calling this function with mysqli_result.
     * @throws Exception statement failed.
     */
    public function query_result(string $query, ...$params) {
        if($stmt = $this->db->prepare($query)){

            if(count($params) > 0){
                $args = array();
                $types = '';
                $args[] = &$types;
                foreach ($params as $i=>$v){
                    $types .= $this->get_bind_type($v);
                    $args[] = &$params[$i];
                }
                call_user_func_array(array($stmt, "bind_param"), $args);
            }

            return new SQLer_Result($this, $stmt, FALSE);
        }else throw new Exception("Statement failed in query $query");
    }

    /**
     * Initialize query and return callback for handle stored result.
     * @param string $query The statement query.
     * @param array ...$params The binding variables.
     * @return SQLer_Result it'll be executed when calling this function with stored mysqli_result.
     * @throws Exception statement failed.
     */
    public function query_store(string $query, ...$params) {
        if($stmt = $this->db->prepare($query)){

            if(count($params) > 0){
                $args = array();
                $types = '';
                $args[] = &$types;
                foreach ($params as $i=>$v){
                    $types .= $this->get_bind_type($v);
                    $args[] = &$params[$i];
                }
                call_user_func_array(array($stmt, "bind_param"), $args);
            }

            return new SQLer_Result($this, $stmt, TRUE);
        }else throw new Exception("Statement failed in query $query");
    }

    /**
     * Just execute the query.
     * @param string $query The statement query.
     * @param array ...$params The binding variables.
     * @return int if success, affected row count will be returned. otherwise, FALSE.
     */
    public function query(string $query, ...$params) {
        if($stmt = $this->db->prepare($query)){

            if(count($params) > 0){
                $args = array();
                $types = '';
                $args[] = &$types;
                foreach ($params as $i=>$v){
                    $types .= $this->get_bind_type($v);
                    $args[] = &$params[$i];
                }
                call_user_func_array(array($stmt, "bind_param"), $args);
            }

            $stmt->execute();
            $count = $stmt->affected_rows;
            $stmt->close();
            return $count;
        }else{
            if ( mysqli_connect_errno() ) {
                throw new mysqli_sql_exception(mysqli_connect_error());
            }
        }
        return FALSE;
    }
}

class SQLer_Fetcher{
    private $stmt;
    private $closed = FALSE;
    public function __construct(mysqli_stmt $stmt){
        $this->stmt = $stmt;
    }

    /**
     * Execute this statement and fetch with callback.
     * @param callable $each The callback each fetch data. same as index in query column.
     * @param bool $preserve default FALSE to exec and close. TRUE if you want close manually.
     * @return int affected row count.
     * @throws Exception cannot call this method when statement has been closed.
     */
    public function exec(callable $each, $preserve = FALSE){
        if($this->closed) throw new Exception('This result has been closed');
        $this->stmt->execute();
        $meta = $this->stmt->result_metadata();
        $params = array();
        while ($field = $meta->fetch_field()) $params[] = &$field->name;
        call_user_func_array(array($this->stmt, "bind_result"), $params);
        $affected = 0;
        while ($this->stmt->fetch()){
            if(call_user_func_array($each, $params) === FALSE) break;
            $affected++;
        }
        if($preserve) $this->close();
        return $affected;
    }

    /**
     * Close this statement without execute.
     */
    public function close(){
        $this->stmt->close();
        $this->closed = TRUE;
    }
}

class SQLer_Result{
    private $sqler;
    private $stmt;
    private $closed = FALSE;
    private $stored = FALSE;
    public function __construct(SQLer $sqler, mysqli_stmt $stmt, $stored)
    {
        $this->sqler = $sqler;
        $this->stmt = $stmt;
        $this->stored = !!$stored;
    }

    /**
     * Execute this statement and handle result.
     * @param callable $result The callback with mysqli_result.
     * @param bool $preserve default FALSE to exec and close. TRUE if you want close manually.
     * @return mixed if callback return value is not NULL, return value will be returned. SQLer class otherwise.
     * @throws Exception cannot call this method when statement has been closed.
     */
    public function exec(callable $result, $preserve = FALSE){
        if($this->closed) throw new Exception('This result has been closed');
        $this->stmt->execute();
        $return = $result($this->stored ? $this->stmt->store_result() : $this->stmt->get_result());
        if($preserve) $this->close();
        return is_null($return) ? $this->sqler : $return;
    }

    /**
     * Close this statement without execute.
     */
    public function close(){
        $this->stmt->close();
        $this->closed = TRUE;
    }
}