PHP & MySQL Image deletion problem?

Question

I have this script that deletes a user's avatar image that is stored on the filesystem. In addition, the image name is stored in a MySQL database.

But for some reason the script deletes all the user's info. For example if the users_id is 3, all of the user's info like first name, last name, age and so on, are deleted as well. Basically everything is deleted including the user.

How do I fix this so only the images and image name is deleted?

Here is the code:

$user_id = '3';

if (isset($_POST['delete_image'])) { 
    $a = "SELECT * FROM users WHERE avatar = '". $avatar ."' AND user_id = '". $user_id ."'";
    $r = mysqli_query ($mysqli, $a) or trigger_error("Query: $a\n<br />MySQL Error: " . mysqli_error($mysqli));
    if ($r == TRUE) {           
        unlink("../members/" . $user_id . "/images/" . $avatar); 
        unlink("../members/" . $user_id . "/images/thumbs/" . $avatar);

        $a = "DELETE FROM users WHERE avatar = '". $avatar ."' AND user_id = '". $user_id ."'";
        $r = mysqli_query ($mysqli, $a) or trigger_error("Query: $a\n<br />MySQL Error: " . mysqli_error($mysqli));
    }
}

Answer 1

Since avatar is an attribute of the table users you may want to simply set the avatar attribute to NULL. As you were doing, you were effectively deleting the entire row.

Therefore you should be using the UPDATE operation instead of DELETE:

$a = "UPDATE users SET avatar = NULL WHERE user_id = '". $user_id ."'";

In addition, be aware that your code is vulnerable to SQL Injection. You should consider using prepared statements.

Answer 2

At the minute your sql says to delete the user where the avatar=x and the user_id=y so update instead.

$a = "UPDATE users SET avatar=NULL where user_id='". $user_id ."'";