PHP Sql::select方法代码示例

 /**
  * @covers Zend\Db\Sql\Sql::select
  */
 public function testSelect()
 {
     $select = $this->sql->select();
     $this->assertInstanceOf('Zend\\Db\\Sql\\Select', $select);
     $this->assertSame('foo', $select->getRawState('table'));
     $this->setExpectedException('Zend\\Db\\Sql\\Exception\\InvalidArgumentException', 'This Sql object is intended to work with only the table "foo" provided at construction time.');
     $this->sql->select('bar');
 }

 public function xhbExists($xhbId)
 {
     try {
         $select = $this->_sql->select(Xhb::MAIN_TABLE);
         $select->columns(array('id'))->where(array('id' => $xhbId));
         $result = $this->_db->query($this->_sql->buildSqlString($select), Adapter::QUERY_MODE_EXECUTE);
     } catch (\RuntimeException $e) {
         return false;
     }
     return count($result) > 0;
 }

 public function getChiTietPhieuDoiTra($array_conditions = array(), $array_columns_1 = array(), $array_columns_2 = array(), $array_columns_3 = array(), $array_columns_4 = array(), $array_columns_5 = array(), $array_columns_6 = array(), $array_columns_7 = array())
 {
     /*
         chuyền vào 2 tham số:   1 tham số là mảng điều kiện, 
                                 1 tham số là mảng cột ở bảng 1 cần lấy ra,
                                 1 tham số là mảng cột ở bảng 2 cần lấy ra,
     */
     $adapter = $this->tableGateway->adapter;
     $sql = new Sql($adapter);
     // select
     $sqlSelect = $sql->select();
     $sqlSelect->from(array('t1' => 'phieu_doi_tra'));
     $sqlSelect->join(array('t2' => 'hoa_don'), 't1.id_hoa_don=t2.id_hoa_don', $array_columns_2, 'LEFT');
     $sqlSelect->join(array('t3' => 'ct_hoa_don'), 't2.id_hoa_don=t3.id_hoa_don', $array_columns_3, 'LEFT');
     $sqlSelect->join(array('t4' => 'san_pham'), 't3.id_san_pham=t4.id_san_pham', $array_columns_4, 'LEFT');
     $sqlSelect->join(array('t5' => 'khach_hang'), 't2.id_khach_hang=t5.id_khach_hang', $array_columns_5, 'LEFT');
     $sqlSelect->join(array('t6' => 'kenh_phan_phoi'), 't5.id_kenh_phan_phoi=t6.id_kenh_phan_phoi', $array_columns_6, 'LEFT');
     $sqlSelect->join(array('t7' => 'don_vi_tinh'), 't4.id_don_vi_tinh=t7.id_don_vi_tinh', $array_columns_7, 'LEFT');
     if ($array_columns_1) {
         $sqlSelect->columns($array_columns_1);
     }
     if ($array_conditions) {
         $sqlSelect->where($array_conditions);
     }
     $statement = $this->tableGateway->getSql()->prepareStatementForSqlObject($sqlSelect);
     $resultSets = $statement->execute();
     $allRow = array();
     foreach ($resultSets as $key => $resultSet) {
         $allRow[] = $resultSet;
     }
     return $allRow;
 }

 public function getPerTime($where_params = null, $is_super_admin = 0)
 {
     $obj_list = array();
     $sql = new Sql($this->adapter);
     $select = $sql->select();
     $select->from('publisherHourlyBidsBidsPerTime');
     if (!empty($where_params['DateCreatedGreater'])) {
         $select->where($select->where->greaterThanOrEqualTo('DateCreated', $where_params['DateCreatedGreater']));
     }
     if (!empty($where_params['DateCreatedLower'])) {
         $select->where($select->where->lessThanOrEqualTo('DateCreated', $where_params['DateCreatedLower']));
     }
     $statement = $sql->prepareStatementForSqlObject($select);
     $results = $statement->execute();
     foreach ($results as $obj) {
         if (!$is_super_admin) {
             array_walk($obj, function ($item, $key) use(&$obj) {
                 if (array_search($key, $this->adminFields) !== FALSE) {
                     $obj[$key] = FALSE;
                 }
             });
             $obj = array_filter($obj, function ($value) {
                 return $value !== FALSE;
             });
         }
         $obj['MDYH'] = $this->re_normalize_time($obj['MDYH']);
         $obj_list[] = $obj;
     }
     return $obj_list;
 }

 public function getPhieuThuAndUserByArrayConditionAnd2ArrayColumn($array_conditions = array(), $array_columns_1 = array(), $array_columns_2 = array())
 {
     /*
         chuyền vào 2 tham số:   1 tham số là mảng điều kiện, 
                                 1 tham số là mảng cột ở bảng 1 cần lấy ra,
                                 1 tham số là mảng cột ở bảng 2 cần lấy ra,
     */
     $adapter = $this->tableGateway->adapter;
     $sql = new Sql($adapter);
     // select
     $sqlSelect = $sql->select();
     $sqlSelect->from(array('t1' => 'phieu_thu'));
     $sqlSelect->join(array('t2' => 'user'), 't1.id_user=t2.user_id', $array_columns_2, 'LEFT');
     if ($array_columns_1) {
         $sqlSelect->columns($array_columns_1);
     }
     if ($array_conditions) {
         $sqlSelect->where($array_conditions);
     }
     $statement = $this->tableGateway->getSql()->prepareStatementForSqlObject($sqlSelect);
     $resultSets = $statement->execute();
     $allRow = array();
     foreach ($resultSets as $key => $resultSet) {
         $allRow[] = $resultSet;
     }
     return $allRow;
 }

 /**
  *
  * @return Select
  */
 public function select()
 {
     if ($this->select === null) {
         $this->select = $this->sql->select();
     }
     return $this->select;
 }

 /**
  * {@inheritdoc}
  */
 public function findUsersOfSite($siteId, $order = null, $paginated = false)
 {
     $sql = new Sql($this->dbAdapter);
     $membershipCols = array();
     foreach (DbSelectColumns::MEMBERSHIP as $col) {
         $membershipCols[DbViewMembership::TABLE . '_' . $col] = $col;
     }
     $activityCols = array();
     foreach (DbSelectColumns::USER_ACTIVITY as $col) {
         $activityCols[DbViewUserActivity::TABLE . '_' . $col] = $col;
     }
     $userCols = array();
     foreach (DbSelectColumns::USER as $col) {
         $userCols[DbViewUsers::TABLE . '_' . $col] = $col;
     }
     $select = $sql->select()->from(array('u' => DbViewUsers::TABLE))->columns($userCols)->join(array('a' => DbViewUserActivity::TABLE), 'u.' . DbViewUsers::USERID . ' = a.' . DbViewUserActivity::USERID, $activityCols)->join(array('m' => DbViewMembership::TABLE), 'm.' . DbViewMembership::USERID . ' = u.' . DbViewUsers::USERID . ' AND m.' . DbViewMembership::SITEID . ' = a.' . DbViewUserActivity::SITEID, $membershipCols, Select::JOIN_LEFT)->where(array('a.' . DbViewUserActivity::SITEID . ' = ?' => $siteId, '(m.' . DbViewMembership::JOINDATE . ' IS NOT NULL OR (a.' . DbViewUserActivity::VOTES . ' > 0) OR (a.' . DbViewUserActivity::REVISIONS . ' > 0) or (a.' . DbViewUserActivity::PAGES . ' > 0))'));
     if (is_array($order)) {
         $this->orderSelect($select, $order);
     }
     $resultSet = new \Zend\Db\ResultSet\HydratingResultSet($this->userSiteHydrator, $this->objectPrototype);
     if ($paginated) {
         $adapter = new \Zend\Paginator\Adapter\DbSelect($select, $this->dbAdapter, $resultSet);
         return new \Zend\Paginator\Paginator($adapter);
     }
     return $resultSet->initialize($this->fetch($sql, $select));
 }

 public function getUserByArrayConditionAndArrayColumn($array_conditions = array(), $array_columns = array())
 {
     /*
         chuyền vào 2 tham số:   1 tham số là mảng điều kiện, 
                                 1 tham số là mảng cột cần lấy ra
     */
     $adapter = $this->tableGateway->adapter;
     $sql = new Sql($adapter);
     // select
     $sqlSelect = $sql->select();
     $sqlSelect->from(array('t1' => 'user'));
     if ($array_columns) {
         $sqlSelect->columns($array_columns);
     }
     if ($array_conditions) {
         $sqlSelect->where($array_conditions);
     }
     $statement = $this->tableGateway->getSql()->prepareStatementForSqlObject($sqlSelect);
     $resultSets = $statement->execute();
     $allRow = array();
     foreach ($resultSets as $key => $resultSet) {
         $allRow[] = $resultSet;
     }
     return $allRow;
 }

 public function getUserRoles($where = array(), $columns = array(), $orderBy = '', $paging = false)
 {
     try {
         $sql = new Sql($this->getAdapter());
         $select = $sql->select()->from(array('sa' => $this->table));
         if (count($where) > 0) {
             $select->where($where);
         }
         $select->where($where);
         if (count($columns) > 0) {
             $select->columns($columns);
         }
         if (!empty($orderBy)) {
             $select->order($orderBy);
         }
         if ($paging) {
             $dbAdapter = new DbSelect($select, $this->getAdapter());
             $paginator = new Paginator($dbAdapter);
             return $paginator;
         } else {
             $statement = $sql->prepareStatementForSqlObject($select);
             $clients = $this->resultSetPrototype->initialize($statement->execute())->toArray();
             return $clients;
         }
     } catch (\Exception $e) {
         throw new \Exception($e->getPrevious()->getMessage());
     }
 }

 public function getCertificateUserAndCertificateByArrayConditionAndArrayColumns($array_conditions = array(), $array_columns_1 = array(), $array_columns_2 = array())
 {
     /*
         chuyền vào 3 tham số:   1 tham số là mảng điều kiện, 
                                 1 tham số là mảng cột bảng thứ nhất cần lấy ra,
                                 1 tham số là cột bảng thứ 2 cần lấy
     */
     $adapter = $this->tableGateway->adapter;
     $sql = new Sql($adapter);
     // select
     $sqlSelect = $sql->select();
     $sqlSelect->from(array('t1' => 'jos_certificate_user'));
     $sqlSelect->join(array('t2' => 'jos_certificate'), 't1.certificate_id=t2.value_id', $array_columns_2, 'LEFT');
     if ($array_columns_1) {
         $sqlSelect->columns($array_columns_1);
     }
     if ($array_conditions) {
         $sqlSelect->where($array_conditions);
     }
     $statement = $this->tableGateway->getSql()->prepareStatementForSqlObject($sqlSelect);
     $resultSets = $statement->execute();
     $allRow = array();
     foreach ($resultSets as $key => $resultSet) {
         $allRow[] = $resultSet;
     }
     return $allRow;
 }

 public function getConcertsByFilter($params)
 {
     $adapter = $this->tableGateway->getAdapter();
     $sql = new Sql($adapter);
     $select = $sql->select();
     $select->from(array('c' => 'concerts'), array('place', 'address', 'city', 'date_concert', 'cost', 'id_artist'))->join(array('a' => 'artists'), 'c.id_artist = a.id', array('name', 'image', 'tags'));
     // join table with alias
     if (count($params) == 5) {
         $select->where(array('city' => $params['city'], 'tags' => $params['tags'], 'cost' => $params['cost']))->where->between('date_concert', $params['date_start'], $params['date_end']);
         $statement = $sql->prepareStatementForSqlObject($select);
         $results = $statement->execute();
         return $results->current();
     } else {
         foreach ($params as $key => $value) {
             if ($key == 'date_start') {
                 $select->where('date_concert >= ' . $value);
             } elseif ($key == 'date_end') {
                 $select->where('date_concert <= ' . $value);
             } else {
                 $select->where(array($key => $value));
             }
         }
         $statement = $sql->prepareStatementForSqlObject($select);
         $results = $statement->execute();
         return $results;
     }
 }

 /**
  * @return array|PostInterface[]
  */
 public function findAll()
 {
     $sql = new Sql($this->dbAdapter);
     $select = $sql->select('posts');
     $stmt = $sql->prepareStatementForSqlObject($select);
     $result = $stmt->execute();
     //return $result;
     //\Zend\Debug\Debug::dump($result);die(); // 141
     //TTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTTT
     /*
     if ($result instanceof ResultInterface && $result->isQueryResult()) {
       $resultSet = new ResultSet();
     
       \Zend\Debug\Debug::dump($resultSet->initialize($result)); // 142
       die();
     }
     
     die("no data");
     */
     //LLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLLL
     if ($result instanceof ResultInterface && $result->isQueryResult()) {
         // 143
         $resultSet = new HydratingResultSet($this->hydrator, $this->postPrototype);
         return $resultSet->initialize($result);
         // 144
     }
     return array();
     // 145
 }

 function login($data, $adapter)
 {
     $sql = new Sql($adapter);
     $select = $sql->select()->from('users')->where(array('email' => $data['email'], 'password' => md5($data['password'])))->limit(1);
     $result = $adapter->query($sql->buildSqlString($select), $adapter::QUERY_MODE_EXECUTE);
     return $result;
 }

 public function getFollowers($profileID)
 {
     $sql = new Sql($this->tableGateway->adapter);
     $select = $sql->select()->from('follows')->where(array('id_user' => $profileID))->join('users', 'users.id = follows.id_follower');
     $statement = $this->tableGateway->getSql()->prepareStatementForSqlObject($select);
     $resultSet = $statement->execute();
     return $resultSet;
 }

 /**
  * This function returns the user by ID
  * @param int $userId
  * @return array
  */
 public function getUser($userId)
 {
     $sql = new Sql($this->tableGateway->getAdapter());
     $select = $sql->select()->from(array('u' => 'user'))->join(array('r' => 'role'), 'r.id = u.role_id', array('*'))->where(array('u.id' => $userId))->order('u.id');
     $stmt = $sql->prepareStatementForSqlObject($select);
     $results = $stmt->execute();
     return $results;
 }