PHP & MySQL - Deleting table rows problem

Question

Okay my script is supposed to delete a specific users case which is stored in 2 MySQL tables but for some reason when the user deletes the specific case it deletes all the users cases I only want it to delete the case the user selects. I was wondering how can I fix this problem? Thanks in advance for helping.

Here is the PHP & MySQL code.

if(isset($_POST['delete_case'])) {

$cases_ids = array();

$mysqli = mysqli_connect("localhost", "root", "", "sitename");
$dbc = mysqli_query($mysqli,"SELECT cases.*, users_cases.* FROM cases INNER JOIN users_cases ON users_cases.cases_id = cases.id WHERE users_cases.user_id='$user_id'");

if (!$dbc) {
    print mysqli_error($mysqli);
}  else {
    while($row = mysqli_fetch_array($dbc)){ 
        $cases_ids[] = $row["cases_id"];
    }
}

foreach($_POST['delete_id'] as $di) {
    if(in_array($di, $cases_ids)) {
        $mysqli = mysqli_connect("localhost", "root", "", "sitename");
        $dbc = mysqli_query($mysqli,"DELETE FROM users_cases WHERE cases_id = '$di'");

        $dbc2 = mysqli_query($mysqli,"DELETE FROM cases WHERE id = '$di'");
    }

}

}

Here is the XHTML code.

<li>
<input type="text" name="file[]" size="25" />
<input type="text" name="case[]" size="25" />
<input type="text" name="name[]" size="25" />
<input type="submit" name="delete_case" id="delete_case" value="Delete Case" />
<input type="hidden" name="delete_id[]" value="' . $row['cases_id'] . '" />
</li>

<li>
<input type="text" name="file[]" size="25" />
<input type="text" name="case[]" size="25" />
<input type="text" name="name[]" size="25" />
<input type="submit" name="delete_case" id="delete_case" value="Delete Case" />
<input type="hidden" name="delete_id[]" value="' . $row['cases_id'] . '" /> 
</li>

<li>
<input type="text" name="file[]" size="25" />
<input type="text" name="case[]" size="25" />
<input type="text" name="name[]" size="25" />
<input type="submit" name="delete_case" id="delete_case" value="Delete Case" />
<input type="hidden" name="delete_id[]" value="' . $row['cases_id'] . '" /> 
</li>

Here is the MySQL tables.

CREATE TABLE cases (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
file VARCHAR(255) NOT NULL,
case VARCHAR(255) NOT NULL,
name VARCHAR(255) NOT NULL,
PRIMARY KEY (id)
);

CREATE TABLE users_cases (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
cases_id INT UNSIGNED NOT NULL,
user_id INT UNSIGNED NOT NULL,
PRIMARY KEY (id)
);

Did you try to debug your code? What are values of all variables, queries? — Your Common Sense
– Your Common Sense, Commented Apr 29, 2010 at 6:32
@Col. Shrapnel, Yes I tried debugging the code but with no success. — oReiLLy
– oReiLLy, Commented Apr 29, 2010 at 6:34
This may be because the id of all the cases is being passed even when the user selects one case.... — SpikETidE
– SpikETidE, Commented Apr 29, 2010 at 6:41
how do I fix this problem so that only the specific case sends the id? — oReiLLy
– oReiLLy, Commented Apr 29, 2010 at 6:43
possible duplicate of stackoverflow.com/questions/2734688/… — Your Common Sense
– Your Common Sense, Commented Apr 29, 2010 at 6:45

scott · Accepted Answer · 2010-04-29 06:46:22Z

1

From first glance it's probably because all that HTML is contained in the same form. So, no matter which Delete Case button you click, every one of the delete_id[] hidden fields is going to be submitted to the server.

So you'd need to separate each row into it's own form like...

<li>
<form method="POST" action="someURL.php">
<input type="text" name="file[]" size="25" />
<input type="text" name="case[]" size="25" />
<input type="text" name="name[]" size="25" />
<input type="submit" name="delete_case" id="delete_case" value="Delete Case" />
<input type="hidden" name="delete_id[]" value="' . $row['cases_id'] . '" />
</form>
</li>

or something else like changing the Submit buttons to just plain Button and adding some javascript...

answered Apr 29, 2010 at 6:46

scott

9761 gold badge6 silver badges6 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

wimvds Over a year ago

Another alternative would be that you keep one form, with one single Delete Case button and checkboxes, so the user can check the cases he wants to remove (ie. replace <input type="hidden" name="delete_id[]" value="' . $row['cases_id'] . '" /> with <input type="checkbox" name="delete_id[]" value="' . $row['cases_id'] . '" />

SpikETidE · Accepted Answer · 2010-04-29 06:52:17Z

0

This may be because the id of all the cases is being passed even when the user selects one case....

The following may work to get only the values selected by the user...

Associate a check box with each li with an unique id
Let the user select the check box of the cases he wants to delete
When the user presses 'Delete' or something, use javascript to collect id of the cases for which the check box is checked...
Then submit these ids to the next page....

answered Apr 29, 2010 at 6:52

SpikETidE

6,97915 gold badges49 silver badges63 bronze badges

Collectives™ on Stack Overflow

PHP & MySQL - Deleting table rows problem

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related