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.. :(
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);
}
}
You need to include the userID in your Delete statement:
"DELETE FROM involved WHERE involvedID = $involvedID AND userID = $userID"
"DELETE FROM involved WHERE involvedID = $involvedID AND userID = $userID"
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.
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
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']
Results in:
array(3) { [0]=> string(1) "1" [1]=> string(1) "5" [2]=> string(1) "2" }
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
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:
Also, after you've run the DELETE query, check how many rows were deleted.
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.
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);
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);
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..
Output:
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
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
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!? ..
I'm guessing there's something weird going on with the POST sending the form to the server!? ..
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.
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.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial