Location: PHPKode > scripts > kisDB > kisdb/kisdb6.php
<?php

/**
 * @author Rizac Marius-Bogdan
 * @link http://www.kisphp.com/kisdb/
 * @name Keep It Simple DATABASE connection
 * @version 6.1
 * @license GNU
 */

/**
 * 
 * added safe() function
 * 
 */

/*
<style type="text/css">
table.debug_sql {
	font-size: 12px;
	border: 1px solid #999999;
}

table.debug_sql tr td,table.debug_sql tr th {
	border: 1px solid #dcdcdc;
}

table.debug_sql tr td table {
	border-top: 2px solid #555555;
}

table.debug_sql tr td table tr th {
	background: #daeafc;
}

table.debug_sql tr td table tr td,table.debug_sql tr td table tr th {
	border: 1px solid #cccccc;
	font-size: 12px;
}
</style>
*/


/**
 * 
 * send errors via email
 * 
 * 1 = send email
 * 0 = do not send email
 *
 */
define("REPORT_ERRORS", 1);

/**
 * activate = 1 | deactivate = 0 debuger
 *
 */
define("DEBUG", 1); // 1 = on | 0 = off

class sql_error
{
	/**
	 * array to keep errors
	 *
	 * @var string
	 */
	protected $errors = array();
	
	/**
	 * destination email where errors will be send to
	 *
	 * @var string
	 */
	protected $mail = 'hide@address.com';
	
	/**
	 * sitename
	 *
	 * @var string
	 */
	protected $site = 'SiteName';
	
	/**
	 * add errors to container
	 *
	 * @param string $er
	 */
	function add($er)
	{
		if (!empty($er)) $this->errors[] = $er;
	}
	
	/**
	 * If there is any database error, send an email to the webmaster
	 *
	 */
	function send()
	{
		if (count($this->errors) == 0) return;
		if (REPORT_ERRORS == 0) return;
		$msg = '<fieldset>
		<legend>Errors</legend>
		<pre>'.print_r($this->errors, true).'</pre>
		</fieldset>';
		// GET INFO
		if (count($_GET) > 0)
		{
		$msg .= '<fieldset>
		<legend>GET</legend>
		<pre>'.print_r($_GET, true).'</pre>		
		</fieldset>';
		}
		// POST INFO
		if (count($_POST) > 0)
		{
		$msg .= '<fieldset>
		<legend>POST</legend>
		<pre>'.print_r($_POST, true).'</pre>	
		</fieldset>';	
		}
		// COOKIE INFO
		if (count($_COOKIE) > 0)
		{
		$msg .= '<fieldset>
		<legend>COOKIE</legend>
		<pre>'.print_r($_COOKIE, true).'</pre>		
		</fieldset>';
		}
		// SERVER INFO
		if (count($_SERVER) > 0)
		{
		$msg .= '<fieldset>
		<legend>SERVER</legend>
		<pre>'.print_r($_SERVER, true).'</pre>		
		</fieldset>';
		}
		// set time
		$msg .= '<fieldset>
		<legend>TIME</legend>
		<pre>'.date("D, d-M-Y G:i:s").'</pre>		
		</fieldset>';
		// make this email to be in HTML format
		$headers  = 'MIME-Version: 1.0' . "\r\n";
		$headers .= 'Content-type: text/html; charset=UTF-8' . "\r\n";
		// send email
		@mail($this->mail, $this->site.' database errors', $msg, $headers);
	}
}


/**
 * Extract the result
 *
 */
class result
{
	/**
	 * resource id
	 *
	 * @var unknown_type
	 */
	public $res;

	/**
	 * Set the resource ID
	 *
	 * @param resource ID $a
	 */
	function __construct($a)
	{
		$this->res = $a;
	}

	/**
	 * Extracts the informations from database
	 *
	 * @param string $type [ a | r | o ]
	 * @return array
	 */
	function fetch($type='a')
	{
		$r = array();
		if ($type == 'r')
		{
			if ($r = @mysql_fetch_row($this->res))
			{
				return $r;
			}
		}
		else if($type == 'o')
		{
			if ($r = @mysql_fetch_object($this->res))
			{
				return $r;
			}
		}
		else
		{
			if ($r = @mysql_fetch_assoc($this->res))
			{
				return $r;
			}
		}
	}
	
	function affected()
	{
		return @mysql_affected_rows($this->res);
	}
	
	/**
	 * Returns how many rows have been returned by the last launched query
	 *
	 * @return integer
	 */
	function rows()
	{
		return @mysql_num_rows($this->res);
	}
}

/**
 * This database class is used to make a connection to database and work with database
 *
 */
class database
{
	/**
	 * Host name
	 *
	 * @var string
	 */
	private $sqlhost = DB_H;

	/**
	 * Database username
	 *
	 * @var string
	 */
	private $sqluser = DB_U;

	/**
	 * Database password
	 *
	 * @var string
	 */
	private $sqlpass = DB_P;

	/**
	 * Database name
	 *
	 * @var string
	 */
	private $sqldatabase = DB_D;

	/**
	 * Query log
	 *
	 * @var array
	 */
	private $query_log = array();

	/**
	 * connect to mysql engine and select the database
	 *
	 */
	function __construct()
	{
		$this->link = @mysql_pconnect($this->sqlhost, $this->sqluser, $this->sqlpass) or die(mysql_error());
		@mysql_select_db($this->sqldatabase, $this->link) or die(mysql_error());
		$this->err = new sql_error();
	}

	/**
	 * make an insert or an update into database
	 *
	 * @param string $table
	 * @param array $array
	 * @param string or integer $id
	 * @param field name $field
	 * @return nothing
	 */
	function save($table, $array, $id=false, $field='id')
	{
		$a = $this->fields_type($table);
		$_table = str_replace(":", ".", $this->secure(reset(explode(" ", $table))));
		if ($id != false)
		{
			$query = "UPDATE `".$_table."` SET ";
			$cond = array();
			foreach ($array as $k=>$v)
			{
				if (array_key_exists($k, $a))
				{
					if ( (eregi('int', $a[$k])) && (is_numeric($v)) )
					{
						$cond[] = " `".$k."` = ".$this->secure($v)." ";
					}
					else
					{
						$cond[] = " `".$k."` = '".$this->secure($v)."' ";
					}
				}
			}
			$query .= implode(", ", $cond);
			$query .= " WHERE `".$this->secure(reset(explode(" ", $field)))."` = ";
			if ( (eregi('int', $a[$field])) && (is_numeric($id)) )
			{
				$query .= $this->secure($id);
			}
			else
			{
				$query .= " '".$this->secure($id)."' ";
			}
			//echo $query; die();
			
			//execute update query
			$do = $this->execute($query);
			
			// if there was nothing modified, make an insert
			if ($this->info('affected') == 0)
			{
				$array[$field] = $id;
				$this->save($table, $array);
			}
		}
		else
		{
			$query = "INSERT INTO `".$_table."` ";
			$fields = array();
			$values = array();
			foreach ($array as $k=>$v)
			{
				if (array_key_exists($k, $a))
				{
					$fields[] = " `".$k."` ";
					$values[] = " '".$this->secure($v)."' ";
				}
			}
			$query .= " (".implode(", ", $fields).") VALUES (".implode(", ", $values).")";
			return $this->execute($query, false);
		}
	}

	/**
	 * Describe the table
	 *
	 * @param string $table
	 * @return array
	 */
	function fields_type($table)
	{
		$_table = $this->secure(reset(explode(" ", $table)));
		$a = $this->execute("DESCRIBE `".$_table."`");
		$r = array();
		while ($b = $a->fetch())
		{
			$type = explode("(", $b['Type']);
			$r[$b['Field']] = $type[0];
		}
		return $r;
	}

	/**
	 * Prepair a query to be launched for execution to database
	 *
	 * @param string $sql
	 * @param array $conditions
	 * @param boolean $return
	 * @return prepaired query
	 */
	function query($sql, $conditions=array(), $return=true)
	{
		if (empty($sql)) return;
		if ((is_array($conditions)) && (count($conditions) > 0))
		{
			$query = '';
			$_query = explode("?", $sql);
			$num = count($_query);
			if ($num > 0)
			{
				for ($i=0; $i<$num; $i++)
				{
					$query .= $_query[$i];
					if (array_key_exists($i, $conditions))
					{
						if (is_numeric($conditions[$i]))
						{
							$query .= $this->secure($conditions[$i]);
						}
						else
						{
							$query .= "'".$this->secure($conditions[$i])."'";
						}
					}
				}
			}
		}
		else
		{
			$query = $sql;
		}
		return $this->execute($query, $return);
	}

	/**
	 * store the name of the current page
	 *
	 * @param string ( __FILE__ ) $page
	 */
	function page($page=__FILE__)
	{
		$this->query_log[] = str_replace($_SERVER['DOCUMENT_ROOT'], '', $page);
	}

	/**
	 * prepairs an array that contains informations about executed queryes
	 *
	 * @param array $data
	 */
	function load($data=array())
	{
		$this->query_log[] = $data;
	}

	/**
	 * Show the launched queryes and informations about that queryes
	 *
	 */
	function debug()
	{
		if (DEBUG == 0)
		{
			if (intval(@$_GET['debug']) == 0)
			{
				return;
			}
		}
		
		if (count($this->query_log) > 0)
		{
			$i = 0;
			echo '<table border="0" class="debug_sql">';
			echo '<tr>
			<th>#</th>
			<th>Query</th>
			<th>Time</th>
			</tr>';
			foreach ($this->query_log as $res)
			{
				++$i;
				if (count($res) == 1)
				{
					echo '<tr>';
					echo '<th>'.$i.'</th>';
					echo '<td colspan="2" bgcolor="#d2e1d3">&nbsp;<span style="color: navy; font-family: verdana; font-weight: bold;">Queries located in ::</span> <span style="color: #e72b8d; font-family: verdana;">'.$res.'</span></td>';
					echo '</tr>';
				}
				else
				{
					echo '<tr>';
					echo '<th rowspan="2">'.$i.'</th>';
					echo '<td class="td_query">'.$res['query'].'</td>';
					echo '<td rowspan="2">'.round($res['time'],5).'</td>';
					echo '</tr>';
					echo '<tr>';
					if (!empty($res['error']))
					{
						echo '<td><span style="color: #ff0000;">';
						echo $res['error'];
						echo '</span></td>';
					}
					else
					{
						echo '<td>';
						//echo '<pre>'.print_r(debug_backtrace(), true).'</pre>';
						if (ereg('SHOW|DESCRIBE|INSERT|UPDATE|DELETE|REPLACE', strtoupper($res['query'])) == true)
						{
							//echo '&nbsp;';
						}
						else
						{
							echo '<table cellpadding="1" cellspacing="1" width="100%">';
							echo '<tr><th>Id</th><th>Select Type</th><th>Table</th><th>Type</th><th>Possible Keys</th>
					<th>Key</th><th>Key Len</th><th>Ref</th><th>Rows</th><th>Extra</th></tr>';
							$_a = @mysql_query("EXPLAIN ".$res['query']) or die("LINE :: ".__LINE__."<br />FILE :: ".__FILE__."<br />ERROR :: ".mysql_error());
							while ($_b = @mysql_fetch_row($_a))
							{
								echo '<tr>';
								foreach ($_b as $_c)
								{
									echo '<td>'.(empty($_c) ? '&nbsp;' : $_c).'</td>';
								}
								echo '</tr>';
							}
							echo '</table>';
						}
						echo '</td>';
					}
					echo '</tr>';
				}
			}
			echo '<tr>';
			echo '<td colspan="3">Memory used :: '.round(memory_get_usage()/(1024*1024), 2).' MB</td>';
			echo '</tr>';
			echo '</table>';
		}
	}

	/**
	 * Executes the query to the database and returns the result
	 *
	 * @param string $sql
	 * @param boolean $return
	 * @return request resource
	 */
	function execute($sql, $return=true)
	{
		$start = microtime();
		$a = mysql_query($sql, $this->link) or $data['error'] = mysql_error();
		$data['query'] = $sql;
		$data['time'] = microtime()-$start;
		if (!empty($data['error']))
		{
			$_er = array(
				'sql'=>$sql,
				'err'=>mysql_error()
			);
			$this->err->add($_er);
		}
		$this->load($data);
		if ($return === true)
		{
			$res = new result($a);
			return $res;
		}
	}

	/**
	 * Return only the first word from the inserted string
	 *
	 * @param string $a
	 * @return string
	 */
	function safe($a)
	{
		return reset(explode(" ", $a));
	}
	
	/**
	 * secures the inserted data
	 *
	 * @param string or array $str
	 * @return safe string
	 */
	function secure($str)
	{
		if (is_array($str))
		{
			foreach($str as $key => $val)
			{
				$str[$key] = $this->secure($val);
			}

			return $str;
		}

		if (function_exists('mysql_real_escape_string') AND is_resource($this->link))
		{
			return mysql_real_escape_string($str, $this->link);
		}
		elseif (function_exists('mysql_escape_string'))
		{
			return mysql_escape_string($str);
		}
		else
		{
			return addslashes($str);
		}
	}

	/**
	 * Gets informations about the queryes
	 *
	 * @param unknown_type $type
	 * @return unknown
	 */
	function info($type='')
	{
		switch ($type)
		{
			case "affected":
				return @mysql_affected_rows($this->link);
				break;
			case "id":
			case "last id":
			case "insertid":
			case "insert id":
				return @mysql_insert_id($this->link);
				break;
			case "all":
				$a['id_inserted'] = @mysql_insert_id($this->link);
				$a['affected_rows'] = @mysql_affected_rows($this->link);
				return $a;
				break;
			default:

				break;
		}
	}

	/**
	 * Deletes records from table
	 *
	 * @param string $table | table_name or database_name:table_name
	 * @param integer|string $id
	 * @param string $field
	 * @param integer $limit
	 * @return none
	 */
	function delete($table, $id, $field='id', $limit=0)
	{
		$_table = str_replace(":", "`.`", reset(explode(" ", $table)));
		$_id = abs(intval(reset(explode(" ", $id))));
		if (is_numeric($field))
		{
			$_limit = abs(intval($field));
			$_field = 'id';
		}
		else
		{
			$_field = reset(explode(" ", $field));
		}
		$_limit = $_limit = abs(intval($limit));
		
		$query = "DELETE FROM `".$_table."` WHERE `".$_field."` = ".$_id;
		if ($_limit > 0)
		{
			$query .= " LIMIT ".$_limit;
		}
		return $this->execute($query, false);
	}

	/**
	 * Returns only a value from the table
	 *
	 * @param string $sql
	 * @param array $conditions
	 * @return string or integer (value from database)
	 */
	function get_value($sql, $conditions='')
	{
		$a = $this->query($sql, $conditions);
		$b = $a->fetch('r');
		return $b[0];
	}
	
	/**
	 * returns an array usually used to selects (id and name)
	 *
	 * @param string $sql
	 * @param array $conditions
	 * @return array
	 */
	function get_pairs($sql, $conditions='')
	{
		if (empty($sql)) return;
		if ((is_array($conditions)) && (count($conditions) > 0))
		{
			$query = '';
			$_query = explode("?", $sql);
			$num = count($_query);
			if ($num > 0)
			{
				for ($i=0; $i<$num; $i++)
				{
					$query .= $_query[$i];
					if (array_key_exists($i, $conditions))
					{
						if (is_numeric($conditions[$i]))
						{
							$query .= $this->secure($conditions[$i]);
						}
						else 
						{
							$query .= "'".$this->secure($conditions[$i])."'";
						}
					}
				}
			}
		}
		else
		{
			$query = $sql;
		}
		$row = array();
		$a = $this->execute($query);
		while ($b = $a->fetch('r'))
		{
			$row[$b[0]] = $b[1];
		}
		return $row;
	}
	
	/**
	 * returns a row from a table
	 *
	 * @param string $table
	 * @param (int|string) $id
	 * @param string $id_name
	 * @return result
	 */
	function get_row($table, $id, $id_name='id')
	{
		$query = "SELECT * FROM `".$this->safe($table)."` WHERE `".$this->safe($id_name)."` = ".$this->safe($id);
		$a = $this->query($query);
		return $a->fetch();
	}
	
	/**
	 * returns how many rows there is in a 
	 *
	 * @param string $table
	 * @param [integer|string] $id
	 * @param string $id_name
	 * @return integer
	 */
	function get_count($table, $id, $field='id')
	{
		$_table = str_replace(":", "`.`", reset(explode(" ", $table)));
		$_id = (is_numeric($id)) ? ' = '.$this->secure($id) : " LIKE '".$this->secure($id)."'";
		$_field = reset(explode(" ", $field));
		$query = "SELECT COUNT(*) FROM `".$_table."` WHERE `".$_field."` ".$_id;
		$a = $this->query($query);
		$b = $a->fetch('r');
		return intval($b[0]);
	}

	/**
	 * Close database connection
	 *
	 */
	function __destruct()
	{
		$this->err->send();
		@mysql_close($this->link);
	}
}
Return current item: kisDB