PHP MySQL row delets all in error

I've got a form with a incident issue and list of checkboxes related to users involved in the incident. The checkbox values goes into an array in the POST [userGroup].

I then need to insert all users into the involved table - works fine.

I then select all rows from the table with the incident ID and loop over them checking if they're in the array. If not - issue the SQL delete statement. The problem is that ALL rows with the incidentID ($iid) are deleted.The SQL code I'm echoing is correct though.. :(

    foreach ($_POST['userGroup'] as $val) {
        // Check if user's added already
        $sql = "SELECT userID FROM involved WHERE incidentID = $iid AND userID = $val";
        $checkSQL = mysql_query($sql) 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
    $sql = "SELECT userID, involvedID FROM involved WHERE incidentID = $iid";
    $involvedSQL = mysql_query($sql) or die(mysql_error);
        while($row = mysql_fetch_assoc($involvedSQL)) {
            $involvedID = $row['involvedID'];
            echo "<br><br>Setting involvedID " . $involvedID . "<br>";
            $userID = $row['userID'];
            echo "Setting userID " . $userID . "<br>";
            
            if (!in_array($userID, $_POST['userGroup'])) {
                // Remove user
                echo "Could not find user " . $userID . " on involvedID " . $involvedID . "<br>";
                $delsql = "DELETE FROM involved WHERE involvedID = $involvedID";
                echo "<br>" . $delsql;
                $deleteSQL = mysql_query($delsql) or die(mysql_error);
            }
        }

Open in new window

MrChrisDavidsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Chris StanyonWebDevCommented:
You need to include the userID in your Delete statement:

"DELETE FROM involved WHERE involvedID = $involvedID AND userID = $userID"
0
MrChrisDavidsAuthor Commented:
Oh come on..Obviously :D Thanks for smacking me accros the head. Not sure what I was thinking..
0
Chris StanyonWebDevCommented:
No worries - I generally need about 3 slaps a day - several more on a bad day :)
0
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

MrChrisDavidsAuthor Commented:
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.
0
Chris StanyonWebDevCommented:
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
0
MrChrisDavidsAuthor Commented:
var_dump($_POST['userGroup']

Open in new window


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

Open in new window


I'll work on getting into MySQLi once done with this project ;)
0
MrChrisDavidsAuthor Commented:
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	

Open in new window

0
Chris StanyonWebDevCommented:
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);

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

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.
0
Chris StanyonWebDevCommented:
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?
0
MrChrisDavidsAuthor Commented:
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
        

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

Open in new window

0
MrChrisDavidsAuthor Commented:
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!? ..
0
Chris StanyonWebDevCommented:
There's nothing in your code that suggest anything wrong. The output tells me that the delete queries are NOT being run. If they were, we'd get some output.

The actual code execution is not browser dependant because it's server side. You may have a difference in the POST array between browsers, but I'd be very surprised. var_dump() the full POST array.

Also, double check that you're not somehow running a cached version of the script, or even a different script altogether. Check that a cached version in Firefox isn't somehow pointing to a different file.

There's clearly something else going on here that we're not seeing - the code you've posted in isolation simply wouldn't behave in the way your describing.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MrChrisDavidsAuthor Commented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.