PHP Database::sql2single方法代码示例

	function write() {
		global $current_user;
		/* @var $current_user CurrentUser */
		if (!$current_user->authorized)
			throw new Exception('Access denied');

		$id = isset(Request::$post['id']) ? Request::$post['id'] : 0;
		$id = max(0, (int) $id);
		$parent_id = isset(Request::$post['parent_id']) ? Request::$post['parent_id'] : false;
		$parent_id = max(0, (int) $parent_id);
		if (!$id)
			throw new Exception('Illegal id');

		$title = isset(Request::$post['title']) ? Request::$post['title'] : false;
		$description = isset(Request::$post['description']) ? Request::$post['description'] : false;


		if ($parent_id == $id)
			throw new Exception('Illegal parent');

		if ($parent_id) {
			$query = 'SELECT `id` FROM `series` WHERE `id`=' . $parent_id;
			if (!Database::sql2single($query))
				throw new Exception('No such parent');
		}

		if (!$title)
			throw new Exception('Empty title');

		$description = prepare_review($description);
		$title = prepare_review($title, '');

		$query = 'UPDATE `series` SET `id_parent`=' . $parent_id . ',`title`=' . Database::escape($title) . ', `description`=' . Database::escape($description) . ' WHERE `id`=' . $id;
		Database::query($query);
	}

    /**
     * сохраняем факт скачивания книги 
     * @param type $variables 
     */
    public function saveUserDownloads($user_id, $book_id, $time = false)
    {
        Database::query('START TRANSACTION');
        // for user
        $period = 24 * 60 * 60;
        // каждый день
        $time_normalized = $time ? $time : floor(time() / $period) * $period;
        // а не качал ли эту книгу юзер уже?
        $query = 'SELECT COUNT(1) FROM  `stat_user_download` WHERE 
			`id_user`=' . $user_id . ' AND
			`id_book`=' . $book_id . ' AND
			`time`>=' . $time_normalized . '';
        $cnt = Database::sql2single($query);
        if ($cnt) {
            // already have
            return;
        }
        $query = 'INSERT IGNORE INTO `stat_user_download` SET 
			`id_user`=' . $user_id . ', 
			`id_book`=' . $book_id . ',
			`time`=' . $time_normalized;
        Database::query($query);
        // for book stat
        $query = 'INSERT INTO `stat_book_download` SET 
			`id_book`=' . $book_id . ',
			`count` = 1,
			`time`=' . $time_normalized . ' ON DUPLICATE KEY UPDATE
			`count` = `count`+1';
        Database::query($query);
        // updating book download_count
        $query = 'UPDATE `book` SET `download_count`=`download_count`+1 WHERE `id`=' . $book_id;
        Database::query($query);
        // genres
        $book = Books::getInstance()->getByIdLoaded($book_id);
        /* @var $book Book */
        $genres = $book->getGenres();
        if (count($genres)) {
            foreach ($genres as $gid => $data) {
                $query = 'INSERT INTO `stat_genre_download` SET 
					`id_genre`=' . $gid . ',
					`count`=1,
					`time`=' . $time_normalized . ' ON DUPLICATE KEY UPDATE
					`count` = `count`+1';
                Database::query($query);
            }
        }
        // authors
        $authors = $book->getAuthors();
        if (count($authors)) {
            foreach ($authors as $id => $data) {
                $query = 'INSERT INTO `stat_author_download` SET 
					`id_author`=' . $data['id'] . ',
					`count`=1,
					`time`=' . $time_normalized . ' ON DUPLICATE KEY UPDATE
					`count` = `count`+1';
                Database::query($query);
            }
        }
        Database::query('COMMIT');
    }

 function getRightholders()
 {
     $cond = new Conditions();
     $per_page = 0;
     if (isset($this->params['per_page'])) {
         $per_page = (int) $this->params['per_page'];
     }
     $per_page = $per_page > 0 ? $per_page : 1;
     $pagingName = isset($this->params['paging_parameter_name']) ? $this->params['paging_parameter_name'] : 'p';
     $where = '';
     $order = 'ORDER BY `id` DESC ';
     $group_by = '';
     $query = 'SELECT COUNT(1) FROM `rightholders` ' . $where . ' ' . $group_by . '';
     $count = Database::sql2single($query);
     $cond->setPaging($count, $per_page, $pagingName);
     $limit = $cond->getLimit();
     $limit = ' LIMIT ' . $limit;
     $query = 'SELECT * FROM `rightholders`' . $where . ' ' . $group_by . ' ' . $order . ' ' . $limit;
     $data = Database::sql2array($query);
     foreach ($data as &$row) {
         $row['path'] = Config::need('www_path') . '/admin/rightholders/' . $row['id'];
     }
     $this->data['rightholders'] = $data;
     $this->data['rightholders']['title'] = 'Правообладатели';
     $this->data['rightholders']['count'] = $count;
     $this->data['conditions'] = $cond->getConditions();
 }

    function process()
    {
        global $current_user;
        /* @var $current_user CurrentUser */
        $current_user->can_throw('add_comments');
        /*
         [writemodule] => CommentsWriteModule
         [reply_to] => 1
         [doc_id] => 440
         [comment] => ghjkhjk
        */
        $document_id = max(0, (int) Request::post('doc_id'));
        $table = Request::post('table');
        $comment = Request::post('comment');
        $reply_to = max(0, (int) Request::post('reply_to'));
        Database::query('START TRANSACTION');
        $query = 'SELECT max(`id`) as `id` FROM `comments` WHERE `doc_id` = ' . $document_id . ' AND `table`=' . Database::escape($table) . '';
        $maxid = 1 + max(0, Database::sql2single($query));
        $query = 'INSERT INTO `comments` SET 
		`id`=' . $maxid . ',
		`table`=' . Database::escape($table) . ', 
		`comment`=' . Database::escape($comment) . ',
		`parent`=' . $reply_to . ',
		`doc_id`=' . $document_id . ',
		`id_author`=' . $current_user->id . ',
		`time`=' . time();
        Database::query($query);
        Database::query('COMMIT');
    }

 function getCityList()
 {
     $country = isset($_POST['country_id']) ? (int) $_POST['country_id'] : 1;
     $this->data['city_list'] = Database::sql2array('SELECT `id`,`name` FROM `lib_city` WHERE `country_id`=' . $country . ' LIMIT 1000', 'id');
     $this->data['country_id'] = $country;
     $this->data['city_id'] = Database::sql2single('SELECT `id` FROM `lib_city` WHERE `country_id`=' . $country . ' LIMIT 1');
 }

	function getCountBySQL($where = false) {
		if (isset($this->cachedCount[$where]))
			return $this->cachedCount[$where];
		$where = $where ? 'WHERE ' . $where : '';
		$query = 'SELECT COUNT(1) FROM ' . $this->Collection->tableName . ' ' . $where;
		$this->cachedCount[$where] = max(0, (int) Database::sql2single($query));
		return $this->cachedCount[$where];
	}

	public function getAvailableNickname($nickname, $additional = '') {
		$nickname = trim($nickname) . $additional;
		$query = 'SELECT `nickname` FROM `users` WHERE `nickname` LIKE \'' . $nickname . '\' LIMIT 1';
		$row = Database::sql2single($query);
		if ($row && $row['nickname']) {
			return $this->getAvailableNickname($nickname, $additional . rand(1, 99));
		}
		return $nickname;
	}

	function getNew() {
		$uid = Request::get(0);
		if ($uid != 'me') {
			if ($uid)
				$uid = Database::sql2single('SELECT `id` FROM `users` WHERE `nickname`=' . Database::escape($uid));
		}
		if($uid)
		XMLClass::$varNode->setAttribute('to', $uid);
		$this->data['message'] = array();
		$this->data['message']['thread_id'] = $this->thread_id;
	}

	function __construct($id = false, $data = false) {
		$this->loaded = false;
		if ($id && !is_numeric($id)) {
			$query = 'SELECT `id` FROM `users` WHERE `nickname`=' . Database::escape($id);
			$id = (int) Database::sql2single($query);
		}
		if ($id) {
			$this->id = max(0, $id);
		}
		if ($data)
			$this->load($data);
	}

 function getUserReview()
 {
     global $current_user;
     if (!$current_user->authorized) {
         return;
     }
     $res = MongoDatabase::getReviewEvent($current_user->id, $this->target_id);
     $this->data = $this->_item($res);
     $this->data['review']['target_id'] = $this->target_id;
     $this->data['review']['target_type'] = $this->target_type;
     $this->data['review']['rate'] = isset($this->data['review']['rate']) ? $this->data['review']['rate'] : Database::sql2single('SELECT `rate` FROM `book_rate` WHERE `id_book` =' . $this->target_id . ' AND `id_user`=' . $current_user->id);
 }

	function getUserReview() {
		global $current_user;
		if (!$current_user->authorized)
			return;
		$query = 'SELECT * FROM `reviews` WHERE `id_target`=' . $this->target_id . ' AND `target_type`=' . $this->target_type . ' AND `id_user`=' . $current_user->id;
		$res = Database::sql2array($query);
		$this->data = $this->_item($res);
		$this->data['review']['target_id'] = $this->target_id;
		$this->data['review']['target_type'] = $this->target_type;
		$this->data['review']['rate'] = isset($this->data['review']['rate']) ?
			$this->data['review']['rate'] :
			Database::sql2single('SELECT `rate` FROM `book_rate` WHERE `id_book` =' . $this->target_id . ' AND `id_user`=' . $current_user->id);
	}

 function add_album_relation()
 {
     $album_id = $_POST['album_id'];
     $nick = $_POST['nick'];
     $role = $_POST['role'];
     $user_id = Database::sql2single('SELECT `id` FROM `user` WHERE `nickname`=' . Database::escape($nick));
     Database::query('INSERT INTO `album_family` SET
         `album_id`=' . $album_id . ',
         `user_id`=' . $user_id . ',
         `family_role`=' . $role . ',
         `add_time`=' . time() . '
             ON DUPLICATE KEY UPDATE
          `family_role`=' . $role . '');
 }

 function getOne($for_editing = false)
 {
     if ($this->genre_id) {
         $query = 'SELECT * FROM `genre` WHERE `id`=' . Database::escape($this->genre_id);
     } else {
         $query = 'SELECT * FROM `genre` WHERE `name`=' . Database::escape($this->genre_name);
     }
     $data = Database::sql2row($query);
     if (!isset($data['name'])) {
         return;
     }
     $this->data['genre'] = array('name' => $data['name'], 'id' => $data['id'], 'id_parent' => $data['id_parent'], 'title' => $data['title'], 'description' => $data['description'], 'books_count' => $data['books_count'], 'path' => Config::need('www_path') . '/genres/' . $data['id'], 'path_edit' => Config::need('www_path') . '/genres/' . $data['id'] . '/edit');
     Request::pass('genre-title', $data['title']);
     if (!$data['id_parent']) {
         $this->data['genre']['subgenres'] = $this->getAll($data['id']);
         return;
     }
     if (!$for_editing) {
         $query = 'SELECT COUNT(1) FROM `book_genre` BG JOIN `book` B ON B.id = BG.id_book WHERE BG.id_genre = ' . $data['id'] . '';
         $count = Database::sql2single($query);
         $cond = new Conditions();
         $cond->setPaging($count, 20);
         $limit = $cond->getLimit();
         $this->data['conditions'] = $cond->getConditions();
         $query = 'SELECT `id_book` FROM `book_genre` BG JOIN `book` B ON B.id = BG.id_book WHERE BG.id_genre = ' . $data['id'] . ' ORDER BY B.mark DESC LIMIT ' . $limit;
         $bids = Database::sql2array($query, 'id_book');
         $books = Books::getInstance()->getByIdsLoaded(array_keys($bids));
         Books::getInstance()->LoadBookPersons(array_keys($bids));
         $aids = array();
         foreach ($books as $book) {
             $book = Books::getInstance()->getById($book->id);
             list($aid, $aname) = $book->getAuthor(1, 1, 1);
             // именно наш автор, если их там много
             $this->data['genre']['books'][$book->id] = $book->getListData();
             $aids[$aid] = $aid;
         }
         if (count($aids)) {
             $persons = Persons::getInstance()->getByIdsLoaded($aids);
             foreach ($persons as $person) {
                 $this->data['genre']['authors'][] = $person->getListData();
             }
         }
         $this->data['parent'] = array();
         if ($data['id_parent']) {
             $data = Database::sql2row('SELECT * FROM `genre` WHERE `id`=' . Database::escape($data['id_parent']));
             $this->data['genre']['parent'][] = array('name' => $data['name'], 'id' => $data['id'], 'id_parent' => $data['id_parent'], 'title' => $data['title'], 'description' => $data['description'], 'books_count' => $data['books_count'], 'path' => Config::need('www_path') . '/genres/' . $data['id'], 'path_edit' => Config::need('www_path') . '/genres/' . $data['id'] . '/edit');
         }
     }
 }

    public static function addCity($id_country, $name)
    {
        if (!$id_country) {
            return false;
        }
        $query = 'INSERT INTO `lib_city` SET `verified`=0, `name`=' . Database::escape($name) . ',`country_id`=' . (int) $id_country . '
			ON DUPLICATE KEY UPDATE `country_id`=' . (int) $id_country;
        Database::query($query);
        $id = Database::lastInsertId();
        if (!$id) {
            $id = Database::sql2single('SELECT id FROM `lib_city` WHERE `country_id`=' . (int) $id_country . '
				AND  `name`=' . Database::escape($name));
        }
        return $id;
    }

 function addComment()
 {
     global $current_user;
     $subscribe = false;
     if (isset(Request::$post['subscribe'])) {
         if (Request::$post['subscribe']) {
             $subscribe = true;
         }
     }
     if (!$current_user->id) {
         return;
     }
     $comment = isset(Request::$post['comment']) ? Request::$post['comment'] : false;
     $comment = trim(prepare_review($comment, '<em><i><strong><b><u><s>'));
     if (!$comment) {
         throw new Exception('comment body expected');
     }
     $post_id = Request::$post['id'];
     $data = array();
     if ($post_id) {
         if (isset(Request::$post['comment_id']) && ($comment_id = Request::$post['comment_id'])) {
             $data = MongoDatabase::addEventComment($post_id, $current_user->id, $comment, $comment_id);
             if ($data) {
                 Notify::notifyEventCommentAnswer($data['commenter_id'], $post_id, $data['comment_id']);
             }
         } else {
             $data = MongoDatabase::addEventComment($post_id, $current_user->id, $comment);
             if ($data) {
                 Notify::notifyEventComment($data['user_id'], $post_id, $data['comment_id']);
             }
         }
     }
     if ($data) {
         if ($subscribe) {
             // на своё и так и так подписаны
             if ($data['post']['user_id'] != $current_user->id) {
                 $query = 'SELECT `id` FROM `events` WHERE `mongoid`=' . Database::escape($post_id);
                 $intid = Database::sql2single($query);
                 if ($intid) {
                     /* @var $current_user User */
                     $current_user->setNotifyRule(UserNotify::UN_COMMENT_ANSWER, UserNotify::UNT_NOTIFY);
                     $current_user->save();
                     Notify::notifySubscribe($current_user->id, $intid);
                 }
             }
         }
     }
 }