PHP Sql::prepareStatementForSqlObject方法代码示例

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

    /**
     * Returns the total number of rows in the result set.
     *
     * @return int
     */
    public function count()
    {
        if ($this->rowCount !== null) {
            return $this->rowCount;
        }

        $select = clone $this->select;
        $select->reset(Select::COLUMNS);
        $select->reset(Select::LIMIT);
        $select->reset(Select::OFFSET);
        $select->reset(Select::ORDER);
        $select->reset(Select::GROUP);

        // get join information, clear, and repopulate without columns
        $joins = $select->getRawState(Select::JOINS);
        $select->reset(Select::JOINS);
        foreach ($joins as $join) {
            $select->join($join['name'], $join['on'], array(), $join['type']);
        }

        $select->columns(array('c' => new Expression('COUNT(1)')));

        $statement = $this->sql->prepareStatementForSqlObject($select);
        $result    = $statement->execute();
        $row       = $result->current();

        $this->rowCount = $row['c'];

        return $this->rowCount;
    }

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

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

 /**
  * 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 indexAction()
 {
     date_default_timezone_set('Asia/Dubai');
     $auth = new AuthenticationService();
     $container = new Container('username');
     $sm = $this->getServiceLocator();
     $dba = $sm->get($container->adapter);
     $secsql = "SELECT * FROM section";
     $secstatement = $dba->query($secsql, array(5));
     $section = new ResultSet();
     $section->initialize($secstatement);
     $teasql = "SELECT * FROM teacher";
     $teastatement = $dba->query($teasql, array(5));
     $teacher = new ResultSet();
     $teacher->initialize($teastatement);
     if ($auth->hasIdentity() && $container->type == 0) {
         if ($this->getRequest()->getPost('submit-update')) {
             $count = (int) $this->getRequest()->getPost('rcount');
             for ($i = 1; $i <= $count; $i++) {
                 $id = $this->getRequest()->getPost('rid' . $i);
                 $att = $this->getRequest()->getPost('rattendance' . $i);
                 $com = $this->getRequest()->getPost('rcomment' . $i);
                 $stup = $this->getRequest()->getPost('status-update' . $i);
                 if ($stup != 0) {
                     $data = array('counted' => $att, 'comment' => $com, 'Abs_value' => $stup);
                     $sql = new Sql($dba);
                     $update = $sql->update();
                     $update->table('attendance');
                     $update->set($data);
                     $update->where(array('Att_id' => $id));
                     $statement = $sql->prepareStatementForSqlObject($update);
                     $statement->execute();
                 } else {
                     $id = $this->getRequest()->getPost('rid' . $i);
                     $sql = new Sql($dba);
                     $delete = $sql->delete('attendance')->where(array('Att_id' => $id));
                     $statement = $sql->prepareStatementForSqlObject($delete);
                     $statement->execute();
                 }
             }
             $sday = $this->getRequest()->getPost('sdate-filter');
             $eday = $this->getRequest()->getPost('edate-filter');
             $syour_date = date("Y-m-d", strtotime($sday));
             $eyour_date = date("Y-m-d", strtotime($eday));
             return new ViewModel(array('attendance' => $this->getRepport($syour_date, $eyour_date, $this->getRequest()->getPost('stid-filter'), $this->getRequest()->getPost('tea-filter'), $this->getRequest()->getPost('sec-filter'), $this->getRequest()->getPost('p-filter'), $this->getRequest()->getPost('status-filter')), 'sections' => $section, 'teachers' => $teacher, 'message' => "Attendance report updated", 'sday' => $sday, 'eday' => $eday, 'stid' => $this->getRequest()->getPost('stid-filter'), 'tid' => $this->getRequest()->getPost('tea-filter'), 'secid' => $this->getRequest()->getPost('sec-filter'), 'pid' => $this->getRequest()->getPost('p-filter'), 'status' => $this->getRequest()->getPost('status-filter'), 'filter' => "Attendance report for " . $this->getRequest()->getPost('stid-filter') . " from " . date("l jS \\of F Y ", strtotime($sday)) . " to " . date("l jS \\of F Y ", strtotime($eday))));
         } else {
             if ($this->getRequest()->getPost('submit-date')) {
                 $sday = $this->getRequest()->getPost('sdate-filter');
                 $eday = $this->getRequest()->getPost('edate-filter');
                 $syour_date = date("Y-m-d", strtotime($sday));
                 $eyour_date = date("Y-m-d", strtotime($eday));
                 return new ViewModel(array('attendance' => $this->getRepport($syour_date, $eyour_date, $this->getRequest()->getPost('stid-filter'), $this->getRequest()->getPost('tea-filter'), $this->getRequest()->getPost('sec-filter'), $this->getRequest()->getPost('p-filter'), $this->getRequest()->getPost('status-filter')), 'sections' => $section, 'teachers' => $teacher, 'message' => "", 'sday' => $sday, 'eday' => $eday, 'stid' => $this->getRequest()->getPost('stid-filter'), 'tid' => $this->getRequest()->getPost('tea-filter'), 'secid' => $this->getRequest()->getPost('sec-filter'), 'pid' => $this->getRequest()->getPost('p-filter'), 'status' => $this->getRequest()->getPost('status-filter'), 'filter' => "Attendance report for " . $this->getRequest()->getPost('stid-filter') . " from " . date("l jS \\of F Y ", strtotime($sday)) . " to " . date("l jS \\of F Y ", strtotime($eday))));
             } else {
                 return new ViewModel(array('attendance' => $this->getRepport(date('Y-m-d'), date('Y-m-d'), "", 0, 0, 0, 0), 'sections' => $section, 'teachers' => $teacher, 'message' => "", 'sday' => date('m/d/Y'), 'eday' => date('m/d/Y'), 'stid' => "", 'tid' => 0, 'secid' => 0, 'pid' => 0, 'status' => 0, 'filter' => "Attendance report for " . date("l jS \\of F Y ", strtotime(date('m/d/Y')))));
             }
         }
     } else {
         return $this->redirect()->toRoute('login', array('controller' => 'index', 'action' => 'login'));
     }
 }

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

 public function getRole($roleId)
 {
     $sql = new Sql($this->tableGateway->getAdapter());
     $select = $sql->select()->from(array('r' => 'role'))->where(array('r.id' => $roleId))->order('r.id');
     $stmt = $sql->prepareStatementForSqlObject($select);
     $results = $stmt->execute();
     return $results;
 }

 /**
  * Removes this record from the database
  */
 protected function delete()
 {
     if ($this->getId()) {
         $sql = new Sql(Database::getConnection(), $this->tablename);
         $delete = $sql->delete()->where(['id' => $this->getId()]);
         $sql->prepareStatementForSqlObject($delete)->execute();
     }
 }

 /**
  * Function for getting role permissions
  * 
  * @author Kaushal Kishore <kaushal.rahuljaiswal@gmail.com>
  * @return unknown
  */
 public function getRolePermissions()
 {
     $sql = new Sql($this->getAdapter());
     $select = $sql->select()->from(array('t1' => 'role'))->columns(array('role_name'))->join(array('t2' => $this->table), 't1.rid = t2.role_id', array(), 'left')->join(array('t3' => 'permission'), 't3.id = t2.permission_id', array('permission_name'), 'left')->join(array('t4' => 'resource'), 't4.id = t3.resource_id', array('resource_name'), 'left')->where('t3.permission_name is not null and t4.resource_name is not null')->order('t1.rid');
     $statement = $sql->prepareStatementForSqlObject($select);
     $result = $this->resultSetPrototype->initialize($statement->execute())->toArray();
     return $result;
 }

 public function findActiveDeals()
 {
     $sql = new \Zend\Db\Sql\Sql($this->getAdapter());
     $select = $sql->select()->from($this->tableName)->join('product', 'deal.product=product.productId')->where('DATE(startDate) <= DATE(NOW())')->where('DATE(endDate) >= DATE(NOW())')->where('stock > 0');
     $stmt = $sql->prepareStatementForSqlObject($select);
     $results = $stmt->execute();
     return $this->hydrate($results);
 }

 public function cleanPriorForgotRequests($userId)
 {
     $sql = new Sql($this->getDbAdapter(), $this->tableName);
     $delete = $sql->delete();
     $delete->where->equalTo($this->userField, $userId);
     $statement = $sql->prepareStatementForSqlObject($delete);
     $statement->execute();
     return true;
 }

 public function count($where = [])
 {
     $db = $this->getAdapter();
     $sql = new Sql\Sql($db);
     $select = $sql->select()->from($this->getTable())->columns(array('count' => new Sql\Expression('COUNT(*)')))->where($where);
     $statement = $sql->prepareStatementForSqlObject($select);
     $rowset = $statement->execute();
     return $rowset->current()['count'];
 }

 public function delete(PostInterface $postObject)
 {
     $action = new Delete($postObject);
     $action->where(array('id=?' => $postObject->getId()));
     $sql = new Sql($this->Adapter);
     $stmt = $sql->prepareStatementForSqlObject($action);
     $result = $stmt->execute();
     return (bool) $result->getAffectedRows();
 }