MySQL update function (I don't want to update blank fields, but all other)

Question

Basically: I got EditProfile.php with form(to change profile data), If all fields are chosen it works perfectly.

Problem: I want to update only that fields, that aren't blank. For example Image (Update only if file, has been uploaded.) AND gender (Update only if option has been selected.) At this moment, it updates these two fields to blank (delete's Image patch and gender option).

And Error with second version:

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''username' = CASE WHEN 'Crelix' IS NULL OR CHAR_LENGTH(TRIM(Crelix)) = 0 THEN 'b' at line 2

Sending Form data to different file function.

if (isset($_POST['edit-btn'])) {
    $id = strip_tags($_POST['id']);
    $username = strip_tags($_POST['username']);
    $email = strip_tags($_POST['email']);
    $password = strip_tags($_POST['password']);
    if (move_uploaded_file($tempFile,$targetFile)) {
        $image = '../Images/Users/Gallery/' . basename($_FILES["file"]["name"]);
    }
    $firstname = strip_tags($_POST['firstname']);
    $surname = strip_tags($_POST['surname']);
    $gender = strip_tags($_POST['gender']);
    $birthday = strip_tags($_POST['birthday']);
    $carsowned = strip_tags($_POST['carsowned']);
    $homepage = strip_tags($_POST['homepage']);
    $phone = strip_tags($_POST['phone']);
    $education = strip_tags($_POST['education']);
    $profession = strip_tags($_POST['profession']);
    $work = strip_tags($_POST['work']);
    $facebook = strip_tags($_POST['facebook']);
    $instagram = strip_tags($_POST['instagram']);
    $twitter = strip_tags($_POST['twitter']);
    $vk = strip_tags($_POST['vk']);
    $bio = strip_tags($_POST['bio']);
    $interests = strip_tags($_POST['interests']);

    $user->editProfile($id,$username,$email,$password,$image,$firstname,$surname,$gender,$birthday,$carsowned,$homepage,$phone,$education,$profession,$work,
                    $facebook,$instagram,$twitter,$vk,$bio,$interests);
}

And Update function: (Got 2 versions) No one works.

My Own Version:

    public function editProfile($id,$username,$email,$password,$image,$firstname,$surname,$gender,$birthday,$carsowned,$homepage,$phone,$education,$profession,$work,
                    $facebook,$instagram,$twitter,$vk,$bio,$interests){
    try{
        $stmt = $this->conn->prepare("UPDATE users SET id=:id,username=:username,email=:email,password=:password,image=:image,firstname=:firstname,surname=:surname,
        gender=:gender,birthday=:birthday,carsowned=:carsowned,homepage=:homepage,phone=:phone,education=:education,profession=:profession,work=:work,facebook=:facebook,instagram=:instagram,
        twitter=:twitter,vk=:vk,bio=:bio,interests=:interests WHERE id=('$id')");

        $stmt->bindparam(":id", $id);
        $stmt->bindparam(":username", $username);
        $stmt->bindparam(":email", $email);
        $stmt->bindparam(":password", $password);
        $stmt->bindparam(":image", $image);
        $stmt->bindparam(":firstname", $firstname);
        $stmt->bindparam(":surname", $surname);
        $stmt->bindparam(":gender", $gender);
        $stmt->bindparam(":birthday", $birthday);
        $stmt->bindparam(":carsowned", $carsowned);
        $stmt->bindparam(":homepage", $homepage);
        $stmt->bindparam(":phone", $phone);
        $stmt->bindparam(":education", $education);
        $stmt->bindparam(":profession", $profession);
        $stmt->bindparam(":work", $work);
        $stmt->bindparam(":facebook", $facebook);
        $stmt->bindparam(":instagram", $instagram);
        $stmt->bindparam(":twitter", $twitter);
        $stmt->bindparam(":vk", $vk);
        $stmt->bindparam(":bio", $bio);
        $stmt->bindparam(":interests", $interests);

        $stmt->execute();

        return $stmt;
    }
    catch(PDOException $e)
    {
        echo $e->getMessage();
    }
}

Reading and searching online for help, made something like this, but nothing changes:

public function editProfile($id,$username,$email,$password,$image,$firstname,$surname,$gender,$birthday,$carsowned,$homepage,$phone,$education,$profession,$work,
                    $facebook,$instagram,$twitter,$vk,$bio,$interests){
    try{
        $stmt = $this->conn->prepare("UPDATE users SET
        '$username' = CASE WHEN '$username' IS NULL OR CHAR_LENGTH(TRIM($username)) = 0 THEN 'bla' ELSE '$username' END,
        '$email' = CASE WHEN '$email' IS NULL OR CHAR_LENGTH(TRIM($email)) = 0 THEN 'bla' ELSE '$email' END,
        '$password' = CASE WHEN '$password' IS NULL OR CHAR_LENGTH(TRIM($password)) = 0 THEN 'bla' ELSE '$password' END,
        '$image' = CASE WHEN '$image' IS NULL OR CHAR_LENGTH(TRIM($image)) = 0 THEN 'bla' ELSE '$image' END,
        '$firstname' = CASE WHEN '$firstname' IS NULL OR CHAR_LENGTH(TRIM($firstname)) = 0 THEN 'bla' ELSE '$firstname' END,
        '$surname' = CASE WHEN '$surname' IS NULL OR CHAR_LENGTH(TRIM($surname)) = 0 THEN 'bla' ELSE '$surname' END,
        '$gender' = CASE WHEN '$gender' IS NULL OR CHAR_LENGTH(TRIM($gender)) = 0 THEN 'bla' ELSE '$gender' END,
        '$birthday' = CASE WHEN '$birthday' IS NULL OR CHAR_LENGTH(TRIM($birthday)) = 0 THEN 'bla' ELSE '$birthday' END,
        '$carsowned' = CASE WHEN '$carsowned' IS NULL OR CHAR_LENGTH(TRIM($carsowned)) = 0 THEN 'bla' ELSE '$carsowned' END,
        '$homepage' = CASE WHEN '$homepage' IS NULL OR CHAR_LENGTH(TRIM($homepage)) = 0 THEN 'bla' ELSE '$homepage' END,
        '$phone' = CASE WHEN '$phone' IS NULL OR CHAR_LENGTH(TRIM($phone)) = 0 THEN 'bla' ELSE '$phone' END,
        '$education' = CASE WHEN '$education' IS NULL OR CHAR_LENGTH(TRIM($education)) = 0 THEN 'bla' ELSE '$education' END,
        '$profession' = CASE WHEN '$profession' IS NULL OR CHAR_LENGTH(TRIM($profession)) = 0 THEN 'bla' ELSE '$profession' END,
        '$work' = CASE WHEN '$work' IS NULL OR CHAR_LENGTH(TRIM($work)) = 0 THEN 'bla' ELSE '$work' END,
        '$facebook' = CASE WHEN '$facebook' IS NULL OR CHAR_LENGTH(TRIM($facebook)) = 0 THEN 'bla' ELSE '$facebook' END,
        '$instagram' = CASE WHEN '$instagram' IS NULL OR CHAR_LENGTH(TRIM($instagram)) = 0 THEN 'bla' ELSE '$instagram' END,
        '$twitter' = CASE WHEN '$twitter' IS NULL OR CHAR_LENGTH(TRIM($twitter)) = 0 THEN 'bla' ELSE '$twitter' END,
        '$vk' = CASE WHEN '$vk' IS NULL OR CHAR_LENGTH(TRIM($vk)) = 0 THEN 'bla' ELSE '$vk' END,
        '$bio' = CASE WHEN '$bio' IS NULL OR CHAR_LENGTH(TRIM($bio)) = 0 THEN 'bla' ELSE '$bio' END,
        '$interests' = CASE WHEN '$interests' IS NULL OR CHAR_LENGTH(TRIM($interests)) = 0 THEN 'bla' ELSE '$interests' END,

    WHERE `id` = '".$id."' ");
        $stmt->execute();

        return $stmt;
    }
    catch(PDOException $e)
    {
        echo $e->getMessage();
    }
}

EDIT: Version 3. Still same problem

public function editProfile($id,$username,$email,$password,$image,$firstname,$surname,$gender,$birthday,
        $carsowned,$homepage,$phone,$education,$profession,$work,$facebook,$instagram,$twitter,$vk,
        $bio,$interests){
    try{

            $stmt = $this->conn->prepare("UPDATE users SET
            username = IF(:username != '', :username, username),
            email = IF(:email != '', :email, email),
            password = IF(:password != '', :password, password),
                image = IF(:image != '', :image, image),
                firstname = IF(:firstname != '', :firstname, firstname),
            surname = IF(:surname != '', :surname, surname),
            gender = IF(:gender != '', :gender, gender),
                birthday = IF(:birthday != '', :birthday, birthday),
                carsowned = IF(:carsowned != '', :carsowned, carsowned),
            homepage = IF(:homepage != '', :homepage, homepage),
            phone = IF(:phone != '', :phone, phone),
                education = IF(:education != '', :education, education),
                profession = IF(:profession != '', :profession, profession),
            work = IF(:work != '', :work, work),
            facebook = IF(:facebook != '', :facebook, facebook),
                instagram = IF(:instagram != '', :instagram, instagram),
                twitter = IF(:twitter != '', :twitter, twitter),
            vk = IF(:vk != '', :vk, vk),
            bio = IF(:bio != '', :bio, bio),
                interests = IF(:interests != '', :interests, interests),
            WHERE id = :id");
            $stmt->execute();

        return $stmt;
    }
    catch(PDOException $e)
    {
        echo $e->getMessage();
    }
}

Errors in these lines:

if (move_uploaded_file($tempFile,$targetFile)) {
            $image = '../Images/Users/Gallery/' . basename($_FILES["file"]["name"]);
        }

$gender = strip_tags($_POST['gender']);

$user->editProfile($id,$username,$email,$password,$image,$firstname,$surname,$gender,$birthday,$carsowned,$homepage,$phone,$education,$profession,$work,
                        $facebook,$instagram,$twitter,$vk,$bio,$interests);

The left side of `=` has to be the column name you want to fill in, not a string. — Barmar, Sep 01 '17 at 05:11
@Barmar With second code I get this error "SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''username' = CASE WHEN 'Crelix' IS NULL OR CHAR_LENGTH(TRIM(Crelix)) = 0 THEN 'b' at line 2" — Crelix, Sep 01 '17 at 05:13
That's what I said, you can't assign to a literal string, you have to assign to a column name. — Barmar, Sep 01 '17 at 05:14
@Barmar I changed left collumn without "$" If I understood right, still same error: "'username' = CASE WHEN '$username' IS NULL OR CHAR_LENGTH(TRIM($username)) = 0 THEN 'bla' ELSE '$username' END," — Crelix, Sep 01 '17 at 05:15
the problem is the quotes, not the `$`. You can't assign to `'username'`, you have to assign to the column name `username`. — Barmar, Sep 01 '17 at 05:17
Read https://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-backticks — Barmar, Sep 01 '17 at 05:18
You also shouldn't store password in plain text, you should use `password_hash()`. — Barmar, Sep 01 '17 at 05:18
@Barmar You think like this, right? "$new_password = password_hash($password, PASSWORD_DEFAULT);" — Crelix, Sep 01 '17 at 05:24
yes, like that. There are many questions here and tutorials elsewhere about the proper way to store passwords in databases. — Barmar, Sep 01 '17 at 05:27

score 1 · Accepted Answer · answered Sep 01 '17 at 05:16

1

Use IF() expressions that return the value if it's not empty, otherwise the old value of the column.

$stmt = $this->conn->prepare("UPDATE users SET 
    username = IF(:username != '', :username, username),
    email = IF(:email != '', :email, email),
    password = IF(:password != '', :password, password),
    ...
    WHERE id = :id");

Also, it doesn't make sense to set the id column, since that's the column you're matching in the WHERE clause.

answered Sep 01 '17 at 05:16

Barmar

741,623
53
500
612

Thanks, will check it out right now. If it works, will be really gratefull. – Crelix Sep 01 '17 at 05:17
You're missing all the calls to `$stmt->bindParam()`. – Barmar Sep 01 '17 at 05:40

MySQL update function (I don't want to update blank fields, but all other)

1 Answers1