Simple PHP MYSQLi Class

MySQL.php

<?php

class Mysql
{
    private $link = null;
    private $info = array(
        'last_query' => null,
        'num_rows' => null,
        'insert_id' => null
    );
    private $connection_info = array();
    
    private $where;
    private $limit;
    private $offset;
    private $join;
    private $order;
    
    function __construct($host, $user, $pass, $db){
        $this->connection_info = array('host' => $host, 'user' => $user, 'pass' => $pass, 'db' => $db);
    }
    
    function __destruct(){
        if($this->link instanceof mysqli_result) mysqli_close($this->link);
    }
    
    /**
     * Setter method
     */
    
    private function set($field, $value){
        $this->info[$field] = $value;
    }
    
    /**
     * Getter methods
     */
    
    public function last_query(){
        return $this->info['last_query'];
    }
    
    public function num_rows(){
        return $this->info['num_rows'];
    }
    
    public function insert_id(){
        return $this->info['insert_id'];
    }
    
    /**
     * Create or return a connection to the MySQL server.
     */
    
    private function connection(){
        if(!is_resource($this->link) || empty($this->link)){
            if(($link = mysqli_connect($this->connection_info['host'], $this->connection_info['user'], $this->connection_info['pass'])) && mysqli_select_db($link, $this->connection_info['db'])){
                $this->link = $link;
                mysqli_set_charset($link, 'utf8');
            }else{
                throw new Exception('Could not connect to MySQL database.');
            }
        }
        return $this->link;
    }
    
    /**
     * MySQL Where methods
     */
    
    private function __where($info, $type = 'AND'){
        $link = self::connection();
        $where = $this->where;
        foreach($info as $row => $value){
            if(empty($where)){
                $where = sprintf("WHERE `%s`='%s'", $row, mysqli_real_escape_string($link, $value));
            }else{
                $where .= sprintf(" %s `%s`='%s'", $type, $row, mysqli_real_escape_string($link, $value));
            }
        }
        $this->where = $where;
    }
    
    private function __join($table, $condition, $type = 'INNER') {
        $join = $this->join;
        $join .= " {$type} JOIN {$table} ON ";
        if(is_array($condition)) {
            foreach ($condition as $key => $cond) {
                if($key > 0) {
                    $join .= " AND ";
                }
                $join .= $cond;
            }
        } else {
            $join .= $condition;
        }
        $this->join = $join;
    }
    public function join($table, $condition) {
        self::__join($table, $condition);
        return $this;
    }
    public function leftJoin($table, $condition) {
        self::__join($table, $condition, 'LEFT');
        return $this;
    }
    public function rightJoin($table, $condition) {
        self::__join($table, $condition, 'RIGHT');
        return $this;
    }
    public function crossJoin($table, $condition) {
        self::__join($table, $condition, 'CROSS');
        return $this;
    }
    
    public function where($field, $equal = null){
        if(is_array($field)){
            self::__where($field);
        }else{
            self::__where(array($field => $equal));
        }
        return $this;
    }
    
    public function and_where($field, $equal = null){
        return $this->where($field, $equal);
    }
    
    public function or_where($field, $equal = null){
        if(is_array($field)){
            self::__where($field, 'OR');
        }else{
            self::__where(array($field => $equal), 'OR');
        }
        return $this;
    }
    
    /**
     * MySQL limit method
     */
    
    public function limit($limit){
        $this->limit = 'LIMIT '.$limit;
        return $this;
    }
    
    /**
     * MySQL OFFSET method
     */
    public function offset($offset){
        $this->offset="OFFSET ".$offset;
        return $this;
    }
    
    /**
     * MySQL Order By method
     */
    
    public function order_by($by, $order_type = 'DESC'){
        $order = $this->order;
        if(is_array($by)){
            foreach($by as $field => $type){
                if(is_int($field) && !preg_match('/(DESC|desc|ASC|asc)/', $type)){
                    $field = $type;
                    $type = $order_type;
                }
                if(empty($order)){
                    $order = sprintf("ORDER BY `%s` %s", $field, $type);
                }else{
                    $order .= sprintf(", `%s` %s", $field, $type);
                }
            }
        }else{
            if(empty($order)){
                $order = sprintf("ORDER BY `%s` %s", $by, $order_type);
            }else{
                $order .= sprintf(", `%s` %s", $by, $order_type);
            }
        }
        $this->order = $order;
        return $this;
    }
    
    /**
     * MySQL query helper
     */
    
    private function extra(){
        $extra = '';
        if(!empty($this->where)) $extra .= ' '.$this->where;
        if(!empty($this->join)) $extra .= ' '.$this->join;
        if(!empty($this->order)) $extra .= ' '.$this->order;
        if(!empty($this->limit)) $extra .= ' '.$this->limit;
        if(empty($this->offset)&&!empty($this->limit))$extra .= ' ';
        
        if(!empty($this->offset)&&(/* !empty($this->where)|| */!empty($this->limit))) $extra .= ' '.$this->offset;
        elseif (!empty($this->offset)) $extra .= ' LIMIT 18446744073709551615 '.$this->offset;
        
        // cleanup
        $this->where = null;
        $this->join = null;
        $this->order = null;
        $this->limit = null;
        $this->offset = null;
        return $extra;
    }
    
    /**
     * MySQL Query methods
     */
    
    public function query($qry, $return = false){
        $link = self::connection();
        self::set('last_query', $qry);
        $result = mysqli_query($link, $qry);
        if($result instanceof mysqli_result){
            self::set('num_rows', mysqli_num_rows($result));
        }
        if($return){
            if(preg_match('/LIMIT 1/', $qry)){
                $data = mysqli_fetch_assoc($result);
                mysqli_free_result($result);
                return $data;
            }else{
                $data = array();
                while($row = mysqli_fetch_assoc($result)){
                    $data[] = $row;
                }
                mysqli_free_result($result);
                return $data;
            }
        }
        return true;
    }
    
    public function get($table, $select = '*'){
        $link = self::connection();
        if(is_array($select)){
            $cols = '';
            foreach($select as $col){
                $cols .= "{$col},";
            }
            $select = substr($cols, 0, -1);
        }
        $sql = sprintf("SELECT %s FROM %s%s", $select, $table, self::extra());
        self::set('last_query', $sql);
        if(!($result = mysqli_query($link,$sql))){
            throw new Exception('Error executing MySQL query: '.$sql.'. MySQL error '.mysqli_errno($link).': '.mysqli_error($link));
            $data = false;
        }elseif($result instanceof mysqli_result){
            $num_rows = mysqli_num_rows($result);
            self::set('num_rows', $num_rows);
            if($num_rows === 0){
                $data = false;
                /*todo modify space*/
            }elseif(preg_match('/LIMIT 1 /', $sql) || $num_rows===1){
                $data = mysqli_fetch_assoc($result);
            }else{
                $data = array();
                while($row = mysqli_fetch_assoc($result)){
                    $data[] = $row;
                }
            }
        }else{
            $data = false;
        }
        mysqli_free_result($result);
        return $data;
    }
    
    public function insert($table, $data){
        $link = self::connection();
        $fields = '';
        $values = '';
        foreach($data as $col => $value){
            $fields .= sprintf("`%s`,", $col);
            $values .= sprintf("'%s',", mysqli_real_escape_string($link, $value));
        }
        $fields = substr($fields, 0, -1);
        $values = substr($values, 0, -1);
        $sql = sprintf("INSERT INTO %s (%s) VALUES (%s)", $table, $fields, $values);
        self::set('last_query', $sql);
        if(!mysqli_query($link,$sql)){
            throw new Exception('Error executing MySQL query: '.$sql.'. MySQL error '.mysqli_errno($link).': '.mysqli_error($link));
        }else{
            self::set('insert_id', mysqli_insert_id($link));
            return true;
        }
    }
    
    public function update($table, $info){
        if(empty($this->where)){
            throw new Exception("Where is not set. Can't update whole table.");
        }else{
            $link = self::connection();
            $update = '';
            foreach($info as $col => $value){
                $update .= sprintf("`%s`='%s', ", $col, mysqli_real_escape_string($link, $value));
            }
            $update = substr($update, 0, -2);
            $sql = sprintf("UPDATE %s SET %s%s", $table, $update, self::extra());
            self::set('last_query', $sql);
            if(!mysqli_query($link,$sql)){
                throw new Exception('Error executing MySQL query: '.$sql.'. MySQL error '.mysqli_errno($link).': '.mysqli_error($link));
            }else{
                return true;
            }
        }
    }
    
    public function delete($table){
        if(empty($this->where)){
            throw new Exception("Where is not set. Can't delete whole table.");
        }else{
            $link = self::connection();
            $sql = sprintf("DELETE FROM %s%s", $table, self::extra());
            self::set('last_query', $sql);
            if(!mysqli_query($link,$sql)){
                throw new Exception('Error executing MySQL query: '.$sql.'. MySQL error '.mysqli_errno($link).': '.mysqli_error($link));
            }else{
                return true;
            }
        }
    }
}

?>

UsageExample.php

<?php
	include_once('MySQL.php');
	
	$mysql = new MySQL('host', 'user', 'password', 'database');
	
	// get all posts
	try{
		$posts = $mysql->get('posts');
		print_r($posts);
		echo $mysql->num_rows(); // number of rows returned
	}catch(Exception $e){
		echo 'Caught exception: ', $e->getMessage();
	}
	
	// get all post titles and authors
	try{
		$posts = $mysql->get('posts', array('title', 'author');
		// or
		$posts = $mysql->get('posts', 'title,author');
		print_r($posts);
		echo $mysql->last_query(); // the raw query that was ran
	}catch(Exception $e){
		echo 'Caught exception: ', $e->getMessage();
	}
	
	// get one post
	try{
		$post = $mysql->limit(1)->get('posts');
		print_r($post);
	}catch(Exception $e){
		echo 'Caught exception: ', $e->getMessage();
	}
	// get offset 5 row
	try{
		$post = $mysql->offset(5)->get('posts');
		print_r($post);
	}catch(Exception $e){
		echo 'Caught exception: ', $e->getMessage();
	}
	// get post after offset 10 rowes
	try{
		$post = $mysql->->offset(10)->get('posts');
		print_r($post);
	}catch(Exception $e){
		echo 'Caught exception: ', $e->getMessage();
	}
	
	// get post with an id of 1
	try{
		$post = $mysql->where('id', 1)->get('posts');
		// or
		$post = $mysql->where(array('id', 1))->get('posts');
		print_r($post);
	}catch(Exception $e){
		echo 'Caught exception: ', $e->getMessage();
	}
	
	// get all posts by the author of "John Doe"
	try{
		$posts = $mysql->where(array('author' => 'John Doe'))->get('posts');
		print_r($posts);
	}catch(Exception $e){
		echo 'Caught exception: ', $e->getMessage();
	}

    // get all posts by the author of "John Doe" and author name
    // available join methods 'join' (inner join), 'leftJoin', 'rightJoin', 'crossJoin'
	try{
		$posts = $mysql->where(array('author' => 'John Doe'))->join('authors')->get('posts', ['posts.*', 'author.name']);
		print_r($posts);
	}catch(Exception $e){
		echo 'Caught exception: ', $e->getMessage();
	}
                             
                             
	// insert post
	try{
		$mysql->insert('posts', array('title' => 'New Title', 'content' => 'post content', 'author' => 'Matthew Loberg'));
		echo $mysql->insert_id(); // id of newly inserted post
	}catch(Exception $e){
		echo 'Caught exception: ', $e->getMessage();
	}
	
	// update post 1
	try{
		$mysql->where('id', 1)->update('posts', array('title' => 'New Title'));
	}catch(Exception $e){
		echo 'Caught exception: ', $e->getMessage();
	}
	
	// delete post 1
	try{
		$mysql->where('id', 1)->delete('posts');
	}catch(Exception $e){
		echo 'Caught exception: ', $e->getMessage();
	}

see https://gist.github.com/ildarkhasanshin/7e7d8523bc5f733395e8e772e1162b64/revisions

When:
$db_articles = $mysql->limit($page_number.', '.$articles_limit)->get($table_prefix.'articles');

When $page_number is the last record in db table, then something don't work correctly.

$db_articles = $mysql->limit($page_number.', '.$articles_limit)->get($table_prefix.'articles'); echo "<TABLE class='t1'>"; echo "<tr><th>ID</th><th>Tytuł</th><th>Data modyfikacji</th><th>Edytuj</th><th>Usuń</th></tr>"; foreach ($db_articles as $roww) { $m = $i%2; /* Alternate colors of the table */ $i=$i+1; /* increment for change style of the table */ echo "<tr class='r$m'> <td>".$roww[id]."</td> <td>".$roww[title]."</td> <td>".$roww[reg_date]."</td> <td> <a href='$page_name?edit_article=".$roww[id]."'>edytuj</a></td> <td> <a href='$page_name?delete_article=".$roww[id]."'>Usuń</a></td></tr>"; }; echo "</table>";

How to fix that?