PHP Zend_Db_Statement_Pdo类代码示例

 public function testStatementConstructWithSelectObject()
 {
     $select = $this->_db->select()->from('zfproducts');
     $stmt = new Zend_Db_Statement_Pdo($this->_db, $select);
     $this->assertTrue($stmt instanceof Zend_Db_Statement_Interface);
     $stmt->closeCursor();
 }

 /**
  * Get formatted statistics message
  *
  * @param string $type Type of query
  * @param string $sql
  * @param array $bind
  * @param \Zend_Db_Statement_Pdo|null $result
  * @return string
  * @throws \Zend_Db_Statement_Exception
  */
 public function getStats($type, $sql, $bind = [], $result = null)
 {
     $message = '## ' . getmypid() . ' ## ';
     $nl = "\n";
     $time = sprintf('%.4f', microtime(true) - $this->timer);
     if (!$this->logAllQueries && $time < $this->logQueryTime) {
         return '';
     }
     switch ($type) {
         case self::TYPE_CONNECT:
             $message .= 'CONNECT' . $nl;
             break;
         case self::TYPE_TRANSACTION:
             $message .= 'TRANSACTION ' . $sql . $nl;
             break;
         case self::TYPE_QUERY:
             $message .= 'QUERY' . $nl;
             $message .= 'SQL: ' . $sql . $nl;
             if ($bind) {
                 $message .= 'BIND: ' . var_export($bind, true) . $nl;
             }
             if ($result instanceof \Zend_Db_Statement_Pdo) {
                 $message .= 'AFF: ' . $result->rowCount() . $nl;
             }
             break;
     }
     $message .= 'TIME: ' . $time . $nl;
     if ($this->logCallStack) {
         $message .= 'TRACE: ' . Debug::backtrace(true, false) . $nl;
     }
     $message .= $nl;
     return $message;
 }

 public function editAction()
 {
     //$this->_helper->viewRenderer->setNoRender(true);
     $referer = $this->getRequest()->getHeader('Referer');
     $id = $this->getParam('id');
     $userForm = new Form_Auth_Add();
     try {
         #using named parameters
         /*
         $sql = 'SELECT * FROM users WHERE id = :id';
         $stmt = new Zend_Db_Statement_Pdo($this->db, $sql);
         $stmt->execute(array(':id' => $this->getParam('id')));
         */
         #using positional parameters
         $sql = 'SELECT * FROM users WHERE id = ?';
         $stmt = new Zend_Db_Statement_Pdo($this->db, $sql);
         $stmt->execute(array($id));
         $userData = $stmt->fetchAll();
         if ($this->getRequest()->isPost()) {
             print_r($usersData);
             exit;
             // clearing previous values
             $usersData = array();
             $userData = $this->getRequest()->getPost();
             if ($userForm->isValid($userData)) {
                 $this->db->update($userData, 'id = ' . (int) $id);
                 $this->_redirect($referer);
             }
         }
         $userForm->populate($userData);
         print_r($userData[0]);
         //exit;
         //$this->view->users = $usersData; // oneliner // $this->view->users = $this->db->fetchAll($sql);
         $this->view->userForm = $userForm;
         // oneliner // $this->view->users = $this->db->fetchAll($sql);
     } catch (Exception $e) {
         $this->_helper->flashMessenger('An error_log');
     }
 }

    public function getFirstOrLastLogForUser($idUser, $first = true)
    {
        $db = $this->getDbTable()->getAdapter();
        $sql = '	SELECT *
					FROM pic 
					WHERE user_id = :idUser
					';
        if ($first) {
            $sql .= ' ORDER BY date_shot DESC LIMIT 1';
        } else {
            $sql .= ' ORDER BY date_shot ASC LIMIT 1';
        }
        $stmt = new Zend_Db_Statement_Pdo($db, $sql);
        $stmt->bindParam(':idUser', $idUser);
        $stmt->execute();
        $resultSet = $stmt->fetchAll();
        $arrPics = $this->createObjektArr($resultSet);
        if (empty($arrPics)) {
            return 0;
        } else {
            return $arrPics[0];
        }
    }

 function insertAllSessionData($session_date, $records)
 {
     $db = Zend_Registry::get('db');
     $i = 0;
     $max_inserts = 100;
     for ($rows = 0; $rows < count($records); $rows += $max_inserts) {
         $values = array();
         $sql = 'INSERT INTO t_exercise_data
                    (session_date,
                     time,
                     distance,
                     heartrate,
                     speed,
                     latitude,
                     longitude,
                     altitude,
                     cadence,
                     temperature,
                     power,
                     gradient,
                     userid)
                 VALUES ';
         /* Do a multi insert */
         for ($i = 0; $i < $max_inserts && $i + $rows < count($records); $i++) {
             if ($i != 0) {
                 $sql .= ', ';
             }
             $sql .= '(:session_date,
                       :time' . $i . ',
                       :distance' . $i . ',
                       :heartrate' . $i . ',
                       :speed' . $i . ',
                       :latitude' . $i . ',
                       :longitude' . $i . ',
                       :altitude' . $i . ',
                       :cadence' . $i . ',
                       :temperature' . $i . ',
                       :power' . $i . ',
                       :gradient' . $i . ',
                       :userid) ';
             $values[':time' . $i] = $records[$i + $rows]->interval;
             $values[':distance' . $i] = $records[$i + $rows]->distance;
             $values[':heartrate' . $i] = $records[$i + $rows]->heart_rate;
             $values[':speed' . $i] = $records[$i + $rows]->speed;
             $values[':latitude' . $i] = $records[$i + $rows]->position_lat;
             $values[':longitude' . $i] = $records[$i + $rows]->position_long;
             $values[':altitude' . $i] = $records[$i + $rows]->altitude;
             $values[':cadence' . $i] = $records[$i + $rows]->cadence;
             $values[':temperature' . $i] = $records[$i + $rows]->temperature;
             $values[':power' . $i] = $records[$i + $rows]->power;
             $values[':gradient' . $i] = $records[$i + $rows]->gradient;
         }
         //$stmt = new Zend_Db_Statement($db, $sql);
         $stmt = new Zend_Db_Statement_Pdo($db, $sql);
         /* Add the constant values for all rows */
         $values[':session_date'] = $session_date;
         $values[':userid'] = Core_Common::getCurrentUserLogin();
         $stmt->execute($values);
     }
 }

 /**
  * Prepares an SQL statement.
  *
  * @param string $sql The SQL statement with placeholders.
  * @param array $bind An array of data to bind to the placeholders.
  * @return PDOStatement
  */
 public function prepare($sql)
 {
     $this->_connect();
     $stmt = new Zend_Db_Statement_Pdo($this, $sql);
     $stmt->setFetchMode($this->_fetchMode);
     return $stmt;
 }

 /**
  * Logging debug information
  *
  * @param int $type
  * @param string $sql
  * @param array $bind
  * @param Zend_Db_Statement_Pdo $result
  * @return Varien_Db_Adapter_Pdo_Mysql
  */
 protected function _debugStat($type, $sql, $bind = array(), $result = null)
 {
     if (!$this->_debug) {
         return $this;
     }
     $code = '## ' . getmypid() . ' ## ';
     $nl = "\n";
     $time = sprintf('%.4f', microtime(true) - $this->_debugTimer);
     if (!$this->_logAllQueries && $time < $this->_logQueryTime) {
         return $this;
     }
     switch ($type) {
         case self::DEBUG_CONNECT:
             $code .= 'CONNECT' . $nl;
             break;
         case self::DEBUG_TRANSACTION:
             $code .= 'TRANSACTION ' . $sql . $nl;
             break;
         case self::DEBUG_QUERY:
             $code .= 'QUERY' . $nl;
             $code .= 'SQL: ' . $sql . $nl;
             if ($bind) {
                 $code .= 'BIND: ' . var_export($bind, true) . $nl;
             }
             if ($result instanceof Zend_Db_Statement_Pdo) {
                 $code .= 'AFF: ' . $result->rowCount() . $nl;
             }
             break;
     }
     $code .= 'TIME: ' . $time . $nl;
     if ($this->_logCallStack) {
         $code .= 'TRACE: ' . Varien_Debug::backtrace(true, false) . $nl;
     }
     $code .= $nl;
     $this->_debugWriteToFile($code);
     return $this;
 }

 /**
  * Create default timeslot values based on days/session duration
  *
  * @param	array	$post	Post request
  */
 public function createTimeslots($post)
 {
     $db = $this->getAdapter();
     $dt = new Zend_Date($post['start'], 'dd/MM/YYYY hh:mm');
     $sql = 'insert into timeslots (tstart, tend, number, type, conference_id)' . 'values (:tstart, :tend, :number, :type, ' . (int) $post['id'] . ')';
     $stmt = new Zend_Db_Statement_Pdo($db, $sql);
     $n = 0;
     // for every day add slot/break/lunch/slot/break/slot
     for ($i = 1; $i <= $post['days']; $i++) {
         $stmt->execute(array(':tstart' => $dt->get(Zend_Date::ISO_8601), ':tend' => $dt->add('90', 'mm')->get(Zend_Date::ISO_8601), ':number' => $n + 1, ':type' => 1));
         $stmt->execute(array(':tstart' => $dt->get(Zend_Date::ISO_8601), ':tend' => $dt->add('30', 'mm')->get(Zend_Date::ISO_8601), ':number' => 0, ':type' => 2));
         $stmt->execute(array(':tstart' => $dt->get(Zend_Date::ISO_8601), ':tend' => $dt->add('90', 'mm')->get(Zend_Date::ISO_8601), ':number' => $n + 2, ':type' => 1));
         $stmt->execute(array(':tstart' => $dt->get(Zend_Date::ISO_8601), ':tend' => $dt->add('90', 'mm')->get(Zend_Date::ISO_8601), ':number' => 0, ':type' => 3));
         $stmt->execute(array(':tstart' => $dt->get(Zend_Date::ISO_8601), ':tend' => $dt->add('90', 'mm')->get(Zend_Date::ISO_8601), ':number' => $n + 3, ':type' => 1));
         $stmt->execute(array(':tstart' => $dt->get(Zend_Date::ISO_8601), ':tend' => $dt->add('30', 'mm')->get(Zend_Date::ISO_8601), ':number' => 0, ':type' => 2));
         $stmt->execute(array(':tstart' => $dt->get(Zend_Date::ISO_8601), ':tend' => $dt->add('90', 'mm')->get(Zend_Date::ISO_8601), ':number' => $n = $n + 4, ':type' => 1));
         // reset date and add a day
         $dt = new Zend_Date($post['start'], 'dd/MM/YYYY hh:mm');
         $dt->add($i, Zend_Date::DAY_SHORT);
     }
     return true;
 }

    public function searchFriendsByName($idUser, $username)
    {
        $db = $this->getDbTable()->getAdapter();
        $sql = '	SELECT 	f.*
					FROM 	user f, 
							user_friends u 
					WHERE 	u.id_user = :idUser
					AND 	f.id = u.id_friend	
					AND		f.username LIKE :name ';
        $username = '%' . $username . '%';
        $stmt = new Zend_Db_Statement_Pdo($db, $sql);
        $stmt->bindParam(':idUser', $idUser);
        $stmt->bindParam(':name', $username);
        $stmt->execute();
        $resultSet = $stmt->fetchAll();
        $arrRestaurants = $this->createObjektArr($resultSet);
        return $arrRestaurants;
    }

 /**
  * Run test fetchItem method
  *
  * @return void
  */
 public function testFetchItem()
 {
     $adapterMock = $this->getMock('Magento\\Framework\\DB\\Adapter\\Pdo\\Mysql', ['query'], [], '', false);
     $this->selectMock->expects($this->once())->method('getConnection')->will($this->returnValue($adapterMock));
     $adapterMock->expects($this->once())->method('query')->will($this->returnValue($this->fetchStmtMock));
     $this->fetchStmtMock->expects($this->once())->method('fetch')->will($this->returnValue(null));
     $this->assertEquals([], $this->query->fetchItem());
 }

 /**
  * Fetches a row from the result set.
  *
  * @param int $style  OPTIONAL Fetch mode for this fetch operation.
  * @param int $cursor OPTIONAL Absolute, relative, or other.
  * @param int $offset OPTIONAL Number for absolute or relative cursors.
  * @return mixed Array, object, or scalar depending on fetch mode.
  * @throws Zend_Db_Statement_Exception
  */
 public function fetch($style = null, $cursor = null, $offset = null)
 {
     $row = parent::fetch($style, $cursor, $offset);
     $remove = $this->_adapter->foldCase('zend_db_rownum');
     if (is_array($row) && array_key_exists($remove, $row)) {
         unset($row[$remove]);
     }
     return $row;
 }

 /**
  * Fetch statement
  *
  * @return mixed
  */
 public function fetchItem()
 {
     if (null === $this->fetchStmt) {
         $this->fetchStmt = $this->getConnection()->query($this->getSelect(), $this->bindParams);
     }
     $data = $this->fetchStmt->fetch();
     if (!$data) {
         $data = [];
     }
     return $data;
 }

 /**
  * Returns an array containing all of the result set rows.
  *
  * Behaves like parent, but if limit()
  * is used, the final result removes the extra column
  * 'zend_db_rownum'
  *
  * @param int $style OPTIONAL Fetch mode.
  * @param int $col   OPTIONAL Column number, if fetch mode is by column.
  * @return array Collection of rows, each in a format by the fetch mode.
  * @throws Zend_Db_Statement_Exception
  */
 public function fetchAll($style = null, $col = null)
 {
     $data = parent::fetchAll($style, $col);
     $results = array();
     $remove = $this->_adapter->foldCase('ZEND_DB_ROWNUM');
     foreach ($data as $row) {
         if (is_array($row) && array_key_exists($remove, $row)) {
             unset($row[$remove]);
         }
         $results[] = $row;
     }
     return $results;
 }

 /**
  * Returns a collection item that corresponds to the fetched row
  * and moves the internal data pointer ahead
  *
  * @return  Varien_Object|bool
  */
 public function fetchItem()
 {
     if (null === $this->_fetchStmt) {
         $this->_fetchStmt = $this->getConnection()->query($this->getSelect());
     }
     $data = $this->_fetchStmt->fetch();
     if (!empty($data) && is_array($data)) {
         $item = $this->getNewEmptyItem();
         if ($this->getIdFieldName()) {
             $item->setIdFieldName($this->getIdFieldName());
         }
         $item->setData($data);
         return $item;
     }
     return false;
 }

 /**
  * Executes a prepared statement.
  *
  * @param array $params OPTIONAL Values to bind to parameter placeholders.
  * @return bool
  * @throws Zend_Db_Statement_Exception
  */
 public function _execute(array $params = null)
 {
     $this->_adapter->incrementExecCounter($this->caller);
     return parent::_execute($params);
 }