PHP Sql\Insert类代码示例

 public function constructPreferences($user_id, $table, $preferences = null, $title = null)
 {
     if ($preferences) {
         $newPreferencesData = false;
         // @todo enforce non-empty set
         if (empty($preferences['columns_visible'])) {
             $newPreferencesData = true;
             $columns_visible = TableSchema::getTableColumns($table, 6);
             $preferences['columns_visible'] = implode(',', $columns_visible);
         }
         $preferencesDefaultsApplied = $this->applyDefaultPreferences($table, $preferences);
         if (count(array_diff($preferences, $preferencesDefaultsApplied))) {
             $newPreferencesData = true;
         }
         $preferences = $preferencesDefaultsApplied;
         if ($newPreferencesData) {
             $id = $this->addOrUpdateRecordByArray($preferences);
         }
         return $preferences;
     }
     $insert = new Insert($this->table);
     // User doesn't have any preferences for this table yet. Please create!
     $columns_visible = TableSchema::getTableColumns($table, 6);
     $data = array('user' => $user_id, 'columns_visible' => implode(',', $columns_visible), 'table_name' => $table, 'title' => $title);
     if (TableSchema::hasTableColumn($table, 'sort')) {
         $data['sort'] = 'sort';
     }
     $data = $this->applyDefaultPreferences($table, $data);
     $insert->values($data);
     $this->insertWith($insert);
     return $data;
 }

 public function insertBookmark($payload)
 {
     $insert = new Insert($this->table);
     $insert->values($payload);
     $this->insertWith($insert);
     return $this->lastInsertValue;
 }

 public function setRequest($id)
 {
     if ($id == $this->user_id) {
         return true;
     }
     $user = $this->getUserById($id);
     if (is_array($user)) {
         if ($user["friendship"] == -1) {
             //insert
             $insert = new Insert('fg_friends');
             $newData = array('user_one' => $this->user_id, 'user_two' => $id, 'state' => '0');
             $insert->values($newData);
             $statement = $this->tableGateway->getSql()->prepareStatementForSqlObject($insert);
             $resultSet = $statement->execute();
         } else {
             if (!$user["i_am_adder"] && $user["friendship"] == 0) {
                 //update
                 $update = new Update('fg_friends');
                 $newData = array('state' => '1');
                 $update->set($newData);
                 $update->where(array('user_one' => $id, 'user_two' => $this->user_id));
                 $statement = $this->tableGateway->getSql()->prepareStatementForSqlObject($update);
                 $resultSet = $statement->execute();
             }
         }
         return true;
     }
     return false;
 }

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

 /**
  * {@inheritDoc}
  */
 public function save(PostInterface $postObject)
 {
     $postData = $this->hydrator->extract($postObject);
     unset($postData['id']);
     // Neither Insert nor Update needs the ID in the array
     if ($postObject->getId()) {
         // ID present, it's an Update
         $action = new Update('post');
         $action->set($postData);
         $action->where(array('id = ?' => $postObject->getId()));
     } else {
         // ID NOT present, it's an Insert
         $action = new Insert('post');
         $action->values($postData);
     }
     $sql = new Sql($this->dbAdapter);
     $stmt = $sql->prepareStatementForSqlObject($action);
     $result = $stmt->execute();
     if ($result instanceof ResultInterface) {
         if ($newId = $result->getGeneratedValue()) {
             // When a value has been generated, set it on the object
             $postObject->setId($newId);
         }
         return $postObject;
     }
     throw new \Exception("Database error");
 }

 public function insertSession($username, $password, $session_id)
 {
     $insert = new Insert('sessions');
     $adapter = $this->table_gateway->getAdapter();
     $insert->columns(array('username', 'password', 'active', 'session_id'))->values(array('username' => $username, 'password' => $password, 'active' => 1, 'session_id' => $session_id));
     $adapter->query($this->sql->buildSqlString($insert), $adapter::QUERY_MODE_EXECUTE);
     return true;
 }

 /**
  * Exports insert SQL.
  *
  * @param  array  $data
  * @return string
  */
 protected function exportInsert(array $data)
 {
     $insertSql = '';
     $insert = new Insert(self::TABLE_NAME);
     foreach ($data as $id => $value) {
         $insertSql .= @$insert->values(array('id' => $id, 'value' => $value))->getSqlString($this->getPlatform()) . ';' . PHP_EOL;
     }
     return $insertSql;
 }

 public function insertPrivilege($attributes)
 {
     $attributes = $this->verifyPrivilege($attributes);
     $status_id = isset($attributes['status_id']) ? $attributes['status_id'] : 0;
     $insert = new Insert($this->getTable());
     $insert->columns(array('table_name', 'permissions', 'group_id'))->values(array('table_name' => $attributes['table_name'], 'permissions' => $attributes['permissions'], 'group_id' => $attributes['group_id'], 'status_id' => $status_id));
     $this->insertWith($insert);
     $privilegeId = $this->lastInsertValue;
     return $this->fetchById($privilegeId);
 }

 public function install($config)
 {
     $dbinstall = new Dbinstall($config);
     $table_account = new CreateTable('account');
     $table_account->addColumn(new Column\Integer('id', FALSE, NULL, array('autoincrement' => true)))->addConstraint(new Constraint\PrimaryKey('id'))->addColumn(new Column\Varchar('username', 50, false))->addColumn(new Column\Varchar('password', 50, false))->addColumn(new Column\Varchar('email', 50, false))->addConstraint(new Constraint\UniqueKey('email'))->addColumn(new Column\Varchar('openid_qq', 100, true))->addConstraint(new Constraint\UniqueKey('openid_qq'))->addColumn(new Column\Varchar('openid_sina', 100, true))->addConstraint(new Constraint\UniqueKey('openid_sina'))->addColumn(new Column\Varchar('openid_wechat', 100, true))->addConstraint(new Constraint\UniqueKey('openid_wechat'))->addColumn(new Column\Integer('status', false, 0));
     $dbinstall->addCreateTable($table_account);
     $insert_account = new Insert('account');
     $insert_account->values(array('username' => 'admin', 'password' => 'admin', 'email' => '164713332@qq.com', 'status' => 1));
     $dbinstall->addInsert($insert_account);
     $dbinstall->install();
 }

 /**
  * @param Insert $insert
  */
 public function preInsert(Insert $insert)
 {
     $metaColumns = $this->tableGateway->getColumns();
     if (count($metaColumns)) {
         $metaColumns = array_flip($metaColumns);
         $columns = array_flip($insert->getRawState('columns'));
         $columns = array_flip(array_intersect_key($columns, $metaColumns));
         $values = $insert->getRawState('values');
         $values = array_intersect_key($values, $columns);
         $insert->values(array_values($values));
         $insert->columns(array_values($columns));
     }
 }

 public function insertPrivilege($attributes)
 {
     $attributes = $this->verifyPrivilege($attributes);
     // @todo: this should fallback on field default value
     if (!isset($attributes['status_id'])) {
         $attributes['status_id'] = 0;
     }
     $attributes = $this->getFillableFields($attributes);
     $insert = new Insert($this->getTable());
     $insert->columns(array_keys($attributes))->values($attributes);
     $this->insertWith($insert);
     $privilegeId = $this->lastInsertValue;
     return $this->fetchById($privilegeId);
 }

 public function saveProduct($productData)
 {
     $action = new Insert('products');
     $action->values($productData);
     $sql = new Sql($this->dbAdapter);
     $stmt = $sql->prepareStatementForSqlObject($action);
     $result = $stmt->execute();
     if ($result instanceof ResultInterface) {
         if ($newId = $result->getGeneratedValue()) {
             return $newId;
         }
         return true;
     }
     throw new \Exception('Database Error');
 }

 public function save(RFollow $followObject)
 {
     $postData = $this->hydrator->extract($followObject);
     //           Debug::dump($postData);
     /*
      * 下面这两行一定记得自己加上去
      */
     $postData['userID'] = $followObject->getUser()->getUserID();
     //         unset($postData['secname']);//等到更改成类的时候需要利用php的特性改变成员变量的类型，class变成id
     //           unset($postData['username']);//虽然还是比较麻烦，但是只要写一个类就够了，不需要再unset这么多了。
     $action = new Insert('rfollow');
     $action->values($postData);
     $sql = new Sql($this->dbAdapter);
     $stmt = $sql->prepareStatementForSqlObject($action);
     $result = $stmt->execute();
 }

 public function save(\Api\Entity\User $user)
 {
     $hydrator = $this->getHydrator();
     $postData = array('display_name' => $user->getEmail(), 'password' => $user->getPassword());
     $postData = array_merge($postData, array('email' => $user->getEmail(), 'username' => $user->getUsername()));
     $insert = new Insert('user');
     $insert->values($postData);
     $sql = new Sql($this->getAdaptor());
     $statement = $sql->prepareStatementForSqlObject($insert);
     $result = $statement->execute();
     if ($result instanceof ResultInterface) {
         if ($pk = $result->getGeneratedValue()) {
             $user->setUserId($pk);
         }
         return $hydrator->extract($user);
     }
     throw new \Exception('something went wrong.Please try again later');
 }

 /**
  * Method to Insert a new User into the Database
  * 
  * @param array $array
  * @return array
  */
 public function registerUserByLoginName($array)
 {
     $array["password"] = $this->passwordService->create($array["password"], $array["timestamp"]);
     $values["password"] = $array["password"];
     $values["loginName"] = $array["loginName"];
     $values["ingameName"] = $array["ingameName"];
     $values["timestamp"] = $array["timestamp"];
     $action = new Insert('tbluser');
     $action->values($values);
     $sql = new Sql($this->dbAdapter);
     $stmt = $sql->prepareStatementForSqlObject($action);
     $result = $stmt->execute();
     if ($result instanceof ResultInterface) {
         if ($newID = $result->getGeneratedValue()) {
             return array('registerSuccess' => true);
         }
     }
     return array('registerSuccess' => false, 'errors' => array('errorMessage' => 'Database error'));
 }