PHP Sql::update方法代碼示例

 /**
  * @covers Zend\Db\Sql\Sql::update
  */
 public function testUpdate()
 {
     $update = $this->sql->update();
     $this->assertInstanceOf('Zend\\Db\\Sql\\Update', $update);
     $this->assertSame('foo', $update->getRawState('table'));
     $this->setExpectedException('Zend\\Db\\Sql\\Exception\\InvalidArgumentException', 'This Sql object is intended to work with only the table "foo" provided at construction time.');
     $this->sql->update('bar');
 }

 public function editarLocal($local, $id)
 {
     $pais = $local['pais'];
     $departamento = $local['departamento'];
     $provincia = $local['provincia'];
     $distrito = $local['distrito'];
     $adapter = $this->tableGateway->getAdapter();
     $sql = new Sql($adapter);
     $idubigeo = $sql->select()->from('ta_ubigeo')->columns(array('in_id'))->where(array('in_idpais' => $pais, 'in_iddep' => $departamento, 'in_idprov' => $provincia, 'in_iddis' => $distrito));
     $selectString0 = $this->tableGateway->getSql()->getSqlStringForSqlObject($idubigeo);
     $result = $adapter->query($selectString0, $adapter::QUERY_MODE_EXECUTE);
     $convertir = $result->toArray();
     $data = array('va_telefono' => $local['va_telefono'], 'va_horario' => $local['va_horario'], 'de_latitud' => $local['de_latitud'], 'de_longitud' => $local['de_longitud'], 'va_rango_precio' => $local['va_rango_precio'], 'va_horario_opcional' => $local['va_horario_opcional'], 'va_direccion' => $local['va_direccion'], 'va_direccion_referencia' => $local['va_direccion_referencia'], 'ta_ubigeo_in_id' => $convertir[0]['in_id'], 'va_dia' => $local['va_dia'], 'va_email' => $local['va_email']);
     $idupda = $sql->update('ta_local')->set($data)->where(array('in_id' => $id));
     $selectString3 = $this->tableGateway->getSql()->getSqlStringForSqlObject($idupda);
     //          var_dump($selectString3);exit;
     $result3 = $adapter->query($selectString3, $adapter::QUERY_MODE_EXECUTE);
     $adapte = $this->tableGateway->getAdapter();
     $sq = new Sql($adapte);
     $select = $sq->select()->from('ta_local')->join(array('tl' => 'ta_plato_has_ta_local'), 'ta_local.in_id = tl.Ta_local_in_id', array('plato' => 'Ta_plato_in_id'))->where(array('ta_local.in_id' => $id));
     $selectString = $sql->getSqlStringForSqlObject($select);
     $results = $adapter->query($selectString, $adapter::QUERY_MODE_EXECUTE);
     $plato = $results->toArray();
     foreach ($plato as $result) {
         $this->estadoRestauranteSolarAction($result['plato'], '');
     }
     return $result3;
 }

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

 public function save($model, $overwrite = false)
 {
     /**
      * For each member variable in the model, call its associated getter to
      * populate the data
      */
     $fields = $model->getFields();
     $data = array();
     foreach ($fields as $row) {
         $data[$row] = $model->{'get' . ucfirst($row)}();
     }
     if (null === $data['id']) {
         /**
          * Create the query string used to insert the data
          * Either update a record if there is a duplicate or ignore it
          */
         $qry = $overwrite ? 'INSERT INTO ' . $this->table : 'INSERT IGNORE INTO ' . $this->table;
         array_shift($fields);
         $insert_fields = implode($fields, ', ');
         $qry .= " ({$insert_fields}) VALUES";
         $i = count($fields);
         $insert_values = array();
         for ($j = 0; $j < $i; $j++) {
             $insert_values[] = ":field_{$j}";
         }
         $insert_values = implode($insert_values, ', ');
         $qry .= " ({$insert_values})";
         if ($overwrite) {
             $qry .= ' ON DUPLICATE KEY UPDATE';
             $update_fields = array();
             for ($j = 0; $j < $i; $j++) {
                 $update_fields[] = ' ' . $fields[$j] . " = :field_{$j}";
             }
             $update_fields = implode($update_fields, ', ');
             $qry .= $update_fields;
         }
         $params = array();
         for ($j = 0; $j < $i; $j++) {
             $params["field_{$j}"] = $data[$fields[$j]];
         }
         $this->dbAdapter->query($qry, $params);
     } else {
         $sql = new Sql($this->dbAdapter);
         $update = $sql->update();
         $fields = $model->getFields();
         array_shift($fields);
         $data = array();
         foreach ($fields as $row) {
             $data[$row] = $model->{'get' . ucfirst($row)}();
         }
         $update->table($this->table);
         $update->set($data);
         $update->where(array('id = ' . $model->getId()));
         $stmt = $sql->prepareStatementForSqlObject($update);
         $stmt->execute();
     }
 }

 public function update($data, $where)
 {
     $update = $this->sql->update($this->_table);
     $update->set($data);
     $update->where($where);
     $result = $this->_execute($update);
     if ($result) {
         return true;
     }
     return false;
 }

 /**
  * Save
  *
  * @return integer
  */
 public function save()
 {
     $this->initialize();
     if ($this->rowExistsInDatabase()) {
         // UPDATE
         $data = $this->data;
         $where = array();
         // primary key is always an array even if its a single column
         foreach ($this->primaryKeyColumn as $pkColumn) {
             $where[$pkColumn] = $this->primaryKeyData[$pkColumn];
             if ($data[$pkColumn] == $this->primaryKeyData[$pkColumn]) {
                 unset($data[$pkColumn]);
             }
         }
         $statement = $this->sql->prepareStatementForSqlObject($this->sql->update()->set($data)->where($where));
         $result = $statement->execute();
         $rowsAffected = $result->getAffectedRows();
         unset($statement, $result);
         // cleanup
     } else {
         // INSERT
         $insert = $this->sql->insert();
         $insert->values($this->data);
         $statement = $this->sql->prepareStatementForSqlObject($insert);
         $result = $statement->execute();
         if (($primaryKeyValue = $result->getGeneratedValue()) && count($this->primaryKeyColumn) == 1) {
             $this->primaryKeyData = array($this->primaryKeyColumn[0] => $primaryKeyValue);
         } else {
             // make primary key data available so that $where can be complete
             $this->processPrimaryKeyData();
         }
         $rowsAffected = $result->getAffectedRows();
         unset($statement, $result);
         // cleanup
         $where = array();
         // primary key is always an array even if its a single column
         foreach ($this->primaryKeyColumn as $pkColumn) {
             $where[$pkColumn] = $this->primaryKeyData[$pkColumn];
         }
     }
     // refresh data
     $statement = $this->sql->prepareStatementForSqlObject($this->sql->select()->where($where));
     $result = $statement->execute();
     $rowData = $result->current();
     unset($statement, $result);
     // cleanup
     // make sure data and original data are in sync after save
     $this->populate($rowData, true);
     // return rows affected
     return $rowsAffected;
 }

 public function updateUsuario($id, $data = array())
 {
     $adapter = $this->tableGateway->getAdapter();
     $sql = new Sql($adapter);
     $update = $sql->update('ta_usuario', $data, array('in_id' => $id));
     $selectString = $sql->getSqlStringForSqlObject($update);
     $results = $adapter->query($selectString, $adapter::QUERY_MODE_EXECUTE);
     // var_dump($selectString);exit;
     $row = $results->current();
     if (!$row) {
         throw new \Exception("No existe registro con el parametro {$id}");
     }
     return $row;
 }

 public function save()
 {
     $sql = new Sql($this->adapter, TestRecord::TABLE);
     $sqlObject = null;
     if ($this->id) {
         // update existing
         $sqlObject = $sql->update()->set(["title" => $this->title, "created_on" => $this->created_on, "type" => $this->type])->where(["id" => $this->id]);
     } else {
         // insert new
         $sqlObject = $sql->insert()->columns(["title", "created_on", "type"])->values(["title" => $this->title, "created_on" => $this->created_on, "type" => $this->type]);
     }
     $statement = $sql->prepareStatementForSqlObject($sqlObject);
     $results = $statement->execute();
 }

 /**
  * Writes the database back to the database
  */
 protected function save()
 {
     $this->validate();
     $zend_db = Database::getConnection();
     $sql = new Sql($zend_db, $this->tablename);
     if ($this->getId()) {
         $update = $sql->update()->set($this->data)->where(array('id' => $this->getId()));
         $sql->prepareStatementForSqlObject($update)->execute();
     } else {
         $insert = $sql->insert()->values($this->data);
         $sql->prepareStatementForSqlObject($insert)->execute();
         $this->data['id'] = $zend_db->getDriver()->getLastGeneratedValue();
     }
 }

    /**
     * Save
     *
     * @return integer
     */
    public function save()
    {
        if (is_array($this->primaryKeyColumn)) {
            // @todo compound primary keys
            throw new Exception\RuntimeException('Compound primary keys are currently not supported, but are on the TODO list.');
        }

        if (isset($this->originalData[$this->primaryKeyColumn])) {

            // UPDATE
            $where = array($this->primaryKeyColumn => $this->originalData[$this->primaryKeyColumn]);
            $data = $this->data;
            unset($data[$this->primaryKeyColumn]);

            $statement = $this->sql->prepareStatementForSqlObject($this->sql->update()->set($data)->where($where));
            $result = $statement->execute();
            $rowsAffected = $result->getAffectedRows();
            unset($statement, $result); // cleanup

        } else {

            // INSERT
            $insert = $this->sql->insert();
            $insert->values($this->data);

            $statement = $this->sql->prepareStatementForSqlObject($insert);

            $result = $statement->execute();
            $primaryKeyValue = $result->getGeneratedValue();
            $rowsAffected = $result->getAffectedRows();
            unset($statement, $result); // cleanup

            $where = array($this->primaryKeyColumn => $primaryKeyValue);
        }

        // refresh data
        $statement = $this->sql->prepareStatementForSqlObject($this->sql->select()->where($where));
        $result = $statement->execute();
        $rowData = $result->current();
        unset($statement, $result); // cleanup

        $this->populateOriginalData($rowData);

        // return rows affected
        return $rowsAffected;
    }

 public function update($data, $orderProductId)
 {
     $adapter = $this->adapter;
     $sql = new Sql($adapter);
     $update = $sql->update();
     $update->table('order_products');
     $update->set($data);
     $update->where(array('order_product_id' => $orderProductId));
     $statement = $sql->prepareStatementForSqlObject($update);
     try {
         $affectedRows = $statement->execute()->getAffectedRows();
     } catch (\Exception $e) {
         die('Error: ' . $e->getMessage());
     }
     if (empty($affectedRows)) {
         die('Zero rows affected');
     }
     return $affectedRows;
 }

 public function _insert(Servicos $servicos)
 {
     $adapter = $this->ConfAdapter();
     $servicos = (array) $servicos;
     $sql = new Sql($adapter);
     if ($servicos['id'] == 0) {
         $insert = $sql->insert('servicos')->values($servicos);
     } else {
         $insert = $sql->update('servicos')->set($servicos)->where(array('id' => $servicos['id']));
     }
     $sqlString = $sql->getSqlStringForSqlObject($insert);
     $adapter->query($sqlString, Adapter::QUERY_MODE_EXECUTE);
     $statement = $adapter->query('select id from servicos order by id desc limit 1');
     $stm = $statement->execute();
     foreach ($stm as $dados) {
         $result = $dados['id'];
     }
     return $result;
 }

 /**
  * Update data into table
  *
  * @param array|ArrayObject $data
  * @param  Where|\Closure|string|array|Predicate\PredicateInterface $predicate
  * @param  string $combination One of the OP_* constants from Predicate\PredicateSet
  * @param  boolean $validate_datatypes ensure all datatype are compatible with column definition
  *
  * @throws Exception\InvalidArgumentException
  * @throws Exception\ColumnNotFoundException when $data contains columns that does not exists in table
  * @throws Exception\ForeignKeyException when insertion failed because of an invalid foreign key
  * @throws Exception\DuplicateEntryException when insertion failed because of an invalid foreign key
  * @throws Exception\NotNullException when insertion failed because a column cannot be null
  * @throws Exception\RuntimeException when insertion failed for another reason
  *
  * @return int number of affected rows
  */
 public function update($data, $predicate, $combination = Predicate\PredicateSet::OP_AND, $validate_datatypes = false)
 {
     $prefixed_table = $this->prefixed_table;
     if ($data instanceof ArrayObject) {
         $d = (array) $data;
     } elseif (is_array($data)) {
         $d = $data;
     } else {
         throw new Exception\InvalidArgumentException(__METHOD__ . ": requires data to be array or an ArrayObject");
     }
     $this->checkDataColumns($d);
     if ($validate_datatypes) {
         $this->validateDatatypes($d);
     }
     $update = $this->sql->update($prefixed_table);
     $update->set($d);
     $update->where($predicate, $combination);
     $result = $this->executeStatement($update);
     return $result->getAffectedRows();
 }

 public function refresh($user_id)
 {
     $adapter = $this->sm->get('Zend\\Db\\Adapter\\Adapter');
     $sql = new Sql($adapter);
     do {
         //Generate new hash
         $hash = Application\Model\Util::unique_id();
         //Check if hash is taken
         $select = $sql->select('tokens_table')->where(array('hash' => $hash));
         $selectString = $sql->getSqlStringForSqlObject($select);
         $results = $adapter->query($selectString, $adapter::QUERY_MODE_EXECUTE)->toArray()[0];
     } while ($results);
     //Disable old hashs
     $update = $sql->update('tokens_table')->set(array('enabled' => 0))->where(array('user_id' => $user_id));
     $selectString = $sql->getSqlStringForSqlObject($update);
     $adapter->query($selectString, $adapter::QUERY_MODE_EXECUTE);
     //Create new hash on table
     $insert = $sql->insert('tokens_table')->values(array('user_id' => $user_id, 'hash' => $hash, 'enabled' => true));
     $selectString = $sql->getSqlStringForSqlObject($insert);
     $adapter->query($selectString, $adapter::QUERY_MODE_EXECUTE);
 }

 public function saveItem($arrParam = null, $options = null)
 {
     if ($options == null) {
         if ($this->getItem($arrParam, array('task' => 'get-item')) == false || $arrParam['id'] == 0) {
             $this->tableGateway->insert($arrParam);
         } else {
             $this->tableGateway->update($arrParam, array('id' => $arrParam['id']));
         }
     }
     if ($options['task'] == 'add') {
         $this->tableGateway->insert($arrParam);
     }
     if ($options['task'] == 'edit') {
         $this->tableGateway->update($arrParam, array('id' => $arrParam['id']));
     }
     if ($options['task'] == 'edit-district') {
         $data = array('name' => $arrParam['name']);
         $sqlObj = new Sql($this->adapter);
         $updateObj = $sqlObj->update('city_district');
         $updateObj->set($data);
         $updateObj->where('id = ' . $arrParam['id']);
         $sqlString = $sqlObj->getSqlStringForSqlObject($updateObj);
         $this->adapter->query($sqlString)->execute();
     }
     if ($options['task'] == 'edit-ward') {
         $data = array('name' => $arrParam['name']);
         $sqlObj = new Sql($this->adapter);
         $updateObj = $sqlObj->update('city_district_ward');
         $updateObj->set($data);
         $updateObj->where('id = ' . $arrParam['id']);
         $sqlString = $sqlObj->getSqlStringForSqlObject($updateObj);
         $this->adapter->query($sqlString)->execute();
     }
     if ($options['task'] == 'add-district') {
         $data = array('city_id' => $arrParam['id'], 'name' => $arrParam['name']);
         $sqlObj = new Sql($this->adapter);
         $insertObj = $sqlObj->insert('city_district');
         $insertObj->values($data);
         $sqlString = $sqlObj->getSqlStringForSqlObject($insertObj);
         $this->adapter->query($sqlString)->execute();
     }
     if ($options['task'] == 'add-ward') {
         $data = array('district_id' => $arrParam['id'], 'name' => $arrParam['name']);
         $sqlObj = new Sql($this->adapter);
         $insertObj = $sqlObj->insert('city_district_ward');
         $insertObj->values($data);
         $sqlString = $sqlObj->getSqlStringForSqlObject($insertObj);
         $this->adapter->query($sqlString)->execute();
     }
 }