<?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"> <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 ' ';
}
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) ? ' ' : $_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);
}
}