PHP Adapter::query方法代碼示例

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

 private function create(SqlInterface $table)
 {
     try {
         $sql = new Sql($this->dbAdapter);
         $this->dbAdapter->query($sql->buildSqlString($table), Adapter::QUERY_MODE_EXECUTE);
     } catch (PDOException $e) {
         $message = $e->getMessage() . PHP_EOL;
     }
 }

 public function executeQuery($sql)
 {
     if (!$this->dbAdapter) {
         /** @var Adapter dbAdapter */
         $this->dbAdapter = $this->getServiceLocator()->get('Zend\\Db\\Adapter\\Adapter');
     }
     /** @var ResultSet $photos */
     return $this->dbAdapter->query($sql, Adapter::QUERY_MODE_EXECUTE);
 }

 /**
  * 
  * @param string $tableName
  * @param array $data
  * @return bool
  */
 public function insert($tableName, array $data)
 {
     $sql = new Sql($this->adapter);
     $insert = $sql->insert($tableName);
     $insert->values($data);
     $sqlString = $sql->getSqlStringForSqlObject($insert);
     $results = $this->adapter->query($sqlString, Adapter::QUERY_MODE_EXECUTE);
     return $results;
 }

 /**
  * Load config from db-adapter
  *
  * @param \Zend\Db\Adapter\Adapter $db
  * @return array
  */
 protected function loadConfig()
 {
     if (empty($this->dbAdapter)) {
         return $this->config;
     }
     $platform = $this->dbAdapter->getPlatform();
     $driver = $this->dbAdapter->getDriver();
     $query = $this->dbAdapter->query('
         SELECT ' . $platform->quoteIdentifier('value') . '
           FROM ' . $platform->quoteIdentifier('settings') . '
          WHERE ' . $platform->quoteIdentifier('key') . '
              = ' . $platform->quoteValue('ini-cache') . '
            AND ' . $platform->quoteIdentifier('type') . '
              = ' . $platform->quoteValue('ini-cache') . '
          LIMIT 1
     ');
     $this->config = array();
     $result = $query->execute();
     if ($result->getAffectedRows() > 0) {
         foreach ($result as $cache) {
             $this->config = ArrayUtils::merge($this->config, (array) unserialize($cache['value']));
         }
     } else {
         $query = $this->dbAdapter->query('
             SELECT ' . $platform->quoteIdentifier('key') . ',
                    ' . $platform->quoteIdentifier('value') . '
               FROM ' . $platform->quoteIdentifier('settings') . '
              WHERE ' . $platform->quoteIdentifier('type') . '
                  = ' . $platform->quoteValue('ini') . '
         ');
         foreach ($query->execute() as $pair) {
             $key = (string) $pair['key'];
             $value = (string) $pair['value'];
             $entry = array();
             $curr =& $entry;
             foreach (explode('.', $key) as $sub) {
                 $curr[$sub] = null;
                 $curr =& $curr[$sub];
             }
             $curr = $value;
             $this->config = ArrayUtils::merge($this->config, $entry);
         }
         $query = $this->dbAdapter->query('
             INSERT INTO ' . $platform->quoteIdentifier('settings') . '
                         ( ' . $platform->quoteIdentifier('key') . ',
                           ' . $platform->quoteIdentifier('value') . ',
                           ' . $platform->quoteIdentifier('type') . ' )
                  VALUES ( ' . $driver->formatParameterName('key') . ',
                           ' . $driver->formatParameterName('value') . ',
                           ' . $driver->formatParameterName('type') . ' )
         ');
         $query->execute(array('key' => 'ini-cache', 'value' => serialize($this->config), 'type' => 'ini-cache'));
     }
     $this->dbAdapter = null;
     return $this->config;
 }

 public function save(Queue $queue)
 {
     if ($queue->getId()) {
         $query = $this->dbAdapter->query('UPDATE `queues` SET `name` = :name WHERE `id = :id`');
         $query->execute($queue->getArrayCopy());
     } else {
         $query = $this->dbAdapter->query("INSERT INTO `queues` (`name`) VALUES (:name)");
         $query->execute(['name' => $queue->getName()]);
         $queue->setId($this->dbAdapter->getDriver()->getLastGeneratedValue());
     }
     return $queue;
 }

 /**
  * Clear datas with $uid key
  * @param mixed $uid
  * @return void
  */
 public function clear($uid = null)
 {
     $options = $this->getOptions();
     if ($uid) {
         if (!$this->has($uid)) {
             return false;
         }
         $stmt = $this->adapter->query(sprintf('DELETE FROM %s WHERE %s = "%s"', $options['table'], $options['column_key'], $uid), Adapter::QUERY_MODE_EXECUTE);
         return true;
     }
     $stmt = $this->adapter->query(sprintf('TRUNCATE TABLE %s', $options['table']), Adapter::QUERY_MODE_EXECUTE);
     return true;
 }

 private function createTable($query)
 {
     try {
         $this->dbAdapter->query($query, DbAdapter::QUERY_MODE_EXECUTE);
     } catch (\PDOException $e) {
         if (strpos($e->getMessage(), 'table or view already exists') === false) {
             echo $e->getMessage() . PHP_EOL;
             exit(1);
         }
     } catch (\Exception $e) {
         echo $e->getMessage() . PHP_EOL;
         exit(1);
     }
 }

 public function save(User $user)
 {
     //select
     $sql1 = 'select max(userID) from user';
     $statement = $this->dbAdapter->query($sql1);
     $result = $statement->execute();
     $row = $result->current();
     //         Debug::dump($row  );
     $user->setUserID($row['max(userID)'] + 1);
     //insert
     $defaultfaceimgpath = '/data/face/defaultfaceimg.png';
     $sql = 'insert into user(userID,username,upassword,schoolID,faceimgpath) values(' . $user->getUserID() . ',"' . $user->getUsername() . '","' . $user->getUpassword() . '","' . $user->getSchoolID() . '","' . $defaultfaceimgpath . '")';
     //         echo $sql;
     $this->dbAdapter->query($sql, Adapter::QUERY_MODE_EXECUTE);
 }

 /**
  * Autenticates the user
  * @return \Zend\Authentication\Result
  */
 public function authenticate()
 {
     extract($this->db->query('
   SELECT COUNT(`username`) as isAuthenticated FROM `login` WHERE 
   (SELECT `active` FROM `profile` WHERE `login`.`profile_id`=`profile`.`id`) = 1 AND
   `username`="' . addslashes($this->username) . '" AND
   `password`="' . addslashes(md5($this->password)) . '"
   ', \Zend\Db\Adapter\Adapter::QUERY_MODE_EXECUTE)->current()->getArrayCopy());
     if ($isAuthenticated > 0) {
         $code = \Zend\Authentication\Result::SUCCESS;
     } else {
         $code = \Zend\Authentication\Result::FAILURE;
     }
     return new \Zend\Authentication\Result($code, $_REQUEST['username']);
 }

 /**
  * Drops the database table for session data
  *
  * @return void
  */
 protected function dropTable()
 {
     if (!$this->adapter) {
         return;
     }
     $this->adapter->query('DROP TABLE sessions', Adapter::QUERY_MODE_EXECUTE);
 }

 public function query($sql, $parametersOrQueryMode = self::QUERY_MODE_PREPARE)
 {
     $this->getProfiler()->startQuery($sql);
     $return = parent::query($sql, $parametersOrQueryMode);
     $this->getProfiler()->endQuery();
     return $return;
 }

 public function query($sql, $parametersOrQueryMode = self::QUERY_MODE_PREPARE, ResultSet\ResultSetInterface $resultPrototype = null)
 {
     $this->getProfiler()->startQuery($sql);
     $return = parent::query($sql, $parametersOrQueryMode, $resultPrototype);
     $this->getProfiler()->endQuery();
     return $return;
 }

 private function createTableLanguages()
 {
     $table = new Ddl\CreateTable('languages');
     $id = new Column\Integer('id');
     $id->setOption('autoincrement', true);
     $table->addColumn($id);
     $table->addColumn(new Column\Varchar('code', 2));
     $table->addColumn(new Column\Varchar('locale', 6));
     $table->addColumn(new Column\Varchar('name', 50));
     $table->addColumn(new Column\Integer('default', false, 0));
     $table->addConstraint(new Constraint\PrimaryKey('id'));
     $table->addConstraint(new Constraint\UniqueKey('code'));
     $table->addConstraint(new Constraint\UniqueKey('locale'));
     $sql = new Sql($this->dbAdapter);
     $this->dbAdapter->query($sql->buildSqlString($table), Adapter::QUERY_MODE_EXECUTE);
 }