Simple PHP MySQL Class

mysql.php

<?php

	class Mysql{
	
		static private $link = null;
		static private $info = array(
			'last_query' => null,
			'num_rows' => null,
			'insert_id' => null
		);
		static private $connection_info = array();
		
		static private $where;
		static private $limit;
		static private $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)) mysql_close(self::$link);
		}
		
		/**
		 * 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 = mysql_connect(self::$connection_info['host'], self::$connection_info['user'], self::$connection_info['pass'])) && mysql_select_db(self::$connection_info['db'], $link)){
					self::$link = $link;
					mysql_set_charset('utf8');
				}else{
					throw new Exception('Could not connect to MySQL database.');
				}
			}
			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, mysql_real_escape_string($value));
				}else{
					$where .= sprintf(" %s `%s`='%s'", $type, $row, mysql_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 = mysql_query($qry);
			if(is_resource($result)){
				self::set('num_rows', mysql_num_rows($result));
			}
			if($return){
				if(preg_match('/LIMIT 1/', $qry)){
					$data = mysql_fetch_assoc($result);
					mysql_free_result($result);
					return $data;
				}else{
					$data = array();
					while($row = mysql_fetch_assoc($result)){
						$data[] = $row;
					}
					mysql_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 = mysql_query($sql))){
				throw new Exception('Error executing MySQL query: '.$sql.'. MySQL error '.mysql_errno().': '.mysql_error());
				$data = false;
			}elseif(is_resource($result)){
				$num_rows = mysql_num_rows($result);
				self::set('num_rows', $num_rows);
				if($num_rows === 0){
					$data = false;
				}elseif(preg_match('/LIMIT 1/', $sql)){
					$data = mysql_fetch_assoc($result);
				}else{
					$data = array();
					while($row = mysql_fetch_assoc($result)){
						$data[] = $row;
					}
				}
			}else{
				$data = false;
			}
			mysql_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',", mysql_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(!mysql_query($sql)){
				throw new Exception('Error executing MySQL query: '.$sql.'. MySQL error '.mysql_errno().': '.mysql_error());
			}else{
				self::set('insert_id', mysql_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, mysql_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(!mysql_query($sql)){
					throw new Exception('Error executing MySQL query: '.$sql.'. MySQL error '.mysql_errno().': '.mysql_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(!mysql_query($sql)){
					throw new Exception('Error executing MySQL query: '.$sql.'. MySQL error '.mysql_errno().': '.mysql_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();
	}

Missing ?> at end of each file

?> is not necessary to close an all-PHP file.

Nice

Hi,
I need to introduce LIKE condition in WHERE function.

SELECT * FROM db_user where name LIKE '%to%'

How can I do?

Thanks

Ussage.php line 18: $posts = $mysql->get('posts', array('title', 'author'); is missing a ) before ;

For the rest: beautiful! That saves a lot of time. Thanks :)

Hi this is a nice Class but how to use it with php7? I tryed to convert it to mysqli_* but didn't work.
Many Tanks.

You dont need ?> on a pure php file

Can I Limit and Offset?

this code was very usefull and practicall for small projects like reports and stuff fo first of thank you @mloberg, now for those interested in a PHP7 compatible version (@vanowm) i have updated this code here, improvements:

• Working with mysqli (since mysql library is deprecated and removed from PHP7)
• Joins implemented
• Fixed multiple instances (the original code only support one connection at the time since it was using static variables)

Missing ?> at end of each file

it normal. php practice.

A very useful class that I have updated to mysqli (see my fork). One minor correction is to the patterns for preg_match with LIMIT. These don't work if a LIMIT of 10, say, has been set. My version is '/LIMIT 1[^\d]/' and this seems to work OK.

elseif (preg_match ( '/LIMIT 1[^\d]/', $sql )) {

[^\d] here you says that you wanna using everything, but not digit [0-9] - only 1 digit. [0-9]+ 1 or more digits [0-9]* no digits or few digit
[0-9] {2} - 2 digit. [0-9] {, 2}. From n to 2.
See documentation of pregmatch

The preg_match is being used to decide how many results to return. The choice is between 1 (LIMIT 1) or more than 1. So 1\d limits the choice to a LIMIT of 1 (and not 10, 11, 12 etc). The original pattern failed on a LIMIT of 10 and returned just 1 match.

It clear. Just i wrote about pregmatch ))

Where can I get step by step tutorial regarding this php script ?

Where can I get step by step tutorial regarding this php script ?

There are very many howto's on how to use MySQL or MariaDB with PHP. Or you could try the PHP page https://www.php.net/manual/en/book.mysqli.php