PHP MySQL row delets all in error

You need to include the userID in your Delete statement:

"DELETE FROM involved WHERE involvedID = $involvedID AND userID = $userID"

Oh come on..Obviously :D Thanks for smacking me accros the head. Not sure what I was thinking..

No worries - I generally need about 3 slaps a day - several more on a bad day :)

Hmm .. Actually - no dice.

Involved ID = the auto-incremented index in the table so it should be unique.

After modifying the SQL statement - I get the very same result.

OK. If the involvedID really is unique, then it's not the SQL that's failing - it's the logic surrounding it (the in_array call). Try dumping your $_POST[usergroup'] to check what's in there.

You should also seriously consider updating your code completely. The mysql_* functions are deprecated. You need to be switching to PDO or MySQLi

var_dump($_POST['userGroup']

Select all Open in new window

Results in:

array(3) { [0]=> string(1) "1" [1]=> string(1) "5" [2]=> string(1) "2" } 

Select all Open in new window

I'll work on getting into MySQLi once done with this project ;)

Checked the MySQL log and it actually does go through the delete statements..

150227 15:38:51	    9 Connect	incident@localhost on 
		    9 Init DB	incident
		    9 Query	SELECT userID, involvedID FROM involved WHERE incidentID = 100040
		    9 Query	DELETE FROM involved WHERE involvedID = 11 AND userID = 1
		    9 Query	DELETE FROM involved WHERE involvedID = 12 AND userID = 5
		    9 Query	DELETE FROM involved WHERE involvedID = 13 AND userID = 2
		    9 Quit	

Select all Open in new window

Hmmm. Can't see anything obviously wrong, so you'll want to add a couple of lines in to help you debug.

Firstly, make sure all errors are reported. Add this right at the top of your script:

error_reporting(E_ALL);
ini_set('display_errors', 1);

Select all Open in new window

Also, after you've run the DELETE query, check how many rows were deleted.

$deleteSQL = mysql_query($delsql) or die(mysql_error);
printf("Rows Deleted: %u (involvedID = %s)", mysql_affected_rows(), $involvedID);

Select all Open in new window

The output should only ever show Rows Deleted: 1, and each delete will also show the involvedID that was deleted.

Can be a little tricky for us to track down because we can't be sure of your data integrity, but these tips should give you a clearer picture of what's going on.

Your SQL Log shows me that the correct records and only those records are being deleted. Is there a chance the wrong records are being deleted elsewhere in your code?

There's no other code - that's the wierd part. Well - nothing touching SQL anyway.

I added the lines you mentioned, but there's no output change. It's almost like the deleteSQL gets executed even though it's not entering the statement? I've added some echos in order to see if there's anything going on..

    var_dump($_POST['userGroup']);

error_reporting(E_ALL);
ini_set('display_errors', 1);
        
    foreach ($_POST['userGroup'] as $val) {
        // Check if user's added already
        $preCheckSql = "SELECT userID FROM involved WHERE incidentID = $iid AND userID = $val";
        $checkSQL = mysql_query($preCheckSql) or die(mysql_error);
        $checkRows = mysql_num_rows($checkSQL); // if == 1, user is added
        if($checkRows == 0) {
            // Add user
            $sql = "INSERT INTO involved ( incidentID, userID ) VALUES ( $iid, $val )"; 
            $insertUser = mysql_query($sql);
        };
    };

    // Check if user is added but should not be
    $preAddSql = "SELECT userID, involvedID FROM involved WHERE incidentID = $iid";
    $involvedSQL = mysql_query($preAddSql) or die(mysql_error);
        while($row = mysql_fetch_assoc($involvedSQL)) {
           
            $involvedID = $row['involvedID'];
            $userID = $row['userID'];

            echo "<br><br>Setting involvedID " . $involvedID . "<br>";
            echo "Setting userID " . $userID . "<br>";
            
            if ( in_array( $userID, $_POST['userGroup'] ) ) {
                echo "Nothing to do with $userID - it's supposed to be there";// Do nothing
            } else {
                // Remove user
                echo "Could not find user " . $userID . " from involvedID " . $involvedID . "<br>";
                $delsql = "DELETE FROM involved WHERE involvedID = $involvedID AND userID = $userID";
                echo "<br>SQL string to execute: " . $delsql;
                $deleteSQL = mysql_query($delsql) or die(mysql_error);
                printf("Rows Deleted: %u (involvedID = %s)", mysql_affected_rows(), $involvedID);
            }; // end if in array
        }; // end while loop
        

Select all Open in new window

Output:

array(3) { [0]=> string(1) "1" [1]=> string(1) "5" [2]=> string(1) "2" }

Setting involvedID 35
Setting userID 1
Nothing to do with 1 - it's supposed to be there

Setting involvedID 36
Setting userID 5
Nothing to do with 5 - it's supposed to be there

Setting involvedID 37
Setting userID 2
Nothing to do with 2 - it's supposed to be there

Select all Open in new window

Hmm .. Not sure, at all, what's going on here. I switched testing from Firefox to Chrome and it's working fine in Chrome. If I run the page in Firefox, everything get's deleted again after the INSERT statements.

I'm guessing there's something weird going on with the POST sending the form to the server!? ..

Seems to have been an issue with the server (caching?). I've switched to Apache from IIS and it's working correctly all the time now - no matter what browser.