мой скриптик для работы с бд.. элементарный, но может будет полезным..
как пользоваться
извиняюсь за то что не пользуюсь тегом ПХП.. он глючит чегото..
<?php
/**
* bmDataBase
*
* @copyright 2008 slik
* @license http://www.gnu.org/licenses/gpl.html GNU General Public License
* @version $Id: ver 0.02 Wed Jul 02 17:05:06 UTC 2008 $
* @link http://dev.bmengine.net
* @author slik <slik.jay@gmail.com>
*/
// defines
define("BM_DB_TRANSACTION_START", 1);
define("BM_DB_TRANSACTION_END", 2);
define("BM_DB_TRANSACTION_RESET", 3);
define("BM_DB_SKIP", log(0));
define("BM_DB_RETURN_RESOURCE", 0);
define("BM_DB_RETURN_ARRAY", 1);
define("BM_DB_RETURN_LASTID", 2);
define("BM_DB_RETURN_ASSOCARRAY", 3);
define("BM_DB_RETURN_BOOL", 4);
class bmDataBase
{
private $ident_prefix = "";
private $link = null;
private $last_error;
private $logging = true;
private $query_logs;
private $statistics;
private $declimer_main = '#';
private $declimer_right = '}}';
private $declimer_left = '{{';
static private $instance = null;
/**
* Create the only object of this class
*
* @return bmDataBase
*/
static public function getInstance()
{
if (is_null(self::$instance)) self::$instance = new bmDataBase();
return self::$instance;
}
/**
* private methods
*/
private function __construct()
{
$this->query_logs = array();
$this->statistics = array();
$this->statistics['queries_count'] = 0;
}
private function __clone() {}
/**
* Connect to database using dns syntax
* mysql://user
assword@host
ort/database_name ...
* or array
* array (
* 'scheme' = > 'mysql', // only MySQL supported
* 'host' => 'host',
* 'port' => 'port', // if need
* 'user' => 'user',
* 'pass' => 'password',
* 'path' => 'database_name',
* 'ident_prefix' => 'ident_prefix' // if need (can use bmDataBase::setIdentPrefix)
* 'declimer_main' => 'declimer_main' // if need (can use bmDataBase::setMainDeclimer)
* 'declimer_left' => 'declimer_left' // if need (can use bmDataBase::setLeftDeclimer)
* 'declimer_right' => 'declimer_right' // if need (can use bmDataBase::setRightDeclimer)
* )
*
* @param string|array $dsn
* @return bool
*/
public function connect($dsn)
{
// Load database driver and create its instance.
$parsed = $this->parseDSN($dsn);
if (!$parsed)
return $this->_setLastError(-1, "Can't parse DNS string", "bmDataBase::connect");
// try to set ident prefix
if (isset($parsed['ident_prefix']))
$this->setIdentPrefix($parsed['ident_prefix']);
// try to set main declimer
if (isset($parsed['declimer_main']))
$this->setMainDeclimer($parsed['declimer_main']);
// try to set left declimer
if (isset($parsed['declimer_left']))
$this->setLeftDeclimer($parsed['declimer_left']);
// try to set right declimer
if (isset($parsed['declimer_right']))
$this->setRightDeclimer($parsed['declimer_right']);
if (!is_callable('mysql_connect'))
return $this->_setLastError(-1, "MySQL extension is not loaded", "mysql_connect");
$ok = $this->link = @mysql_connect(
$parsed['host'].(empty($parsed['port'])? "" : ":".$parsed['port']),
$parsed['user'],
$parsed['pass'],
true
);
$this->_resetLastError();
if (!$ok) return $this->_setLastError(-1, "Can't connect to database", "mysql_connect");
$parsed['path'] = strtr($parsed['path'], array('/' => ''));
$ok = @mysql_select_db($parsed['path'], $this->link);
if (!$ok) return $this->_setLastError(-1, "Can't select database '{$parsed['path']}'", "mysql_select_db");
return true;
}
/**
* parseDSN(mixed $dsn)
* Parse a data source name.
* See parse_url() for details.
*
* @return array
*/
protected function parseDSN($dsn)
{
// if argument is array
if (is_array($dsn))
{
if (array_key_exists('scheme', $dsn) &&
array_key_exists('host', $dsn) &&
array_key_exists('user', $dsn) &&
array_key_exists('pass', $dsn) &&
array_key_exists('path', $dsn))
return $dsn;
else
return null;
}
// if argument is string
$parsed = @parse_url(strval($dsn));
// argument empty..
if (!$parsed) return null;
// if used params
$params = null;
if (!empty($parsed['query']))
{
parse_str($parsed['query'], $params);
$parsed += $params;
}
// save dns string
$parsed['dsn'] = $dsn;
return $parsed;
}
/**
* set prefix for #_
*
* @param unknown_type $prefix
*/
public function setIdentPrefix($prefix)
{
$this->ident_prefix = strval($prefix);
}
/**
* set main declimer (default '#')
*
* @param string(1) $declimer
*/
public function setMainDeclimer($declimer)
{
$tmp = strval($declimer);
$this->declimer_main = $tmp[0];
}
/**
* set right declimer (default '{{')
*
* @param string(2) $declimer
*/
public function setRightDeclimer($declimer)
{
$this->declimer_right = substr(strval($declimer), 0, 2);
}
/**
* set left declimer (default '}}')
*
* @param string(2) $declimer
*/
public function setLeftDeclimer($declimer)
{
$this->declimer_right = substr(strval($declimer), 0, 2);
}
/**
* log sql queryes ?
*
* @param bool $log_it
*/
public function setLogging($log_it)
{
$this->logging = intval($log_it);
}
/**
* Return last error info
*
* @return array
*/
public function getLastError()
{
return $this->last_error;
}
/**
* return statistics
*
* @return array
*/
public function getStatistics()
{
return $this->statistics;
}
/**
* sql query using placeholders:
*
* #s - string: string
* #i - identificator: `indent`[, ...]
* #v - value 'value'[, ...]
* #a - assotiative array `key` = 'value'[, ...]
* #d - integer 10
* #f - float 0.0
* #n - string, but if empty, if 0 or if null, sets to NULL
* #_ - identification index for tables
*
* @param sting $sql[, ...]
* @return array
*/
public function query($sql)
{
$this->_resetLastError();
$args = func_get_args();
$sql = $this->_parseSql($args);
return $this->_query($sql, BM_DB_RETURN_RESOURCE);
}
/**
* return generated sql query
*
* @param string $sql[, ...]
* @return string
*/
public function query_debug($sql)
{
$args = func_get_args();
return $this->_parseSql($args);
}
/**
* sql query using placeholders (see bmDataBase::query)
*
* @param string $sql[, ...]
* @return int (last id)
*/
public function insert($sql)
{
$this->_resetLastError();
$args = func_get_args();
$sql = $this->_parseSql($args);
return $this->_query($sql, BM_DB_RETURN_LASTID);
}
/**
* sql query using placeholders (see bmDataBase::query)
*
* @param string $sql[, ...]
* @return array
*/
public function select($sql)
{
$this->_resetLastError();
$args = func_get_args();
$sql = $this->_parseSql($args);
return $this->_query($sql, BM_DB_RETURN_ARRAY);
}
public function update($sql)
{
$this->_resetLastError();
$args = func_get_args();
$sql = $this->_parseSql($args);
return $this->_query($sql, BM_DB_RETURN_BOOL);
}
/**
* sql query using placeholders (see bmDataBase::query)
*
* @param string $sql[, ...]
* @return assoc array
*/
public function selectRow($sql)
{
$this->_resetLastError();
$args = func_get_args();
$sql = $this->_parseSql($args);
return $this->_query($sql, BM_DB_RETURN_ASSOCARRAY);
}
/**
* you can use transactions
* actions:
* BM_DB_TRANSACTION_START - start transaction
* BM_DB_TRANSACTION_END - stop transaction (commit)
* BM_DB_TRANSACTION_RESET - reset transaction (rollback)
* @param int $action
*/
public function transaction($action)
{
switch ($action)
{
case BM_DB_TRANSACTION_START:
$this->query("BEGIN");
break;
case BM_DB_TRANSACTION_END:
$this->query("COMMIT");
break;
case BM_DB_TRANSACTION_RESET:
$this->query("ROLLBACK");
break;
}
}
protected function _setLastError($code, $msg, $query)
{
$info = array();
$context = "";
if (is_callable('debug_backtrace'))
{
$trace = debug_backtrace();
$context = empty($trace[1]['class'])?
$trace[1]['function']:
$trace[1]['class']."::".$trace[1]['function'];
$info['file'] = $trace[1]['file'];
$info['line'] = $trace[1]['line'];
$info['function'] = $trace[2]['function'];
$info['class'] = $trace[2]['class'];
}
$this->last_error = array('code' => $code,
'message' => $msg,
'query' => $query,
'context' => $context,
'info' => $info);
return false;
}
protected function _resetLastError()
{
$this->last_error = array();
}
protected function _logQuery($query)
{
if($this->logging)
$this->query_logs[] = $query;
}
protected function _escape($str, $is_ident = false)
{
if($is_ident)
return "`".str_replace("`", "``", strval($str))."`";
elseif(is_null($str))
return 'NULL';
else
return "'".mysql_real_escape_string(strval($str), $this->link)."'";
}
protected function _parseMacroses($sql)
{
$start = 0;
$macroses = array();
$macroses['placeholders'] = array();
$i = 0;
$p = 1;
while (($pos = strpos($sql, $this->declimer_left, $start)) !== false)
{
$macroses[$i]['begin'] = $pos;
$macroses[$i]['end'] = strpos($sql, $this->declimer_right, $pos+2) + 2;
$macroses[$i]['macros'] = substr($sql, $macroses[$i]['begin'], $macroses[$i]['end']-$macroses[$i]['begin']);
$chars = count_chars(substr($sql, $start, $macroses[$i]['begin']-$start), 0);
$macroses[$i]['placeholders_start'] = $chars[ord($this->declimer_main)];
$chars = count_chars($macroses[$i]['macros'], 0);
$macroses[$i]['placeholders_count'] = $chars[ord($this->declimer_main)];
$p += $macroses[$i]['placeholders_start'];
for ($j=0; $j<$macroses[$i]['placeholders_count']; $j++)
$macroses['placeholders'][$p++] = $i;
$start = $macroses[$i]['end'];
$i++;
}
return $macroses;
}
protected function _parseSql($args)
{
trim($args[0]);
// #_
$args[0] = strtr($args[0], array($this->declimer_main.'_' => $this->ident_prefix));
if(count($args) > 1)
{
$skip = array();
$macroses = $this->_parseMacroses($args[0]);
for($i=1, $cnt=count($args); $i<$cnt; $i++)
{
if($args[$i] === BM_DB_SKIP)
{
$skip[$macroses[$macroses['placeholders'][$i]]['macros']] = "";
foreach ($macroses['placeholders'] as $key => $val)
{
if($val == $macroses['placeholders'][$i])
{
unset($args[$key]);
}
}
}
}
$args = array_values($args);
$skip[$this->declimer_left] = " ";
$skip[$this->declimer_right] = " ";
$args[0] = strtr($args[0], $skip);
$sql = explode($this->declimer_main, $args[0]);
$main_sql = $sql[0];
for ($i=1, $cnt=count($sql); $i<$cnt; $i++)
{
$type = $sql[$i][0];
$sql[$i] = substr($sql[$i], 1);
if(!isset($args[$i]))
$args[$i] = null;
switch ($type)
{
case 's': // -------------------------------------
$main_sql .= strval($args[$i]);
break;
case 'i': // -------------------------------------
if (is_null($args[$i]))
return $this->_setLastError(-1, "Placeholder '#i' can't be 'NULL'", 'bmDataBase::query');
elseif (is_array($args[$i]))
{
foreach ($args[$i] as $val)
$main_sql .= $this->_escape($val, true).", ";
$main_sql = substr($main_sql, 0, -2);
}
else
$main_sql .= $this->_escape($args[$i], true);
break;
case 'v': // -------------------------------------
if (is_array($args[$i]))
{
foreach ($args[$i] as $val)
$main_sql .= $this->_escape($val).", ";
$main_sql = substr($main_sql, 0, -2);
}
else
$main_sql .= $this->_escape($args[$i]);
break;
case 'a': // -------------------------------------
if(is_array($args[$i]))
{
foreach ($args[$i] as $key => $val)
$main_sql .= $this->_escape($key, true)." = ".$this->_escape($val).", ";
$main_sql = substr($main_sql, 0, -2);
}
else
return $this->_setLastError(-1, "Placeholder '#a' must be array", "bmDataBase::query");
break;
/*
case '_': // -------------------------------------
$main_sql .= $this->ident_prefix;
array_unshift($args, 0);
break;*/
case 'd': // -------------------------------------
if (is_null($args[$i]))
$main_sql .= 'NULL';
else
$main_sql .= intval($args[$i]);
break;
case 'f': // -------------------------------------
if (is_null($args[$i]))
$main_sql .= 'NULL';
else
$main_sql .= floatval($args[$i]);
break;
case 'n': // -------------------------------------
if(empty($args[$i]) || is_null($args[$i]) || $args[$i] == 0)
$main_sql .= 'NULL';
else
$main_sql .= $this->_escape($args[$i]);
break;
default: // -------------------------------------
return $this->_setLastError(-1, "Unknown placeholder type '#".$type."'", 'bmDataBase::query');
break;
}
$main_sql .= $sql[$i];
}
if(isset($args[$i+1]))
$main_sql .= $sql[$i+1];
}
else
$main_sql = $args[0];
return $main_sql;
}
protected function _query($sql, $return_as)
{
// sql query
$resource = @mysql_query($sql);
$this->statistics['queries_count']++;
if(!$resource)
return $this->_setLastError(mysql_errno($this->link), mysql_error($this->link), $main_sql);
// parse resources
switch ($return_as)
{
case BM_DB_RETURN_RESOURCE:
$return_val = $resource;
break;
case BM_DB_RETURN_ARRAY:
$return_val = array();
while ($row = @mysql_fetch_assoc($resource))
$return_val[] = $row;
break;
case BM_DB_RETURN_LASTID:
$return_val = @mysql_insert_id($this->link);
break;
case BM_DB_RETURN_ASSOCARRAY:
$return_val = @mysql_fetch_assoc($resource);
break;
case BM_DB_RETURN_BOOL:
$return_val = true;
break;
}
$this->statistics['affected_rows'] = mysql_affected_rows($this->link);
return $return_val;
}
}
?>
Яваскрипт
Линейный вид
