PHP & MySQL function delete unchecked categories from database problem.

Question

I have a PHP function that allows users to pick which categories there post should be displayed in. I want to be able to delete a users picked category from the database when they unchecked a check box. But everything I tried seemed to delete all the users categories or not work at all. Can someone help me with this problem?

Here is part of my PHP function that should delete unchecked categories.

for ($x = 0; $x < count($query_cat_id); $x++){
    if($query_cat_id[$x] == $cat['id']){
        echo 'checked="checked"';
    } else if(!isset($query_cat_id[$x])) {
        $mysqli = mysqli_connect("localhost", "root", "", "sitename");
        $delete_id = mysqli_query($mysqli,"DELETE FROM posts_categories WHERE post_id = '" . $post_id . "'");
        if (!$delete_id) {
            print mysqli_error($mysqli);
        }
    }
}

Answer 1

This query is deleting all categories from your post because you are telling it to with you where clause. You are essentially saying, remove every record from the table posts_categories that has a post id = $post_id. You need to add to your where clause to make it more specific.

$delete_id = mysqli_query($mysqli,"DELETE FROM posts_categories WHERE post_id = '" . $post_id . "' AND categoryID = '". $query_cat_id[$x] ."'");

This now says the same thing you did but adds an extra qualifier. Only delete rows from the table where the post_id is $post_id and the ID of the category is (value of your unchecked check box).

*Disclaimer: I am taking a guess at where you are storing your category IDs in your function($query_cat_id[$x]).*