Easy MySql Class (No more typing queries)

[bluegman991]

Well I do not know if it has been created yet nor have I checked if this has been done yet. I probably should have though because if it has I could have saved myself some time.

 

<?php
function contains_array(array $array)
{
foreach($array as $k => $v)
{
	if(is_array($v)) { return true; }
}
return false;
}
class Database
{
protected $host;
protected $database;
protected $user;
protected $pass;
protected $persistent;
public $connection_id;
public $last_query;
public $last_result;
public $num_queries;
public $start_time;
function __construct(array $settings)
{
	$this->host=@$settings['host'] or 'localhost';
	$this->user=@$settings['user'] or 'root';
	$this->pass=@$settings['pass'] or '';
	$this->database=@$settings['database'] or die('Database not defined!');
	$this->persistent=!empty($settings['persistent']) ? true : false;
	return true;
}

protected function Reset() //resets variables no longer required for after disconnection
{
	$this->connection_id=NULL;
	$this->last_query=NULL;
	$this->last_result=NULL;
	$this->num_queries=NULL;
	$this->start_time=NULL;
	return true;
}

function Escape($data) //escape strings going into database
{
	return mysql_real_escape_string($data);
}

function Connect()
{
	if($this->persistent)
	{
		$this->connection_id=mysql_pconnect($this->host,$this->user,$this->pass)
		or
		die('Persistent database connection failed on '.$this->host.' ('.mysql_error().')');
	}
	else
	{
		$this->connection_id=mysql_connect($this->host,$this->user,$this->pass,true)
		or
		die('Database connection failed on '.$this->host.' ('.mysql_error().')');
	}
	$this->Change_db($this->database);
	$time=explode(' ',microtime());
	$this->start_time=((float)$time[0]+(float)$time[1]); //Timestamp of when database was connected to
	return $this->connection_id;
}

function Disconnect()
{
	if($this->connection_id)
	{
		mysql_close($this->connection_id); //disconnects from database
		$this->Reset(); //see function for description
		return true;
	}
	else { return false; }
}

protected function Query_error($text='') //throws mysql query error (if detected) or ends page outputting $text
{
	return die("<pre>Database Query Error:\n".(empty($text) ? mysql_error($this->connection_id) : $text)."\nQuery: ".$this->last_query.'</pre>');;
}

protected static function Escape_callback($matches) //used with $this->Auto_escape function for preg_replaces_callback
{
	return $matches[1].mysql_real_escape_string($matches[2]).$matches[1];
}

protected function Auto_escape($data) //regexp finds any text that is in beginning and end quotes (ignores and captures escaped characters)
{
	return preg_replace_callback("/([\"'])((?:\\\\?+.)*?)\\1/",'self::Escape_callback',$data);
}

function Query($query)
{
	$this->num_queries++; //increments query count (logs number of queries executed)
	$this->last_query=$query; //logs last query in text form (after compiled)
	$this->last_result=mysql_query($this->last_query,$this->connection_id) or $this->Query_error(); //executes query or throws error on fail
	$return=array();
	if(@mysql_affected_rows($this->connection_id))//if rows are affected puts how many in $return array
	{
		$return['affected_rows']=mysql_affected_rows($this->connection_id);
	}
	if(is_numeric(@mysql_num_rows($this->last_result)))//if any rows are returned from select statement puts how many into $return array
	{
		$return['num_rows']=mysql_num_rows($this->last_result);
	}
	if(@mysql_insert_id($this->connection_id))//if insert id is detected puts it in $return array
	{
		$return['insert_id']=mysql_insert_id($this->connection_id);
	}
	$return['result']=$this->last_result;
	return $return;
}

function fetch_row($result)//fetch all rows returned from query
{
	return mysql_fetch_assoc($result);
}

private function dowhere1(array $where)//recusively loops through array to finalize where statement
{
	$where2=array();
	foreach($where as $k => $v)//loop through array
	{
		if(is_string($v) && ($v=='||' || strtolower($v)=='or')) //detects OR marker
		{
			$where2[]='OR';//inserts or marker into final where statement
		}
		elseif(is_array($v))
		{
			$where2[]=dowhere1($v);//runs this function on child array (for recusrsiveness)
		}
		else
		{
			if(!strpos($k,' '))//operator is found after space so check for space
			{
				return $this->Query_error('Error in where clause: Operator was not found!');//no space found = no operator found = throw error
			}
			elseif(substr_count($k,' ')>1)//check for: multiple spaces = multiple operators = throw error
			{
				return $this->Query_error('Error in where clause: Multiple operators found (Only 1 needed)');
			}
			else
			{
				$rop=array('=','!=','<','>','<=','>=','LIKE','LIKE%%','NOT-LIKE','NOT-LIKE%%','IS-NULL','IS-NOT-NULL','REGEXP','NOT-REGEXP');
				//^^operator list (Not all operators included) (add any more you need but replace spaces with dashes)
				$k=explode(' ',$k);//seperate by space
				$op=$k[1];//operator is after space
				$k=$k[0];//table name is before space
				if(!in_array($op,$rop))//validate operator
				{
					return $this->Query_error('Error in where clause:Invalid operator.');//throw error if operator is not found in operator list
				}
				if($op=='IS-NULL' || $op=='IS-NOT-NULL')//operator special treatment
				{
					$v=''; //empty $v because nothing is needed to match against when checking for null
					$op=' '.str_replace('-',' ',$op);//change "-" to space
				}
				elseif($op=='REGEXP' || $op=='NOT-REGEXP')//operator special treatment
				{
					$op=' '.str_replace('-',' ',$op).' ';//change "-" to space
				}
				else
				{
					$symb=$op=='LIKE%%' || $op=='NOT-LIKE%%' ? '%' : '';//operator special treatment
					$v=$symb.$this->Auto_escape($v).$symb;//run auto escape on value
					$op=' '.$op.' ';//surround operator with space
				}
				$op=str_replace(array('-','%'),array(' ',''),$op);//change "-" to space for remaining operators
				if($v[0]=='%' && $v[1]=="'")//check for %'string'%
				{
					$v[0]="'"; $v[1]='%';
					$v[strlen($v)-1]="'";
					$v[strlen($v)-2]='%';//replace %'string'% with '%string%'
				}
				if($v[0]=='%' && $v[1]=='"')//check for %"string"%
				{
					$v[0]='"'; $v[1]='%';
					$v[strlen($v)-1]='"';
					$v[strlen($v)-2]='%';//replace %"string"% with "%string%"
				}
				$v='`'.$k.'`'.$op.$v;//compiles where statement (`field` = value) 'field' is field selected, '=' is operator, 'value' is value being checked against
				$where2[]=$v;//put final where statement into final array
			}
		}
	}
	return $where2;//return final array
}

private function dowhere2(array $where)//convert final array into mysql where statement
{
	while(contains_array($where))
	{
		foreach($where as $k => $v)//loop through array
		{
			if(is_array($v))
			{
				$where[$k]=$this->dowhere2($v);//execute this function on child arrays (for recursiveness)
			}
		}
	}

	$return='(';//beginning of string
	$first=true;//true because first loop has not yet been executed
	$nextop=' AND ';//default operator
	foreach($where as $k => $v)
	{
		if(is_string($v) && ($v=='||' || strtolower($v)=='or'))//check for or marker
		{
			$nextop=' OR ';//set next operator to be 'or'
		}
		if($v !='OR')//check if this loop is not 'or' marker
		{
			$return.=($first==false ? $nextop : '').$v; $nextop=' AND ';//append where statement to final where string
		}
		$first=false;//set to false because first loop has already been executed
	}
	$return.=')';//end parenthesis
	return 'WHERE '.$return;//return WHERE plus <where statement>
}

private function dowhere(array $where)//make dowhere1 and dowhere2 1 function
{
	return $this->dowhere2($this->dowhere1($where));
}

function Update($tbl,array $set,array $where=array())
{
	$query='UPDATE `'.$tbl.'` SET ';
	$first=true;
	foreach($set as $k => $v)
	{
		$v=$this->Auto_escape($v);
		$query.=($first==true ? '' : ',').'`'.$k.'`='.$v;
		$first=false;
	}
	$where=$this->dowhere($where);
	$query.=' '.$where;
	return $this->Query($query);
}

function Select($tbl,$sel,array $where,array $order=array(),$limit='')
{
	$query='SELECT ';
	if(empty($sel)) { $this->Query_error('No fields selected.'); }//make sure you are selecting something
	elseif(is_string($sel)) { $query.=$sel=='*'? '*' : '`'.$sel.'`'; }//if selecting * ouput only * otherwise output `field`
	elseif(is_array($sel))//if selecting multiple fields
	{
		$first=true;
		foreach($sel as $k => $v)
		{
			$query.=($first==true ? '' : ',').'`'.$v.'`';//seperate fields selected by commas
			$first=false;
		}
	}
	$query.=' FROM `'.$tbl.'`';
	$where=$this->dowhere($where);
	$query.=' '.$where;
	if(!empty($order))
	{
		$first=true;
		foreach($order as $k => $v)//$k is field ordering by, $v is order (ASC or DESC)
		{
			$query.=($first==true ? ' ORDER BY ' : ',').'`'.$k.'`'.(empty($v) ? '' : ' '.$v);//appends order by if defined
			$first=false;
		}
	}
	if(!empty($limit)) { $query.=' LIMIT '.$limit; }//appends limit if defined
	return $this->Query($query);//executes query
}

function Insert($tbl,array $fields,array $values)
{
	$query='';
	$query.='INSERT INTO `'.$tbl.'`';
	if(!empty($fields))
	{
		$query.=' (';
		$first=true;
		foreach($fields as $k => $v)//loop and append each field intended to be inserted on
		{
			$query.=($first==true ? '' : ',').'`'.$v.'`';
			$first=false;
		}
		$query.=') ';
	}
	$query.='VALUES';
	$efirst=true;
	foreach($values as $ek => $ev)//loop through each insert
	{
		$first=true;
		$query.=($efirst==true ? '(' : ',(');//add parenthesis around each insert
		foreach($ev as $k => $v)//loop through each field value
		{
			$query.=($first==true ? '' : ',').$this->Auto_escape($v);//seperate each field value
			$first=false;
		}
		$efirst=false;
		$query.=')';//end insert parenthesis
	}
	return $this->Query($query);
}

function Delete($tbl,array $where)
{
	$query='DELETE FROM `'.$tbl.'`';
	$where=$this->dowhere($where);
	$query.=' '.$where;
	return $this->Query($query);
}

function Change_db($db)
{
	if(mysql_select_db($db,$this->connection_id))
	{
		$this->database=$db;
		return true;
	}
	else { return die('Failed to connect to database: '.$db); }
}

function __destruct()
{
	return $this->Disconnect();
}
}
?>

 

Sorry i'm not all that good at explaining things so if you don't understand what something truly does just ask here and i'll elaborate a bit more on it.

Usage

Database::Update($table,

---- $set=array($field => $value [, '<field>' => $value])

---- [, $where=array('<field> <operator>' => $value)]

);

$table is the mysql table you are updating.

$set must be an array containing:

---- <field> as the array index (The field to set)

---- $value as array value. (The value to set the field to)

$where is optional and must be an array containing:

---- A string as the index containing:

---- ---- <field> as the field you are checking against

---- ---- followed by 1 space

---- ---- followed by the operator to test the field against

---- $value as the array value to check against the field

 

Database::Delete($table

---- [, $where=array('<field> <operator>' => $value)]

);

$table is the mysql table you are updating.

$where is optional and must be an array containing:

---- A string as the index containing:

---- ---- <field> as the field you are checking against

---- ---- followed by 1 space

---- ---- followed by the operator to test the field against

---- $value as the array value to check against the field

 

Databse::Select($table,

---- $select=array('<field>' [, '<field>'])

---- [, $where=array('<field> <operator>' => $value)]

---- [, $order=array('<field>' => ('ASC' | 'DESC'))]

---- [, $limit='[<int1>,]<int2>']

);

$table is the mysql table you are updating.

$select must be an array containing the field to select as the array value (<field>)

$where is optional and must be an array containing:

---- A string as the index containing:

---- ---- <field> as the field you are checking against

---- ---- followed by 1 space

---- ---- followed by the operator to test the field against

---- $value as the array value to check against the field

$order must be an array containing:

---- <field> as the array key (what to order by)

---- Array value must be 'ASC' or 'DESC' (which direction to order by)

$limit must be a string containing:

---- <int1> as the row to start at

---- followed by space

---- <int2> as the maximum rows to retrieve

 

Database::Insert($table,

---- $fields=array('<field>' [, '<field>'])

---- $values=array(array() [,array()])

);

$table is the mysql table you are updating.

$fields must be an array containing:

---- the fields to be selected as each value

$values must be an array containing:

---- more arrays as each field you're inserting, containg:

---- ---- each value must be in order corresponding to the $fields array

^^ This function may be changed up a bit.

 

$where is optional and must be an array containing:

---- A string as the index containing:

---- ---- <field> as the field you are checking against

---- ---- followed by 1 space

---- ---- followed by the operator to test the field against

---- $value as the array value to check against the field

You by default the seperating operator is AND. You can change the operator by inserting an array entry with a string value of OR in between the desired where statements.

Where statements are parsed recursively so they can be infinitely contain child arrays. The purpose of this is so you can do more complex checks.

IDK how readable i'm going to be able to make the usage look.

Edited October 15, 2011 by bluegman991

[Nickson]

I'm quite sure it will be useful to some people. I'm curious myself to see how you've actually achieved it. A database connector, or the new trend, a class is rather important for a web site. I'd like to see it and maybe do a quick check, but I doubt I'll be using it at some point myself

[Lithium]

Quite similar the class used in CodeIgniter and the one in Zend Framework as well! Also Postgre as something like this actually :)

[bluegman991]

O.K. I'll post it up. Just going to have one more look over it and add in explaining comments.

Had a look at code igniter's mysql class from what I found you still had to type out queries. I did see Zend_DB though and can say mine is a bit different.

[rulerofzu]

The use of arrays for select is common place in more current php scripts Ive seen this used quite a bit.

[Neon]

I tried to take a screenshot of mine to show how my is setup. I dislike doing any "direct" queries so I tried to make a broken down wrapper for anything I'd possibly need to do.

[bluegman991]

First post updated with the class.

[Danny696]

Neon, yours looks very good, but bluegman, I would advise not using a connect and destuct function, and just using __constuct and __desctuct functions instead. There are some other things, but I'm not on my laptop.

[bluegman991]

Lol neon also has a connect, disconnect, __construct, and __destruct function.

[Neon]

My constructor initializes the database information from a readonly file. It then unsets the reference to that file and connects to the database. The deconstructor is blank. I believe phpEdit automatically put that in there when I built the class.

_connect() and _disconnect() are just some helping classes. I check _connect() on each query. Just in case the connection to the dB has been disconnected or went down. _disconnected is ran when I explicitly want to end the database connection (mainly on shutdown queries).

[Nickson]

There is nothing wrong with using a __construct, __destruct, connect and disconnect function all in the same class, it just depends on how you fill those in and for what reason they exist. I have done this before, and I've seen it in clean code from others as well...

[bluegman991]

This class is a bit un-organized. I rushed it out, and I was also using it to experiment with somethings. So i know some of the code is messed up, un-needed, or not being used. I will release a more organized and easier to use query compiler that supports joining. Could be out today could be out in a few days. I already have it done, I am just testing everything. So it just depends on what kind of errors I run into.

[bluegman991]

I have finished a cleaner class. What I have now is simply a query compiler (Does not come with DB Class). It is much more cleaner and more organized than this class. If anyone wants it you can msg me and I will send it over.

Examples:

$qry=new Query('(select | update | insert | delete)');
$qry->table('tbl1');//for joins: $qry->table(array('tbl1' [ ,'tbl2]...));    for table name variables $qry->table(array('varname' => 'tbl1' [ , 'varname2' => 'tbl2']));
$qry->selectFields('*|fieldName'); //for multiple fields $qry->selectFields(array('fieldName' [ , 'fieldName']...));
mysql_query($qry->Compile()); //to execute

 

You can see the rest of the usage when I give you the script