Simple PHP MySQL Class

mysql.php

<?php
/* This is a mysqli update to the original code that works with PHP 7 variants.
*  There are also a couple of small corrections to the preg_match patterns for LIMIT
*/
class Mysql {
	private static $link = null;
	private static $info = array (
			'last_query' => null,
			'num_rows' => null,
			'insert_id' => null
	);
	private static $connection_info = array ();
	private static $where;
	private static $limit;
	private static $order;
	function __construct($host, $user, $pass, $db) {
		self::$connection_info = array (
				'host' => $host,
				'user' => $user,
				'pass' => $pass,
				'db' => $db
		);
	}
	function __destruct() {
		if (is_resource ( self::$link ))
			self::$link->close ();
	}

	/**
	 * Setter method
	 */
	static private function set($field, $value) {
		self::$info [$field] = $value;
	}

	/**
	 * Getter methods
	 */
	public function last_query() {
		return self::$info ['last_query'];
	}
	public function num_rows() {
		return self::$info ['num_rows'];
	}
	public function insert_id() {
		return self::$info ['insert_id'];
	}

	/**
	 * Create or return a connection to the MySQL server.
	 */
	static private function connection() {
		if (! is_resource ( self::$link ) || empty ( self::$link )) {
			if (($link = new mysqli ( self::$connection_info ['host'], self::$connection_info ['user'], self::$connection_info ['pass'], self::$connection_info ['db'] ))) {
				self::$link = $link;
				self::$link->set_charset ( 'utf8' );
			} else {
				throw new Exception ( 'Could not connect to database ' . self::$connection_info ['db'] );
			}
		}
		return self::$link;
	}

	/**
	 * MySQL Where methods
	 */
	static private function __where($info, $type = 'AND') {
		$link = self::connection ();
		$where = self::$where;
		foreach ( $info as $row => $value ) {
			if (empty ( $where )) {
				$where = sprintf ( "WHERE `%s`='%s'", $row, self::$link->real_escape_string ( $value ) );
			} else {
				$where .= sprintf ( " %s `%s`='%s'", $type, $row, self::$link->real_escape_string ( $value ) );
			}
		}
		self::$where = $where;
	}
	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) {
		self::$limit = 'LIMIT ' . $limit;
		return $this;
	}

	/**
	 * MySQL Order By method
	 */
	public function order_by($by, $order_type = 'DESC') {
		$order = self::$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 );
			}
		}
		self::$order = $order;
		return $this;
	}

	/**
	 * MySQL query helper
	 */
	static private function extra() {
		$extra = '';
		if (! empty ( self::$where ))
			$extra .= ' ' . self::$where;
		if (! empty ( self::$order ))
			$extra .= ' ' . self::$order;
		if (! empty ( self::$limit ))
			$extra .= ' ' . self::$limit;
		// cleanup
		self::$where = null;
		self::$order = null;
		self::$limit = null;
		return $extra;
	}

	/**
	 * MySQL Query methods
	 */
	public function query($qry, $return = false) {
		$link = self::connection ();
		self::set ( 'last_query', $qry );
		$result = $link->query ( $query );
		self::set ( 'num_rows', $result->num_rows );
		if ($return) {
			if (preg_match ( '/LIMIT 1[^\d]/', $qry )) {
				$data = $result->fetch_assoc ();
				$result->free_result ();
				return $data;
			} else {
				$data = array ();
				while ( $row = $result->fetch_assoc () ) {
					$data [] = $row;
				}
				$result->free_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 = $link->query ( $sql ))) {
			throw new Exception ( 'Error executing MySQL query: ' . $sql . '. MySQLi error ' . $link->errno . ': ' . $link->error );
			$data = false;
		} else {
			$num_rows = $result->num_rows;
			self::set ( 'num_rows', $num_rows );
			if ($num_rows === 0) {
				$data = false;
			} elseif (preg_match ( '/LIMIT 1[^\d]/', $sql )) {
				$data = $result->fetch_assoc ();
			} else {
				$data = array ();
				while ( $row = $result->fetch_assoc () ) {
					$data [] = $row;
				}
			}
		}
		$result->free_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',", $link->real_escape_string ( $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 (! $link->query ( $sql )) {
			throw new Exception ( 'Error executing MySQL query: ' . $sql . '. MySQL error ' . $link->errno . ': ' . $link->error );
		} else {
			self::set ( 'insert_id', $link->insert_id );
			return true;
		}
	}
	public function update($table, $info) {
		if (empty ( self::$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, $link->real_escape_string ( $value ) );
			}
			$update = substr ( $update, 0, - 2 );
			$sql = sprintf ( "UPDATE %s SET %s%s", $table, $update, self::extra () );
			self::set ( 'last_query', $sql );
			if (! $link->query ( $sql )) {
				throw new Exception ( 'Error executing MySQL query: ' . $sql . '. MySQL error ' . $link->errno . ': ' . $link->error );
			} else {
				return true;
			}
		}
	}
	public function delete($table) {
		if (empty ( self::$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 (! $link->query ( $sql )) {
				throw new Exception ( 'Error executing MySQL query: ' . $sql . '. MySQL error ' . $link->errno . ': ' . $link->error );
			} else {
				return true;
			}
		}
	}
}

usage.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 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();
	}
	
	// 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();
	}