• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 137
  • Last Modified:

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

0
MrChrisDavids
Asked:
MrChrisDavids
  • 7
  • 6
1 Solution
 
Chris StanyonCommented:
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 StanyonCommented:
No worries - I generally need about 3 slaps a day - several more on a bad day :)
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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 StanyonCommented:
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 StanyonCommented:
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 StanyonCommented:
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 StanyonCommented:
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now