CODEWITHSUNDEEP
phpmysql
xttrust
xttrust

Reputation: 595

PHP/MySQL, How to update just some records in database, not all at once?

Can anyone tell me how not to update all my records in table. This is my update statement.

public function update() {

    try {
        $form = new Form();

        $form   ->post('id')
                ->post('username')
                ->val('minlenght', 4)
                ->val('maxlenght', 20)
                ->post('role')
                ->post('first_name')
                ->post('last_name')
                ->post('birthdate')
                ->post('email')
                ->val('email');

        $form->submit();

        // sett variables after  
        $this->model->id = $_POST['id'];
        $this->model->username = $_POST['username'];
        $this->model->pass = $_POST['pass'];
        $this->model->role = $_POST['role'];
        $this->model->first_name = $_POST['first_name'];
        $this->model->last_name = $_POST['last_name'];
        $this->model->email = $_POST['email'];
        $this->model->join_date = $_POST['join_date'];
        $this->model->birthdate = $_POST['birthdate'];
        $this->model->country = $_POST['country'];
        $this->model->status = $_POST['status'];

        // here i call the method from model
        $this->model->update();

        $_SESSION['message'] = '<span class="label label-success">Saved</span>';
        redirect_to(URL."user/edit/".$this->model->id);
    } catch (Exception $e) {
        $_SESSION['message'] = '<span class="label label-important">' . $e->getMessage() . '</span>';
        redirect_to(URL . 'user?page=1');
    }

}

Here is the code for the model.

public function update() {

    $attributes = $this->sanitized_attributes();
    $attribute_pairs = array();
    foreach ($attributes as $key => $value) {
        $attribute_pairs[] = "{$key}='{$value}'";
    }
    $sql = "UPDATE " . self::$table_name . " SET ";
    $sql .= join(", ", $attribute_pairs);
    $sql .= " WHERE id=" . $this->database->escape_value($this->id);
    $this->database->query($sql);
    return ($this->database->affected_rows() == 1) ? true : false;
}

The attributes are 

private static $db_fields = array('id', 'role', 'username', 'pass', 'role', 'first_name', 'last_name', 'email', 'join_date', 'birthdate', 'country', 'avatar', 'status');
private static $table_name = "user";
public $id;
public $username;
public $pass;
public $role;
public $first_name;
public $last_name;
public $email;
public $join_date;
public $birthdate;
public $country;
public $avatar;
public $status;

When I try to update, even if I put only username, it updates all records in database. What need to be change in this code so that I can update only records I specify? I tried couple of hours to try to figure it out, but I came at the conclusion that I need to remake my user class, not to be so automatic.

Upvotes: 0

Views: 319

Answers (1)

Akhilesh Sharma
Akhilesh Sharma

Reputation: 1628

Here is the updated version of the update() method that you have listed above

public function update() {

    $attributes = $this->sanitized_attributes();
    $attribute_pairs = array();
    foreach ($attributes as $key => $value) {
if(empty($value))
{
continue;
}
else
{
$attribute_pairs[] = "{$key}='{$value}'";
}

    }
    $sql = "UPDATE " . self::$table_name . " SET ";
    $sql .= join(", ", $attribute_pairs);
    $sql .= " WHERE id=" . $this->database->escape_value($this->id);
    $this->database->query($sql);
    return ($this->database->affected_rows() == 1) ? true : false;
}

Upvotes: 1

Related Questions