data mapper

blog_mapper.php

<?php
class BlogMapper extends AbstractMapper
{
    protected $tableName = 'blog';
    protected $fieldIdName = 'blog_id';
    public function FindByName ($name)
    {
        $sql = sprintf('SELECT * FROM `blog` WHERE `blog_name` =%s LIMIT 1', $this->db->quot($name));
        $row = $this->db->sqlparse($sql);
        return ($row) ? $this->CreateFromRow($row[0]) : false;
    }
    public function FindLastBlogs ($limit)
    {
        $sql = sprintf('SELECT * FROM `blog` ORDER BY blog_id DESC LIMIT %d', $limit);
        return $this->FindCollection($sql);
    }
    public function CreateFromRow ($row)
    {
        $blog = new Blog();
        $blog->setId($row['blog_id']);
        $blog->setName($row['blog_name']);
        $blog->setInfo($row['blog_info']);
        $blog->setuserId($row['user_id']);
        $blog->setPhoto($row['blog_photo']);
        if(isset($row['total_grade'])) $blog->setRating($row['total_grade']);
        if(isset($row['cnt_users'])) $blog->setCountUsers($row['cnt_users']);
        return $blog;
    }
    
    function FindByUserId ($user_id)
    {
        $sql = sprintf('SELECT * FROM `blog` WHERE user_id = %d', $user_id);
        return $this->FindCollection($sql);
    }
    function FindByMemberId ($member_id)
    {
        $sql = sprintf('SELECT b.* FROM blog b, blog_member bm WHERE bm.`user_id` = %d AND b.blog_id=bm.blog_id', $member_id);
        return $this->FindCollection($sql);
    }
    public function FindCountByUserId($user_id)
    {
    	$count = 0;
    	$sql = sprintf('SELECT COUNT(*) AS `cnt` FROM `blog` WHERE user_id = %d', $user_id);
    	$result = $this->db->SQLParse($sql);
    	$count = $result ? $result[0]['cnt'] : 0;
    	
    	$sql = sprintf('SELECT COUNT(*) AS `cnt` FROM blog b, blog_member bm WHERE bm.`user_id` = %d AND b.blog_id=bm.blog_id', $user_id);
    	$result = $this->db->SQLParse($sql);
    	$count += $result ? $result[0]['cnt'] : 0;
    	return $count;
    }
    public function FindBlogRatingByBlogId($blog_id)
    {
        	$sql = sprintf("SELECT SUM(g.`grade_value`) AS `sum_grade`
							FROM grade g WHERE g.content_type = ".ContentTypes::get_by_name('blogpost')->getId()." AND g.content_id IN(
							SELECT b.blog_post_id FROM blog_post b WHERE b.blog_id = %d
							)",$blog_id);
    	$rs = $this->db->SQLParse($sql);
    	return ($rs) ? $rs[0]['sum_grade'] : 0;	
    }
    public function FindRating()
    {
    	$sql = 'SELECT SUM(g.grade_value) AS `sum_grade`, b.*
				FROM blog b
				LEFT JOIN blog_post bp ON (b.blog_id=bp.blog_id)
				LEFT JOIN grade g ON bp.blog_post_id = g.content_id AND g.content_type = ' . ContentTypes::get_by_name('blogpost')->getId() . ' 
				GROUP BY b.`blog_id`
				ORDER BY `sum_grade` DESC';
    	return $this->FindCollection($sql);
    }
    public function FindPopular()
    {
    	$sql ="SELECT COUNT(bm.user_id) AS `cnt_users`, b.* FROM blog b
				LEFT JOIN  blog_member bm ON b.blog_id=bm.blog_id
				GROUP BY b.`blog_id` ORDER BY `cnt_users` DESC";
    	return $this->FindCollection($sql);
    }
    public function FindBlogsByProductId ($product_id)
    {
        $tagMapper = new TagMapper();
        $tag_ids = $tagMapper->FindTagIdsByProductId($product_id);
        if (empty($tag_ids))
            return false;
        $percent = (int) count($tag_ids) * 0.6;
        $sql = sprintf("SELECT
                        COUNT(DISTINCT t.tag_id) AS `cnt`,
                        b.*
                        FROM blog b, blog_post bp, blog_tag bt, tag t
                        WHERE
                        t.tag_id IN(%s)
                        AND t.tag_id=bt.tag_id
                        AND bp.blog_post_id=bt.blog_post_id
                        AND b.blog_id=bp.blog_id
                        GROUP BY b.blog_id HAVING `cnt` >= %d ORDER BY `cnt` DESC", implode(',', $tag_ids), $percent);
        return $this->FindCollection($sql);
    }
    public function FindCountByProductId($product_id)
    {
        $tagMapper = new TagMapper();
        $tag_ids = $tagMapper->FindTagIdsByProductId($product_id);
        if (empty($tag_ids))
            return false;
        $percent = (int) count($tag_ids) * 0.6;
    	$sql = sprintf('SELECT COUNT(*) AS `blog_count` FROM (
						SELECT
						COUNT(DISTINCT t.tag_id) AS `cnt`
						FROM blog b, blog_post bp, blog_tag bt, tag t
						WHERE t.tag_id IN(%s) AND t.tag_id=bt.tag_id AND bp.blog_post_id=bt.blog_post_id AND b.blog_id=bp.blog_id
						GROUP BY b.blog_id HAVING `cnt` >= %d ORDER BY `cnt`) `t`', implode(',', $tag_ids), $percent);
    	
    	$result = $this->db->SQLParse($sql);
    	return ($result) ? $result[0]['blog_count'] : 0;
    }
    public function JoinUserToBlog ($user_id, $blog_id)
    {
        $data['user_id'] = $user_id;
        $data['blog_id'] = $blog_id;
        return $this->db->Insert('blog_member', $data);
    }
    public function LeaveUserByBlog ($user_id, $blog_id)
    {
        $where = sprintf('`user_id`=%d AND `blog_id`=%d', $user_id, $blog_id);
        return $this->db->delete('blog_member', $where);
    }
    protected function ImportData ($object)
    {
        $data = array();
        $data['blog_name'] = $this->db->quot($object->getName());
        $data['blog_info'] = $this->db->quot($object->getInfo());
        $data['user_id'] = $object->getUserId();
        $data['blog_photo'] = $this->db->quot($object->getPhoto());
        return $data;
    }
    public function FindMemberByBlogId ($id)
    {
        $sql = sprintf('SELECT `user_id` AS id FROM `blog_member` WHERE `blog_id` = %d', $id);
        $res = $this->db->SQLParse($sql);
        $data = array();
        if ($res) {
            foreach ($res as $rs)
                $data[] = $rs['id'];
        }
        return $data;
    }

//>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

    public function FindMemberByBlogIdLimit ($id, $limit)
    {
        $sql = sprintf('SELECT u.*
		FROM `blog_member` bm
		INNER JOIN `user` u ON bm.user_id = u.user_id
		WHERE `blog_id` = %d 
		LIMIT %d', $id, $limit);
        return $this->FindCollection($sql);
    }

//<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
}
?>

db.php

<?php
class Db_DataBase
{
	public $handle = null;
	private static $instance = NULL;
	protected $_bebug;
	private $count_query = 0;
	
	static public function instance()
	{
		if (self::$instance == NULL)
		{
			self::$instance = new self(); 
		}
		return self::$instance;
	}
	
	private function __construct() {}
	
	protected function connect()
	{
	    if ($this->handle) return $this->handle;
	    
	    $config = Registry::get('db_config');
		$this->_bebug = $config['debug'];
	    if ($this->_bebug == 2)
			self::DebugLog('[Start connect ' . date('d.m.Y H:i:s') . ' ]');
			
	    $this->handle = mysql_pconnect($config['host'], $config['user'], $config['password']);
	    $this->sql('SET NAMES utf8');
	    mysql_select_db($config['db_name'], $this->handle);
	    
	    return $this->handle;
	}
	
	public function close()
	{
		if ($this->_bebug == 2)
			self::DebugLog('[end connect ' . date('d.m.Y H:i:s') . ' ]' . "\n\n");
		//mysql_close($this->handle);
	}
	
	public function __destruct()
	{
	    if ($this->_bebug == 1) {
	        echo "<br/><b>Всего {$this->count_query} запросов.</b><br/>";
	        $this->count_query = 0;
	    }
	    
//		$this->close();
	}
	
	public function getLastId()
	{
		return mysql_insert_id($this->connect()) ? mysql_insert_id($this->connect()) : - 1;
	}
	
	public function quot( $value )
	{
	    /*if (is_numeric($value) && !is_float($value)) {
	        $value = (int)$value;
	    } else {*/
			if (is_array($value))
				foreach (array_keys($value) as $k)
					$value[$k] = "'" . mysql_real_escape_string($value[$k], $this->connect()) . "'";
			else
				$value = "'" . mysql_real_escape_string($value, $this->connect()) . "'";
	    /*}*/
		return $value;
	}
	
	public function getErrorNumber()
	{
		return mysql_errno($this->connect());
	}
	
	public function sql( $query )
	{
		if ($this->_bebug == 1) {
		    $this->count_query++;
			echo "<b>{$this->count_query}:</b> {$query}<br />";
		}
		if ($this->_bebug == 2)
			self::DebugLog($query);
		return mysql_query($query, $this->connect());
	}
	
	public function sqlParse( $query )
	{
		$res = $this->sql($query);
		if ($res)
		{
			while($row = mysql_fetch_assoc($res))
				$qresult[] = $row;
			mysql_free_result($res);
		}
		
		return isset($qresult) ? $qresult : null;
	}
	
	public function insert( $table, $items )
	{
		$f_sql = $v_sql = array ();
		
		foreach ($items as $f => $v) {
			$f_sql[] = '`' . $f . '`';
			$v_sql[] = $v;
		}
		$query = 'INSERT INTO `' . $table . '` (' . implode(',', $f_sql) . ') VALUES (' . implode(',', $v_sql) . ')';		
		$this->sql($query);
		return $this->getLastId();
	}
	
	public function replace( $table, $items )
	{
		$f_sql = $v_sql = array ();
		foreach ($items as $f => $v)
		{
			$f_sql[] = '`' . $f . '`';
			$v_sql[] = $v;
		}
		$query = 'REPLACE INTO `' . $table . '` (' . implode(',', $f_sql) . ') VALUES (' . implode(',', $v_sql) . ')';
		$this->sql($query);
		return $this->getLastId();
	}
	
	public function update( $table, $items, $where )
	{
		$sql = array ();
		foreach ($items as $f => $v)
			$sql[] = '`' . $f . '`=' . $v;
		$query = 'UPDATE `' . $table . '` SET ' . implode(',', $sql);
		if (! empty($where))
			$query .= ' WHERE ' . $where;
		return $this->sql($query);
	}
	
	public function delete( $table, $where )
	{
		$query = 'DELETE FROM `' . $table . '` WHERE ' . $where;
		return $this->sql($query);
	}
	
	private static function DebugLog( $str )
	{
		$file = ROOT_DIR . 'sql_tmp_' . date('d_m_Y') . '.txt';
		$handle = @fopen($file, 'a+');
		@fwrite($handle, $str . "\n");
		@fclose($handle);
	}
}
?>

mapper.php

<?php
abstract class AbstractMapper
{
    protected $db;
    protected $tableName;
    protected $fieldIdName;
    protected $pager;
    public function __construct ()
    {
        $this->db = Db_DataBase::instance();
        $this->RemovePager();
    }
    public function SetPager (Pager $pager)
    {
        $this->pager = $pager;
    }
    /**
     * Сбрасывает использование Pager.
     * Т.е. отказываемся от постраничного вывода.
     *
     */
    public function RemovePager ()
    {
        $this->pager = null;
    }
    public function getCount()
    {
    	$sql = sprintf('SELECT COUNT(*) AS `cnt` FROM `'.$this->tableName.'`');
    	$res = $this->db->SQLParse($sql);
    	return $res ? $res[0]['cnt'] : 0;
    }
    public function FindById ($id)
    {
        $sql = sprintf('SELECT * FROM `%s` WHERE %s = %d LIMIT 1', strtolower($this->tableName), $this->fieldIdName, $id);
        $row = $this->db->sqlparse($sql);
        return ($row) ? $this->CreateFromRow($row[0]) : false;
    }
    public function FindAll ()
    {
        $sql = sprintf('SELECT * FROM `%s`', strtolower($this->tableName));
        return $this->FindCollection($sql);
    }
    public function FindCollection ($sql)
    {
        $data = null;
        if ($this->pager) {
            $sql .= $this->pager->getLimitQuery();
            $sql = trim($sql);
            $sql = 'SELECT SQL_CALC_FOUND_ROWS ' . substr($sql, 6);
        }
        $row = $this->db->sqlparse($sql);
        if ($this->pager) {
            $this->Count();
            $this->RemovePager();
        }
        if ($row) {
            $data = array();
            foreach ($row as $item) {
                $data[] = $this->CreateFromRow($item);
            }
        }
        return $data;
    }
    public function Save ($object)
    {
        if ($object->getId() < 0) {
            $id = $this->Insert($object);
            $object->setId($id);
            return $id;
        } else {
            $this->Update($object);
            return $object->getId();
        }
    }
    public function Delete ($object)
    {
        $sql = sprintf('DELETE FROM `%s` WHERE `%s` = %d LIMIT 1', strtolower($this->tableName), $this->fieldIdName, $object->getId());
        return $this->db->sql($sql);
    }
	
    /**
     * Передача в пейджер числа найденных записей
     *
     * @param string $sql
     */
    protected function Count ()
    {
        $res = $this->db->sqlparse('SELECT FOUND_ROWS()  AS `cnt`');
        $this->pager->setCount(($res) ? $res[0]['cnt'] : 0);
    }
    protected function Insert ($object)
    {        
    	$data = $this->ImportData($object);
        return $this->db->insert(strtolower($this->tableName), $data);
    }
    protected function Update ($object)
    {
        $data = $this->ImportData($object);
        return $this->db->update(strtolower($this->tableName), $data, '`' . $this->fieldIdName . '` = ' . $object->getId());
    }
    abstract public function CreateFromRow ($row);
    abstract protected function ImportData ($object);
}
?>

model.php

<?php
class Model
{
    protected $id = - 1;
    public function __construct ()
    {}
    public function getId ()
    {
        return (int)$this->id;
    }
    public function setId ($id)
    {
        //Делаем ID инвариантом для модели
        if ($this->id == - 1)
            $this->id = $id;
    }
}