PHP Sql\Sql类代码示例

 public function saveItem($arrParam = null, $options = null)
 {
     if ($options['task'] == 'add') {
         $sqlObj = new \Zend\Db\Sql\Sql($this->adapter);
         $insertObj = $sqlObj->insert('service_account_register');
         $insertObj->values($arrParam);
         $sqlString = $sqlObj->getSqlStringForSqlObject($insertObj);
         $this->adapter->query($sqlString)->execute();
     }
     if ($options['task'] == 'edit') {
         $this->tableGateway->update($arrParam, array('id' => $arrParam['id']));
     }
     if ($options['task'] == 'multi-status') {
         if (!empty($arrParam)) {
             foreach ($arrParam['id'] as $key => $value) {
                 if ($arrParam['type'] == 'multi-active') {
                     $status = 1;
                 }
                 if ($arrParam['type'] == 'multi-in-active') {
                     $status = 0;
                 }
                 $data = array('id' => $value, 'status' => $status);
                 $this->tableGateway->update($data, array('id' => $value));
             }
         }
     }
 }

 public function find($params)
 {
     $sql = new Sql($this->getAdapter());
     $select = new Select();
     $select->from($params['from']);
     if (!empty($params['columns'])) {
         $select->columns($params['columns']);
     }
     foreach ($params['where'] as $where) {
         $select->where($where);
     }
     foreach ($params['joins'] as $join) {
         if (empty($join['columns'])) {
             $join['columns'] = Select::SQL_STAR;
         }
         if (empty($join['type'])) {
             $join['type'] = Select::JOIN_INNER;
         }
         $select->join($join['name'], $join['on'], $join['columns'], $join['type']);
     }
     $query = $sql->getSqlStringForSqlObject($select);
     $results = $this->adapter->query($query, Adapter::QUERY_MODE_EXECUTE);
     $data = $results->toArray();
     if (empty($data)) {
         return false;
     } else {
         if (count($data) == 1) {
             return $data[0];
         } else {
             return $data;
         }
     }
 }

 public function save(\Api\Entity\Post $post)
 {
     $hydrator = $this->getHydrator();
     $action = null;
     $postData = array('title' => $post->getTitle(), 'description' => $post->getDescription());
     if ($post->getId()) {
         $action = new Update('posts');
         $action->set($postData);
         $action->where(array('id = ?' => $post->getId()));
     } else {
         $postData['author_id'] = $post->getAuthorId();
         $action = new Insert('posts');
         $action->values($postData);
     }
     $sql = new Sql($this->getAdaptor());
     $statement = $sql->prepareStatementForSqlObject($action);
     $result = $statement->execute();
     if ($result instanceof ResultInterface) {
         if ($pk = $result->getGeneratedValue()) {
             $post->setId($pk);
         }
         return $this->getPost($post->getId());
     }
     throw new \Exception('something went wrong.Please try again later');
 }

 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;
 }

 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 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 onDispatch(MvcEvent $e)
 {
     if (!$e->getRequest() instanceof ConsoleRequest) {
         throw new RuntimeException('You can only use this action from a console!');
     }
     $table = new Ddl\CreateTable('queue_messages');
     $table->addColumn(new Ddl\Column\Integer('id', false, null, ['autoincrement' => true]));
     $table->addColumn(new Ddl\Column\Varchar('queue_name', 100));
     $table->addColumn(new Ddl\Column\Integer('status', false));
     $table->addColumn(new Ddl\Column\Varchar('options', 250));
     $table->addColumn(new Ddl\Column\Text('message', null, true));
     $table->addColumn(new Ddl\Column\Text('output', null, true));
     $table->addColumn(new Ddl\Column\Datetime('started_dt', true));
     $table->addColumn(new Ddl\Column\Datetime('finished_dt', true));
     $table->addColumn(new Ddl\Column\Datetime('created_dt', false));
     $table->addColumn(new Ddl\Column\Datetime('updated_dt', true));
     $table->addConstraint(new Ddl\Constraint\PrimaryKey('id'));
     $sql = new Sql($this->dbAdapter);
     try {
         $this->dbAdapter->query($sql->buildSqlString($table), DbAdapter::QUERY_MODE_EXECUTE);
     } catch (\Exception $e) {
         // currently there are no db-independent way to check if table exists
         // so we assume that table exists when we catch exception
     }
 }

 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;
     }
 }

 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 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());
     }
 }

 /**
  * {@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));
 }

 /**
  * Saves a user to a database
  *
  * @param array $user
  */
 public function saveUser(array $user)
 {
     $sql = new Sql($this->dbAdapter);
     $insert = $sql->insert('users');
     $insert->columns(array_keys($user))->values($user);
     $sql->prepareStatementForSqlObject($insert)->execute();
 }

 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 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;
 }

 protected function create(\Zend\Db\Sql\Ddl\CreateTable $dll)
 {
     return $this->generateCode(function () use($dll) {
         $adapter = $this->getTableGateway()->adapter;
         $sql = new Sql($adapter);
         $adapter->query($sql->getSqlStringForSqlObject($dll), $adapter::QUERY_MODE_EXECUTE);
     });
 }