CODEWITHSUNDEEP
phpmysql
Crelix
Crelix

Reputation: 349

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

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).

Image

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

Upvotes: 0

Views: 64

Answers (1)

Barmar
Barmar

Reputation: 780899

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.

Upvotes: 1

Related Questions