Поработал только что над проектом, где ОРМ нет, написал груду функций на каждый SQL-запрос (выносил из контроллера обращение к БД). Производительность упала, количество ненужного кода сильно возросло. Стало грустно
/**
* Singleton DB class. Provides access to database MySQL
*
*/
class DB {
/**
* @var DB
*/
protected static $instance = null;
private $result;
private $query;
private $_fetch_row = false;
public $debug = 0;
public $queries = array();
private function __construct() {
self::connect();
if (isset($_REQUEST['showqueries']) && $_REQUEST['showqueries']) {
$this->debug = 1;
}
}
/**
* Returns instance of DB
* @return DB
*/
public static function getInstance() {
if (is_null(self::$instance))
{
self::$instance = new DB();
}
return self::$instance;
}
private function connect() {
$config = config::getInstance()->config_values;
mysql_select_db(
$config['db_local']['dbname'],
mysql_connect(
$config['db_local']['host'],
$config['db_local']['user'],
$config['db_local']['password']
)
);
}
public function query($query) {
$this->result = array();
$this->query = trim($query);
if ($this->debug) {
$time_start = microtime(true);
}
$result = mysql_query($this->query);
if ($this->debug) {
$time_run = microtime(true) - $time_start;
$this->queries[] = array('query' => $this->query, 'time' => $time_run);
}
if (!$result) {
throw new Exception($this->error());
}
if ($this->_fetch_row)
{
$this->result = mysql_fetch_row($result);
}
elseif (preg_match('/^select /i', $this->query) || preg_match('/^show /i', $this->query))
{
while ($this->result[] = mysql_fetch_assoc($result)) {}
}
return $result;
}
public function fetch_all($query = '') {
if ($query)
{
$this->query($query);
}
return $this->result;
}
public function fetch_row($query = '') {
if ($query)
{
$this->query($query);
}
return $this->result[0];
}
public function fetch_val($query = '') {
if ($query)
{
$this->_fetch_row = true;
$this->query($query);
$this->_fetch_row = false;
}
return $this->result[0];
}
public function result($index, $field) {
if ($index < 0 || $index >= sizeof($this->result)) {
throw new Exception('DB error: ' . $index . ': no such index');
}
return $this->result[$index][$field];
}
public function numrows() {
return sizeof($this->result);
}
public function error() {
return mysql_error();
}
public function insert_id() {
return mysql_insert_id();
}
/**
* Singleton
*/
protected function __clone() {
;
}
}include("lib/config.class.php");
include("lib/db.mysql.class.php");
$db = DB::getInstance();
//-------- SELECT ALL --------
$sql = "SELECT * FROM hotel LIMIT 10";
$rows = $db->fetch_all($sql);
if ($rows) {
echo '<hr />';
echo 'SELECT ALL: <br />';
foreach ($rows as $row) {
print_r($row);
}
}
echo '<hr />';
//-------- SELECT one row --------
$sql = "SELECT * FROM hotel LIMIT 10";
$row = $db->fetch_row($sql);
echo 'SELECT one row: <br />';
print_r($row);
echo '<hr />';
//-------- SELECT value --------
$sql = "SELECT hotel_id FROM hotel LIMIT 10";
$hotel_id = $db->fetch_val($sql);
echo 'SELECT one value: ' . $hotel_id;
echo '<hr />';
//-------- UPDATE --------
$sql = "UPDATE hotel SET hotel_id=3 WHERE hotel_id=3";
$result = $db->query($sql);
echo 'UPDATE: ' . $result;
echo '<hr />';
//-------- INSERT + LAST_INSERT_ID --------
$sql = "INSERT INTO hotel (hotel_id)
VALUES ('')";
$result = $db->query($sql);
echo 'INSERT + LAST_INSERT_ID: ' . $db->insert_id();
echo '<hr />';
//-------- DELETE --------
$sql = "DELETE FROM hotel WHERE hotel_id = " . $db->insert_id();
$result = $db->query($sql);
echo 'DELETE: ' . $result;
echo '<hr />';
//------- DEBUG, show count of queries, list of all queries --------
if ($db->debug && isset($db->queries)) {
$mysql_time = '';
echo '<hr noshade />';
foreach ($db->queries as $query) {
echo '<pre>' . $query['query'] . '</pre>run: ' . sprintf("%01.6f", $query['time']) . ' sec.<hr noshade />';
$mysql_time += $query['time'];
}
echo 'Total queries: <b>' . count($db->queries) . '</b> (' . sprintf("%01.6f", $mysql_time) . ' sec.)';
}Это вызов режима отладки прямо в строке браузера GET-запросом.
function query() {
$args = func_get_args();
$query = array_shift($args);
$query = str_replace("%s","'%s'",$query);
foreach ($args as $key => $val) {
$args[$key] = mysql_real_escape_string($val);
}
$query = vsprintf($query, $args);
}$sql = "DELETE FROM hotel WHERE hotel_id = %d";
$result = $db->query($sql, $db->insert_id());
$sql = "SELECT hotel_id FROM hotel WHERE name = %s";
$hotel_id = $db->fetch_val($sql,$_GET['name']);
//единственная проблема будет с лайком. надо будет делать так:
$sql = "SELECT hotel_id FROM hotel WHERE name = %s";
$name = '%'.$_GET['name'].'%';
$hotel_id = $db->fetch_val($sql,$name);то я и ответил
