PHP Zend_Db_Select::query方法代码示例

 public function onStatistics($event)
 {
     $table = Engine_Api::_()->getItemTable('ynfeedback_idea');
     $select = new Zend_Db_Select($table->getAdapter());
     $select->from($table->info('name'), 'COUNT(*) AS count');
     $event->addResponse($select->query()->fetchColumn(0), 'feedback');
 }

 function onInstall()
 {
     // member homepage
     $db = $this->getDb();
     $select = new Zend_Db_Select($db);
     $select->from('engine4_core_pages')->where('name = ?', 'user_index_home')->limit(1);
     $page_id = $select->query()->fetchObject()->page_id;
     // Check if it's already been placed
     $select = new Zend_Db_Select($db);
     $select->from('engine4_core_content')->where('page_id = ?', $page_id)->where('type = ?', 'widget')->where('name = ?', 'social-connect.call-popup-invite');
     $info = $select->query()->fetch();
     if (empty($info)) {
         // container_id (will always be there)
         $select = new Zend_Db_Select($db);
         $select->from('engine4_core_content')->where('page_id = ?', $page_id)->where('type = ?', 'container')->limit(1);
         $container_id = $select->query()->fetchObject()->content_id;
         // middle_id (will always be there)
         $select = new Zend_Db_Select($db);
         $select->from('engine4_core_content')->where('parent_content_id = ?', $container_id)->where('type = ?', 'container')->where('name = ?', 'middle')->limit(1);
         $middle_id = $select->query()->fetchObject()->content_id;
         // tab on profile
         $db->insert('engine4_core_content', array('page_id' => $page_id, 'type' => 'widget', 'name' => 'social-connect.call-popup-invite', 'parent_content_id' => $middle_id, 'params' => '', 'order' => 999));
     }
     parent::onInstall();
 }

 public function indexAction()
 {
     // Users
     $userTable = Engine_Api::_()->getItemTable('user');
     $select = new Zend_Db_Select($userTable->getAdapter());
     $select->from($userTable->info('name'), 'COUNT(user_id) as count');
     $data = $select->query()->fetch();
     $this->view->userCount = (int) $data['count'];
     // Reports
     $reportTable = Engine_Api::_()->getDbtable('reports', 'core');
     $select = new Zend_Db_Select($reportTable->getAdapter());
     $select->from($reportTable->info('name'), 'COUNT(report_id) as count')->where('`read` = ?', 0);
     $data = $select->query()->fetch();
     $this->view->reportCount = (int) $data['count'];
     // Plugins
     $moduleTable = Engine_Api::_()->getDbtable('modules', 'core');
     $select = new Zend_Db_Select($moduleTable->getAdapter());
     $select->from($moduleTable->info('name'), 'COUNT(TRUE) as count')->where('type = ?', 'extra');
     $data = $select->query()->fetch();
     $this->view->pluginCount = (int) $data['count'];
     // Notifications
     // Hook-based
     $event = Engine_Hooks_Dispatcher::_()->callEvent('getAdminNotifications');
     $this->view->notifications = $event->getResponses();
     // Database-based
     $select = Engine_Api::_()->getDbtable('log', 'core')->select()->where('domain = ?', 'admin')->order('timestamp DESC');
     $this->view->paginator = $paginator = Zend_Paginator::factory($select);
     $paginator->setItemCountPerPage(4);
 }

 /**
  * Get full details for a given record
  *
  * @param array $where Conditions to build query
  *
  * @return false|array
  */
 public function fetchDetail(array $where)
 {
     foreach ($where as $field => $value) {
         if (array_key_exists($field, $this->_fields)) {
             $field = $this->_fields[$field]['field'];
         }
         $this->_select->where($field . '=?', $value);
     }
     $this->_select->reset(Zend_Db_Select::LIMIT_COUNT);
     $this->_select->reset(Zend_Db_Select::LIMIT_OFFSET);
     if ($this->_cache['enable'] == 1) {
         $hash = 'Bvb_Grid' . md5($this->_select->__toString());
         if (!($result = $this->_cache['instance']->load($hash))) {
             $final = $this->_select->query(Zend_Db::FETCH_ASSOC);
             $result = $final->fetchAll();
             $this->_cache['instance']->save($result, $hash, array($this->_cache['tag']));
         }
     } else {
         $final = $this->_select->query(Zend_Db::FETCH_ASSOC);
         $result = $final->fetchAll();
     }
     if (!isset($result[0])) {
         return false;
     }
     if (isset($result[0]['ZFG_GHOST'])) {
         unset($result[0]['ZFG_GHOST']);
     }
     return $result[0];
 }

 function getById($jobid)
 {
     // do Bacula ACLs
     Zend_Loader::loadClass('Job');
     $table = new Job();
     if (!$table->isJobIdExists($jobid)) {
         return FALSE;
     }
     $select = new Zend_Db_Select($this->db);
     switch ($this->db_adapter) {
         case 'PDO_SQLITE':
             // bug http://framework.zend.com/issues/browse/ZF-884
             $select->distinct();
             $select->from(array('l' => 'Log'), array('logid' => 'LogId', 'jobid' => 'JobId', 'LogTime' => 'Time', 'logtext' => 'LogText'));
             $select->where("JobId = ?", $jobid);
             $select->order(array('LogId', 'LogTime'));
             break;
         default:
             // mysql, postgresql
             $select->distinct();
             $select->from(array('l' => 'Log'), array('LogId', 'JobId', 'LogTime' => 'Time', 'LogText'));
             $select->where("JobId = ?", $jobid);
             $select->order(array('LogId', 'LogTime'));
     }
     //$sql = $select->__toString(); echo "<pre>$sql</pre>"; exit; // for !!!debug!!!
     $stmt = $select->query();
     return $stmt->fetchAll();
 }

 public function onStatistics($event)
 {
     $table = Engine_Api::_()->getDbTable('photos', 'album');
     $select = new Zend_Db_Select($table->getAdapter());
     $select->from($table->info('name'), 'COUNT(*) AS count');
     $event->addResponse($select->query()->fetchColumn(0), 'photo');
 }

 public function getTotal($type, $start = null, $end = null)
 {
     $select = new Zend_Db_Select($this->getAdapter());
     $select->from($this->info('name'), 'SUM(value) as sum')->where('type = ?', $type);
     // Can pass "today" into start
     switch ($start) {
         case 'day':
             $start = mktime(0, 0, 0, gmdate("n"), gmdate("j"), gmdate("Y"));
             $end = mktime(0, 0, 0, gmdate("n"), gmdate("j") + 1, gmdate("Y"));
             break;
         case 'week':
             $start = mktime(0, 0, 0, gmdate("n"), gmdate("j") - gmdate('N') + 1, gmdate("Y"));
             $end = mktime(0, 0, 0, gmdate("n"), gmdate("j") - gmdate('N') + 1 + 7, gmdate("Y"));
             break;
         case 'month':
             $start = mktime(0, 0, 0, gmdate("n"), gmdate("j"), gmdate("Y"));
             $end = mktime(0, 0, 0, gmdate("n") + 1, gmdate("j"), gmdate("Y"));
             break;
         case 'year':
             $start = mktime(0, 0, 0, gmdate("n"), gmdate("j"), gmdate("Y"));
             $end = mktime(0, 0, 0, gmdate("n"), gmdate("j"), gmdate("Y") + 1);
             break;
     }
     if (null !== $start) {
         $select->where('date >= ?', gmdate('Y-m-d', $start));
     }
     if (null !== $end) {
         $select->where('date < ?', gmdate('Y-m-d', $end));
     }
     $data = $select->query()->fetch();
     if (!isset($data['sum'])) {
         return 0;
     }
     return $data['sum'];
 }

 /**
  * Protected function that executes the built query if it needs to be
  *
  */
 protected function _execute()
 {
     if ($this->_needsExecute) {
         $this->_rowIndex = 0;
         $this->_statement = $this->_select->query();
         $this->_needsExecute = false;
     }
 }

 public function getPostIndex()
 {
     $table = $this->getTable();
     $select = new Zend_Db_Select($table->getAdapter());
     $select->from($table->info('name'), new Zend_Db_Expr('COUNT(post_id) as count'))->where('topic_id = ?', $this->topic_id)->where('post_id < ?', $this->getIdentity())->order('post_id ASC');
     $data = $select->query()->fetch();
     return (int) $data['count'];
 }

 /**
  * Fetch data from Db end return records subset, full count records, success = true
  * @param Zend_Db_Select $select
  * @return array
  */
 public function getData(Zend_Db_Select $select)
 {
     $adapter = new Zend_Paginator_Adapter_DbSelect($select);
     $result = array();
     $result['data'] = $select->query()->fetchAll();
     $result['total'] = $adapter->count();
     $result['success'] = true;
     return $result;
 }

 public function indexAction()
 {
     // Don't render this if not authorized
     $viewer = Engine_Api::_()->user()->getViewer();
     if (!Engine_Api::_()->core()->hasSubject()) {
         return $this->setNoRender();
     }
     // Don't render this if friendships are disabled
     if (!Engine_Api::_()->getApi('settings', 'core')->user_friends_eligible) {
         return $this->setNoRender();
     }
     // Get subject and check auth
     $subject = Engine_Api::_()->core()->getSubject('user');
     //if( !$subject->authorization()->isAllowed($viewer, 'view') ) {
     //  return $this->setNoRender();
     //}
     // If no viewer or viewer==subject, don't display
     if (!$viewer->getIdentity() || $viewer->isSelf($subject)) {
         return $this->setNoRender();
     }
     // Diff friends
     $friendsTable = Engine_Api::_()->getDbtable('membership', 'user');
     $friendsName = $friendsTable->info('name');
     // Mututal friends/followers mode
     //    $col1 = 'user_id';
     //    $col2 = 'resource_id';
     // Mututal friends/following mode
     $col1 = 'resource_id';
     $col2 = 'user_id';
     $select = new Zend_Db_Select($friendsTable->getAdapter());
     $select->from($friendsName, $col1)->join($friendsName, "`{$friendsName}`.`{$col1}`=`{$friendsName}_2`.{$col1}", null)->where("`{$friendsName}`.{$col2} = ?", $viewer->getIdentity())->where("`{$friendsName}_2`.{$col2} = ?", $subject->getIdentity())->where("`{$friendsName}`.active = ?", 1)->where("`{$friendsName}_2`.active = ?", 1);
     // Now get all common friends
     $uids = array();
     foreach ($select->query()->fetchAll() as $data) {
         $uids[] = $data[$col1];
     }
     // Do not render if nothing to show
     if (count($uids) <= 0) {
         return $this->setNoRender();
     }
     // Get paginator
     $usersTable = Engine_Api::_()->getItemTable('user');
     $select = $usersTable->select()->where('user_id IN(?)', $uids);
     $this->view->paginator = $paginator = Zend_Paginator::factory($select);
     // Set item count per page and current page number
     $paginator->setItemCountPerPage($this->_getParam('itemCountPerPage', 6));
     $paginator->setCurrentPageNumber($this->_getParam('page', 1));
     // Do not render if nothing to show
     if ($paginator->getTotalItemCount() <= 0) {
         return $this->setNoRender();
     }
     // Add count to title if configured
     if ($this->_getParam('titleCount', false) && $paginator->getTotalItemCount() > 0) {
         $this->_childCount = $paginator->getTotalItemCount();
     }
 }

 public function mailAction()
 {
     $this->view->form = $form = new Core_Form_Admin_Message_Mail();
     // let the level_ids be specified in GET string
     $level_ids = $this->_getParam('level_id', false);
     if (is_array($level_ids)) {
         $form->target->setValue($level_ids);
     }
     if (!$this->getRequest()->isPost()) {
         return;
     }
     if (!$form->isValid($this->getRequest()->getPost())) {
         return;
     }
     $values = $form->getValues();
     $table = Engine_Api::_()->getItemTable('user');
     $select = new Zend_Db_Select($table->getAdapter());
     $select->from($table->info('name'), 'email')->where('enabled = ?', true);
     // Do not email disabled members
     $level_ids = $this->_getParam('target');
     if (is_array($level_ids) && !empty($level_ids)) {
         $select->where('level_id IN (?)', $level_ids);
     }
     $emails = array();
     foreach ($select->query()->fetchAll(Zend_Db::FETCH_COLUMN, 0) as $email) {
         $emails[] = $email;
     }
     // temporarily enable queueing if requested
     $temporary_queueing = Engine_Api::_()->getApi('settings', 'core')->core_mail_queueing;
     if (isset($values['queueing']) && $values['queueing']) {
         Engine_Api::_()->getApi('settings', 'core')->core_mail_queueing = 1;
     }
     $mailApi = Engine_Api::_()->getApi('mail', 'core');
     $mail = $mailApi->create();
     $mail->setFrom($values['from_address'], $values['from_name'])->setSubject($values['subject'])->setBodyHtml(nl2br($values['body']));
     if (!empty($values['body_text'])) {
         $mail->setBodyText($values['body_text']);
     } else {
         $mail->setBodyText(strip_tags($values['body']));
     }
     foreach ($emails as $email) {
         $mail->addTo($email);
     }
     $mailApi->send($mail);
     $mailComplete = $mailApi->create();
     $mailComplete->addTo(Engine_Api::_()->user()->getViewer()->email)->setFrom($values['from_address'], $values['from_name'])->setSubject('Mailing Complete: ' . $values['subject'])->setBodyHtml('Your email blast to your members has completed.  Please note that, while the emails have been
     sent to the recipients\' mail server, there may be a delay in them actually receiving the email due to
     spam filtering systems, incoming mail throttling features, and other systems beyond SocialEngine\'s control.');
     $mailApi->send($mailComplete);
     // emails have been queued (or sent); re-set queueing value to original if changed
     if (isset($values['queueing']) && $values['queueing']) {
         Engine_Api::_()->getApi('settings', 'core')->core_mail_queueing = $temporary_queueing;
     }
     $this->view->form = null;
     $this->view->status = true;
 }

 public function indexAction()
 {
     $viewer = Engine_Api::_()->user()->getViewer();
     $params = $this->_getAllParams();
     if (!empty($params['itemCountPerPage'])) {
         $limit = $params['itemCountPerPage'];
     } else {
         $limit = 3;
     }
     // Don't render this if friendships are disabled
     if (!Engine_Api::_()->getApi('settings', 'core')->user_friends_eligible) {
         return $this->setNoRender();
     }
     if (!$viewer->getIdentity()) {
         return $this->setNoRender();
     }
     $tableUser = Engine_Api::_()->getItemTable('user');
     $select = $tableUser->select()->where('user_id <> ?', $viewer->getIdentity())->where('enabled = 1')->where('verified = 1')->where('approved = 1')->order(new Zend_Db_Expr('rand()'));
     $list_users = $tableUser->fetchAll($select);
     $list_show_users = array();
     foreach ($list_users as $subject) {
         if (count($list_show_users) >= $limit) {
             break;
         }
         // Diff friends
         $friendsTable = Engine_Api::_()->getDbtable('membership', 'user');
         $friendsName = $friendsTable->info('name');
         // Mututal friends/following mode
         $col1 = 'resource_id';
         $col2 = 'user_id';
         $select = new Zend_Db_Select($friendsTable->getAdapter());
         $select->from($friendsName, $col1)->join($friendsName, "`{$friendsName}`.`{$col1}`=`{$friendsName}_2`.{$col1}", null)->where("`{$friendsName}`.{$col2} = ?", $viewer->getIdentity())->where("`{$friendsName}_2`.{$col2} = ?", $subject->getIdentity())->where("`{$friendsName}`.active = ?", 1)->where("`{$friendsName}_2`.active = ?", 1);
         // Now get all common friends
         $uids = array();
         foreach ($select->query()->fetchAll() as $data) {
             $uids[] = $data[$col1];
         }
         // Do not render if nothing to show
         if (count($uids) > 0) {
             if (!$subject->membership()->isMember($viewer)) {
                 $list_show_users[] = array('user_id' => $subject->getIdentity(), 'number_mutual' => count($uids));
             }
         }
     }
     if (count($list_show_users) <= 0) {
         foreach ($list_users as $subject) {
             if (count($list_show_users) >= $limit) {
                 break;
             }
             if (!$subject->membership()->isMember($viewer)) {
                 $list_show_users[] = array('user_id' => $subject->getIdentity());
             }
         }
     }
     $this->view->list_show_users = $list_show_users;
 }

 function getClientId($client_name)
 {
     $select = new Zend_Db_Select($this->_db);
     $select->from('Client');
     $select->where("Name = ?", $client_name);
     $select->limit(1);
     $stmt = $select->query();
     $res = $stmt->fetch();
     return $res['clientid'];
 }

 /**
  * @return array $relatedEntries
  */
 private function _getRelatedCompaniesLinks()
 {
     if (!is_null($this->_relatedCache)) {
         return $this->_relatedCache;
     }
     $itemRow = $this->getData()->parent->row;
     $categoryDirectory = $this->_getCategoryDirectory();
     $connectTableName = Kwc_Abstract::getSetting($categoryDirectory->componentClass, 'categoryToItemModelName');
     $linkLimit = $this->_getSetting('linkLimit');
     $categoryReference = Kwc_Directories_Category_Detail_List_Component::getTableReferenceData($connectTableName, 'Category');
     $categoryIds = array();
     $categoryIdsRowset = $itemRow->getRow()->findDependentRowset($connectTableName);
     foreach ($categoryIdsRowset as $categoryIdRow) {
         $categoryIds[] = $categoryIdRow->{$categoryReference['itemColumn']};
     }
     if ($categoryIds) {
         $itemReference = Kwc_Directories_Category_Detail_List_Component::getTableReferenceData($connectTableName, 'Item');
         // anzahl holen
         $s = new Zend_Db_Select(Kwf_Registry::get('db'));
         $s->from($itemReference['tableName'], $itemReference['itemColumn']);
         $s->where($categoryReference['itemColumn'] . ' IN(' . implode(',', $categoryIds) . ')');
         $s->order($itemReference['tableName'] . '.id ASC');
         $rowset = $s->query()->fetchAll();
         // eigene position ermitteln und rauslöschen
         $selfPos = 0;
         $i = 0;
         foreach ($rowset as $k => $row) {
             if ($row[$itemReference['itemColumn']] == $itemRow->id) {
                 if (!$selfPos) {
                     $selfPos = $i;
                 }
                 unset($rowset[$k]);
             }
             $i++;
         }
         $rowset = array_values($rowset);
         $categoryRowCount = count($rowset);
         $itemIds = $ret = array();
         if ($categoryRowCount) {
             for ($i = 0; $i < $linkLimit; $i++) {
                 $offset = ($selfPos + floor($categoryRowCount / $linkLimit) * $i) % $categoryRowCount;
                 $itemIds[] = $rowset[$offset][$itemReference['itemColumn']];
             }
             $itemDirectory = $this->getData()->parent->parent;
             $select = $itemDirectory->getGenerator('detail')->select($itemDirectory);
             $select->where('id IN(' . implode(',', $itemIds) . ')');
             $ret = $itemDirectory->getChildComponents($select);
         }
         $this->_relatedCache = $ret;
         return $ret;
     }
     $this->_relatedCache = array();
     return array();
 }