PHP query building database class

api.php

<?php

// format
	$db->method( $arg type, [$optional_arg type1|type2 default_val])



// methods

	$db->select( $fields string )

	$db->from( $table string )


	$db->where( $data array|string, [$value mixed null] )

	$db->where_or( $data array|string, [$value mixed null] ) 

	$db->order_by( $data array|string, [$direction string 'ASC'] )

	$db->join( $table string, $on, string )

	$db->limit( $start int, $count int )


	$db->get( [$reset boolean false] )

	$db->get_one( [$reset boolean false] )

	$db->get_value( $field string, [$reset boolean false] )

	$db->get_fields( $table string )


	$db->set( $data array|string, [$value mixed null] )

	$db->insert( $table string, [$data array null] )

	$db->update( $table string, [$where array null], [$data array null] )

	$db->delete( $table string, [$where array null] )

database.class.php

<?php


class Database{


	private $debug = true;
	private $select;
	private $where;
	private $from;
	private $order;
	private $join;
	private $limit;

	public $last_query;
	public $last_insert_id;
	public $num_updated_rows;

	private $sets = array();
	private $connection = null;








	/*
		Connect to the database.
	*/

	public function __construct(){
		$this->connection = new mysqli(
			'localhost',
			'db',
			'user',
			'pass'
		);

		if($this->connection->errno){
			$this->report_arror('Database::__construct() - '.$this->connection->error);
		}
	}






	/*
		Write a SELECT statement.
	*/

	public function select($fields){
		$this->select = "SELECT $fields ";

		return $this;
	}






	/*
		Write a WHERE statement.

		It can be called multiple times. Only the first time it will use the WHERE clause.
		After that it will use AND.

		It can be used in two ways.

		The first way is a single combination of field and value, as two parameters.
		eg.
			$db->where('id', 1);

		The second way is as an array of multiple values, as the only parameter.
		eg.
			$db->where(array(
				'username' => $username,
				'password' => $db->hash($password),
				'admin'    => 1
			));

		Also, each time a field is defined, you can also add an operator.
		There MUST be a space before the operator.
		(This works for both of the above usage types)
		eg.
			$db->where(array(
				'product_type' => 5,
				'price <' => '1000'
			));
	*/

	public function where($data, $value = null){
		if($value != null){
			$data = array($data => $value);
		}

		$this->where .= $this->make_where($data);

		return $this;
	}


	public function where_or($data, $value = null){
		if($value != null){
			$data = array($data => $value);
		}

		$this->where .= $this->make_where($data, true);

		return $this;
	}









	/*
		Write the ORDER BY statement.
		ASC is the default direction.

		It can be written in multiple ways.

		You can write a single field and direction pair.
		Direction is optional.
		eg.
			$db->order_by('price');
			-- or --
			$db->order_by('price', 'desc');

		It can also take a single array of multiple field/direction pairs.
		Again, the 
		eg.
			$db->order_by(array(
				'price',
				'product_type' => 'desc'
			));
	*/

	public function order_by($data, $direction = 'ASC'){
		$order = ' ORDER BY ';

		if(is_array($data)){
			foreach($data as $key => $value){
				

				if(is_numeric($key)){
					$field = $value;
					$direction = 'ASC';
				}else{
					$field = $key;
					$direction = $value;
				}

				$order .= $field.' '.$direction.', ';
			}

			$this->order = substr($order, 0, -2);
		}else{
			$this->order .= $order.$data.' '.$direction;
		}

		return $this;
	}







	/*
		Write a FROM statement.
	*/

	public function from($table){
		$this->from = ' FROM '.$table;

		return $this;
	}







	/*
		Write an INNER JOIN.

		First parameter is the table to join.
		Second parameter is a string containing the two field to join on
		eg.
			$db->join('tb_pages', 'tb_pages.cat_id = tb_category.cat_id');
	*/

	public function join($table, $on){
		$this->join .= ' INNER JOIN '.$table;
		$this->join .= ' ON '.$on;

		return $this;
	}



	public function limit($start, $count){
		$this->limit = " LIMIT $start, $count ";

		return $this;
	}







	/*
		This function runs the current built up query and returns it as an associative array.
		
		It requires that $db->select() and $db->from() be run first.

		It uses the information created by the select, from, where, order and join functions.
		When it finishes, it clears all of that information.
	*/

	public function get($reset = true){
		if(!$this->select || !$this->from){
			$this->report_error('Database::Get() - SELECT or FROM not set.');
		}else{
			$q  = $this->select;
			$q .= $this->from;
			$q .= $this->join;
			$q .= $this->where;
			$q .= $this->order;
			$q .= $this->limit;

			if($reset) $this->reset();

			return $this->assoc($this->run($q));
		}
	}


	/*
		Runs a get() and returns only the first result
	*/

	public function get_one($reset = true){
		$result = $this->get($reset);
		return $result[0];
	}



	/*
		Runs a get() and returns only the single specified field from the first result.
		Doesn't require 	
	*/

	public function get_value($field, $reset = true){
		$this->select($field, $reset);
		$result = $this->get_one();

		return $result[$field];
	}







	public function get_fields($table){
		// This query should be built by this class
		$field_query = 'SELECT column_name FROM information_schema.columns WHERE table_name = "'.$table.'" ORDER BY ordinal_position';
		$result = $this->assoc($this->connection->query($field_query));

		foreach($result as $key => $field){
			$fields[] = $field['column_name'];
		}

		return $fields;
	}








	public function set($data, $value = null){
		if(is_array($data)){
			$this->sets = array_merge($data, $this->sets);
		}else{
			if($value != null){
				$this->sets[$data] = $this->filter($value);
			}
		}

		return $this;
	}








	public function insert($table, $data = null){

		if($data != null && is_array($data)){
			$this->set($data);
		}

		if(!count($this->sets)){
			$this->report_error('Database::insert() - No data to insert.', true);
		}

		$insert_query = 'INSERT INTO '.$table.$this->make_set($this->sets);

		$this->sets = array();

		$this->run($insert_query);

		$this->last_insert_id = $this->connection->insert_id;

		return $this;
	}








	public function update($table, $where = null, $data = null){

		if($data != null && is_array($data)){
			$this->set($data);
		}

		if(!count($this->sets)){
			$this->report_error('Database::update() - Missing SET clause.', true);
		}

		$update_query = 'UPDATE '.$table.$this->make_set($this->sets);

		if($where != null){
			$update_query .= $this->make_where($where);
		}else if($this->where != null){
			$update_query .= $this->where;
		}else{
			$this->report_error('Database::update() - Missing WHERE clause.');
		}

		$this->reset();

		$this->run($update_query);

		$this->num_updated_rows = $this->connection->affected_rows;

		return $this;
	}







	public function delete($table, $where = null){

		# If the supplied $where is supplied ...
		if($where != null){
			# ... then add to the existing WHERE clause
			$this->where .= $this->make_where($where);

		# If $where is not supplied, and there is not an existing WHERE clause...
		}else if($this->where == null){
			# ... report an error
			$this->report_error('Database::delete() - Missing WHERE clause.');
		}

		$delete_query = 'DELETE FROM '.$table.$this->where;

		$this->run($delete_query);

		$this->reset();

		return $this;
	}








	private function make_set($data){
		$set = ' SET ';

		foreach($data as $field => $val){
			$set .= $field.' = "'.$val.'", ';
		}

		return substr($set, 0, -2);
	}








	public function make_where($data, $or = false){

		if(is_array($data)){
			$where = '';
			foreach($data as $field => $value){
				$field = trim($field);

				$op = strpos($field, ' ') ? '' : '=';

				if(!strpos($where, 'WHERE') && !strpos($this->where, 'WHERE')){
					$where .= ' WHERE '.$field.' '.$op.' "'.$value.'" ';
				}else{
					$where .= ($or ? ' OR ' : ' AND ').$field.' '.$op.'"'.$value.'" ';
				}
			}
		}else{
			$where = ' WHERE '.$data;
		}

		return $where;
	}



	private function reset(){
		$this->select = '';
		$this->from   = '';
		$this->where  = '';
		$this->order  = '';
		$this->join   = '';
		$this->limit   = '';
		$this->sets = array();
	}


	private function assoc($result){
		while($row = $result->fetch_array(MYSQLI_ASSOC)){
			$rows[] = $row;
		}
		return $rows;
	}








	private function run($query){
		$result = $this->connection->query($query);

		$this->last_query = $query;

		if(!$result) $this->report_query_error($query);

		return $result;
	}



	private function report_query_error($query, $exit = false){
		if($this->debug){
			echo '<div><b>Query Error: </b>'.$query.'</div>';
			if($exit) exit;
		}
	}


	private function report_error($error, $exit = false){
		if($this->debug){
			echo '<div><b>Database Error: </b>'.$error.'</div>';
			if($exit) exit;
		}
	}




	# the way in
	public function filter($input){
		$output = $this->connection->real_escape_string($input);
		return $output;
	}

	# the way out
	public function sanitize($input){
		$output = htmlspecialchars($input);
		return $output;
	}



} #this is the ending of the database class