PHP AdapterInterface::query方法代码示例

 public function getNewRFollowID()
 {
     $sql = "select max(rfollowID) from rfollow";
     //         echo $sql;
     $statement = $this->dbAdapter->query($sql);
     $result = $statement->execute();
     return $result->current()['max(rfollowID)'] + 1;
 }

 public function getRandomUser($used = 1)
 {
     $usedCount = $this->getUnusedCount();
     if (intval($usedCount) == 0) {
         return false;
     }
     $userData = $this->getOrderByRand($this->table, array("is_used" => 0), 1);
     if (!$userData) {
         return false;
     }
     $uid = $userData[0]["id"];
     $where = array("id" => $uid);
     $connection = $this->dbAdapter->getDriver()->getConnection();
     $connection->beginTransaction();
     $sql = "select * from " . $this->table . "  where id =? for update";
     $result = $this->dbAdapter->query($sql, array($uid));
     $userInfo = $result->toArray();
     //        $updateData = array("is_used" => $used, "used_time" => time());
     $updateData = array("is_used" => $used);
     if ($userInfo[0]["is_used"] == 0) {
         $this->updateData($this->table, $where, $updateData);
         $connection->commit();
         if (!empty($userInfo)) {
             return $userInfo[0];
         }
     } else {
         $connection->commit();
         return $this->getRandomUser($used);
     }
 }

 public function industries(AdapterInterface $adapter)
 {
     $sql = "SELECT industries_id,industry_name from industries where status=1 ORDER by xorder ASC";
     $statement = $adapter->query($sql);
     $result = $statement->execute();
     return $result;
 }

 public function getNewPageID()
 {
     $sql1 = 'select max(pageID) from page';
     $statement = $this->dbAdapter->query($sql1);
     $result = $statement->execute();
     $row = $result->current();
     //         Debug::dump($row  );
     return $row['max(pageID)'] + 1;
 }

 /**
  * Initializes database on first run then resets tables on subsequent runs.
  * 
  * @see \Spork\Test\TestCase\TestCaseService::setUp()
  * @throws \Exception on attempt to reset non temporary table
  */
 protected function setUp()
 {
     parent::setUp();
     $services = $this->getServiceLocator();
     if (null === self::$dbAdapter) {
         // initialize database connections
         if (isset($GLOBALS['DB_MAIN_SERVICE_NAME'])) {
             self::$dbAdapterName = $GLOBALS['DB_MAIN_SERVICE_NAME'];
         }
         $dbAdapterDefault = $services->get(self::$dbAdapterName);
         self::$dbSchemaDefault = $dbAdapterDefault->getCurrentSchema();
         if (isset($GLOBALS['DB_TEST_SERVICE_NAME'])) {
             self::$dbAdapter = $services->get($GLOBALS['DB_TEST_SERVICE_NAME']);
             self::$dbSchemaTest = self::$dbAdapter->getCurrentSchema();
         } else {
             self::$dbAdapter = $dbAdapterDefault;
         }
         // drop static tables
         if (null !== self::$dbSchemaTest) {
             self::$dbAdapter->query("DROP DATABASE {$this->quoteIdentifier(self::$dbSchemaTest)}", Adapter::QUERY_MODE_EXECUTE);
             self::$dbAdapter->query("CREATE DATABASE {$this->quoteIdentifier(self::$dbSchemaTest)}", Adapter::QUERY_MODE_EXECUTE);
             self::$dbAdapter->query("USE {$this->quoteIdentifier(self::$dbSchemaTest)}", Adapter::QUERY_MODE_EXECUTE);
         }
         // create test tables
         foreach ($this->getTables($dbAdapterDefault) as $table) {
             $this->createTestTable($table, $dbAdapterDefault, self::$dbAdapter);
         }
         // copy triggers
         if (null !== self::$dbSchemaTest) {
             $triggers = self::$dbAdapter->query("SHOW TRIGGERS FROM {$this->quoteIdentifier(self::$dbSchemaDefault)}", Adapter::QUERY_MODE_EXECUTE);
             foreach ($triggers as $trigger) {
                 $createTrigger = self::$dbAdapter->query("SHOW CREATE TRIGGER {$this->quoteIdentifier(self::$dbSchemaDefault)}.{$this->quoteIdentifier($trigger['Trigger'])}", Adapter::QUERY_MODE_EXECUTE);
                 self::$dbAdapter->query($createTrigger->current()['SQL Original Statement'], Adapter::QUERY_MODE_EXECUTE);
             }
         }
     } else {
         foreach ($this->getTables(self::$dbAdapter) as $table) {
             // make sure temporary tables are temporary
             if (null === self::$dbSchemaTest) {
                 $createTable = $this->getCreateTable($table, self::$dbAdapter);
                 if (strpos($createTable, 'CREATE TEMPORARY TABLE') === false) {
                     throw new \Exception("Table '{$table}' is not temporary");
                 }
             }
             // empty tables
             $result = self::$dbAdapter->query("truncate table `{$table}`", Adapter::QUERY_MODE_EXECUTE);
         }
         $allowOverride = $services->getAllowOverride();
         $services->setAllowOverride(true)->setService(self::$dbAdapterName, self::$dbAdapter)->setAllowOverride($allowOverride);
     }
     $allowOverride = $services->getAllowOverride();
     $services->setAllowOverride(true)->setService(self::$dbAdapterName, self::$dbAdapter)->setAllowOverride($allowOverride);
 }

 public function findRec()
 {
     if (WAuthUtil::get_auth() !== null) {
         $userId = WAuthUtil::get_auth()->userID;
         $sql = "select page.*,predictRating,user.* from (recs join page  on recs.pageId = page.pageID)join user on recs.userId = user.userId where \n                recs.userId = {$userId} and predictRating <> 0 order by predictRating desc";
         $statement = $this->dbAdapter->query($sql);
         $result = $statement->execute();
         if ($result instanceof ResultInterface && $result->isQueryResult()) {
             $resultSet = new WHydrateResultset($this->hydrator, $this->pagePrototype, $this->prototypeArr);
             $tmp = $resultSet->initialize($result);
             //              foreach ($resultSet as $row){
             //                  Debug::dump($row);
             //              }
             return $tmp;
         }
         throw new \InvalidArgumentException("Forum with given ID:{$id} not found.");
     } else {
         return null;
     }
 }

 public function childModuleAccessRights(AdapterInterface $adapter, $module_id, $function_name)
 {
     //Get user credentials
     $session = new Container('base');
     $user = $session->offsetGet('user');
     $this->adapter = $adapter;
     $statement = $adapter->query("SELECT `module_name`, function_name\n                                        FROM\n                                         `app_sub_accesses`\n                                        LEFT JOIN\n                                         `app_modules`\n                                        ON\n                                         `app_modules`.`app_modules_id` = `app_sub_accesses`.`module_id`\n                                         LEFT JOIN\n                                         `app_roles`\n                                         ON\n                                          `app_sub_accesses`.`role_id` = `app_roles`.`app_roles_id`\n                                          WHERE\n                                          `app_roles`.`app_roles_id`= " . $user['role_id'] . "\n                                          AND `app_modules`.`app_modules_id` = {$module_id}\n                                          AND `app_roles`.`status`=1\n                                          AND function_name='" . $function_name . "'\n                                        ");
     $result = $statement->execute();
     //Count number of records
     if (count($result) > 0) {
         return true;
     } else {
         return false;
     }
 }

 public function updateReplynum($id)
 {
     $sql = "update page set preplynum=preplynum+1 where pageID={$id}";
     $statement = $this->dbAdapter->query($sql);
     $statement->execute();
 }