Can't execute php to delete multiple rows in database

mrguyhawkes · May 16, 2023

I'm trying to create a page where data is being displayed from database where data was inserted by other page. My data is displayed and i made checkboxes for it to select and a button to delete them. i can select them and when i press delete i made echo when it says that record deleted. Cant figure out why it doesnt actually delete from database. I want to delete by the row id.
My index:



<?php
session_start();
    require_once 'connection.php';
    $sql = "SELECT * FROM test";                       
    $all_product = $conn->query($sql);
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <link rel="stylesheet" href="style.css"/>
</head>
<body>
    <div class="productList">
        <div class="title">
            <h1>Product List</h1>
        </div>
            <div class="button-container">
            <a href="add-product.html">
                <input type="button" class="addButton" value="ADD">
            </a>
                <input type="submit" form="list-check-box" name="delete" id="#delete-product-btn"  value="MASS DELETE" method="post">
            </div>
    </div>
    <hr class="divLine">
    <div class="show-list">
    <?php
    while($row = mysqli_fetch_assoc($all_product)){
?>
        <form class="grid-item" id="list-check-box" action="submit-copy.php" method="post">
            <div class="list" >
            <p class="sku">
                <input type="checkbox" id=".delete-checkbox" class="delete_checkbox" name="delete-product-btn[]" value="<?php $row['id'];?>">
            <?php echo $row["sku"];?></p>
            <p class="name"><?php echo $row["name"];?></p>
            <p class="price"><?php echo $row["price"];?></p>
            </div>
            <div class="size">
            <p class="size"><?php echo $row["size"];?></p>
            </div>
            <div class="weight">
            <p class="weight"><?php echo $row["weight"];?></p>
            </div>
            <div class="furniture">
            <p class="height"><?php echo $row["height"];?></p>
            <p class="width"><?php echo $row["width"];?></p>
            <p class="length"><?php echo $row["length"];?></p>
            </div>
    </form>     
<?php
    }
?>
    </div>
    <div class="footer">
        <hr class="divLine">
        <h4>Scandiweb Test assignment</h4>
    </div> 
</body>
<?php
    include_once 'submit.php';
?>
</html>

My PHP code:

<?php 
    session_start();
    $id = $_POST["id"];
    $host = "localhost";
    $dbname = "productData";
    $username = "root";
    $password = "root";
    $conn = mysqli_connect($host, $username, $password, $dbname);   
// Check connection
if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}
// sql to delete a record
$sql = "DELETE FROM test WHERE id IN($id)";
if(isset($_POST['delete'])){
    $all_id = $_POST['delete-product-btn'];   
  echo "Record deleted successfully";
} else {
  echo "Error deleting record: " . $conn->error;
}
$conn->close();
?>

Barand · May 16, 2023

Your main problem is that you create a new form for each record and all these form have the same id "list-check-box".

When you click submit, which one of these forms will be submitted?

Id's must be unique.

Drummin · May 17, 2023

Yes, as Barand pointed out you need to move the form tags out of the while loop so they also contain the submit button and from what I see they might as well go out to the body tags so there are no nesting issues with the <div> tags. Another Big issue is that you are not echoing the ID in the checkbox input. I would also remove the id=".delete-checkbox" attribute. It is not needed and as was mentioned id's needs to be unique.

Now you can't just assign the checkbox value to a variable because you are dealing with an POST array. To SEE what is being sent you can print the post like so, which will give you a good picture of what you are dealing with.

echo "<pre>";
print_r($_POST);
echo "</pre>";

Now testing looks like this.

Array
(
    [delete] => MASS DELETE
    [delete-product-btn] => Array
        (
            [0] => 3
            [1] => 4
            [2] => 6
            [3] => 7
        )

)

these array ids need to be converted into a comma separated string like 3,4,6,7 to be used in your IN() condition. Now a person could just implode this array defining the separator as a comma.

$ids = implode(',', $_POST['delete-product-btn']);

However it's a good practice to bind values being sent to a query. To do this those 3,4,6,7 values would need to be replaced with question mark placeholders. You can make an array of question marks using array_fill() which says "starting with 0 as the first KEY repeat 4 times placing a value as a question mark while making the array. But to make this dynamic we will use count($_POST['delete-product-btn']) in place of 4.

array_fill(0, count($_POST['delete-product-btn']), '?')

Which looks like this when printed.

Array
(
    [5] => ?
    [6] => ?
    [7] => ?
    [8] => ?
)

..then implode with the comma.

$placeholders = implode(',', array_fill(0, count($_POST['delete-product-btn']), '?'));

We need define the Type of values we are dealing with for each value being passed. In this case integers, which are defined as i . So we are looking for iiii, which can be made with str_repeat() again using count to defined how many i's we need.

$bindString = str_repeat("i",count($_POST['delete-product-btn']));

You can now prepare, bind and execute the query like so.

$sqlDelete = "DELETE FROM test WHERE id IN($placeholders)";	
$queryDelete = $conn->prepare($sqlDelete);	
$queryDelete->bind_param($bindString, ...$_POST['delete-product-btn']); 		
$queryDelete->execute();

If you wanted to know the number of records that were affected you could add this line right after the query.

$num = mysqli_stmt_affected_rows($queryDelete);

I would wrap all your processing code in an IF condition so it is not processed if no checkboxes are checked.

if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['delete']) && !empty($_POST['delete-product-btn'])):
	
	$placeholders = implode(',', array_fill(0, count($_POST['delete-product-btn']), '?'));
	$bindString = str_repeat("i",count($_POST['delete-product-btn']));
	
	$sqlDelete = "DELETE FROM test WHERE id IN($placeholders)";	
	$queryDelete = $conn->prepare($sqlDelete);	
	$queryDelete->bind_param($bindString, ...$_POST['delete-product-btn']); 		
	$queryDelete->execute(); 
	$num = mysqli_stmt_affected_rows($queryDelete);
		
	if(!empty($num)){
		$plural = ($num !== 1 ? 's' : '');
		$verb = ($num !== 1 ? 'Were' : 'Was');							
		echo $num.' Record'.$plural.' '.$verb.' Deleted Successfully.';
	}
	
endif;

mrguyhawkes · May 17, 2023

On 5/16/2023 at 1:33 PM, Barand said:

Your main problem is that you create a new form for each record and all these form have the same id "list-check-box".

When you click submit, which one of these forms will be submitted?

Id's must be unique.

my submit button only is meant for deletion. i need to delete selected queries that are shown in form. i thought i needed to make new form for each record but i must be wrong. so i need to create all entries to show in one form? because i need checkbox by each record so that i select multiple and when i press mass delete button i can delete those data from database

mrguyhawkes · May 18, 2023

On 5/17/2023 at 6:05 AM, AKADRUMMIN said:
Yes, as Barand pointed out you need to move the form tags out of the while loop so they also contain the submit button and from what I see they might as well go out to the body tags so there are no nesting issues with the <div> tags. Another Big issue is that you are not echoing the ID in the checkbox input. I would also remove the id=".delete-checkbox" attribute. It is not needed and as was mentioned id's needs to be unique.

Now you can't just assign the checkbox value to a variable because you are dealing with an POST array. To SEE what is being sent you can print the post like so, which will give you a good picture of what you are dealing with.
echo "<pre>";
print_r($_POST);
echo "</pre>";
Now testing looks like this.
Array
(
    [delete] => MASS DELETE
    [delete-product-btn] => Array
        (
            [0] => 3
            [1] => 4
            [2] => 6
            [3] => 7
        )

)
these array ids need to be converted into a comma separated string like 3,4,6,7 to be used in your IN() condition. Now a person could just implode this array defining the separator as a comma.
$ids = implode(',', $_POST['delete-product-btn']);
However it's a good practice to bind values being sent to a query. To do this those 3,4,6,7 values would need to be replaced with question mark placeholders. You can make an array of question marks using array_fill() which says "starting with 0 as the first KEY repeat 4 times placing a value as a question mark while making the array. But to make this dynamic we will use count($_POST['delete-product-btn']) in place of 4.
array_fill(0, count($_POST['delete-product-btn']), '?')
Which looks like this when printed.
Array
(
    [5] => ?
    [6] => ?
    [7] => ?
    [8] => ?
)
..then implode with the comma.
$placeholders = implode(',', array_fill(0, count($_POST['delete-product-btn']), '?'));
We need define the Type of values we are dealing with for each value being passed. In this case integers, which are defined as i . So we are looking for iiii, which can be made with str_repeat() again using count to defined how many i's we need.
$bindString = str_repeat("i",count($_POST['delete-product-btn']));
You can now prepare, bind and execute the query like so.
$sqlDelete = "DELETE FROM test WHERE id IN($placeholders)";	
$queryDelete = $conn->prepare($sqlDelete);	
$queryDelete->bind_param($bindString, ...$_POST['delete-product-btn']); 		
$queryDelete->execute();
If you wanted to know the number of records that were affected you could add this line right after the query.
$num = mysqli_stmt_affected_rows($queryDelete);
I would wrap all your processing code in an IF condition so it is not processed if no checkboxes are checked.
if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['delete']) && !empty($_POST['delete-product-btn'])):
	
	$placeholders = implode(',', array_fill(0, count($_POST['delete-product-btn']), '?'));
	$bindString = str_repeat("i",count($_POST['delete-product-btn']));
	
	$sqlDelete = "DELETE FROM test WHERE id IN($placeholders)";	
	$queryDelete = $conn->prepare($sqlDelete);	
	$queryDelete->bind_param($bindString, ...$_POST['delete-product-btn']); 		
	$queryDelete->execute(); 
	$num = mysqli_stmt_affected_rows($queryDelete);
		
	if(!empty($num)){
		$plural = ($num !== 1 ? 's' : '');
		$verb = ($num !== 1 ? 'Were' : 'Was');							
		echo $num.' Record'.$plural.' '.$verb.' Deleted Successfully.';
	}
	
endif;

i tried all your suggestions but all i get is a black submit-copy.php page with no source. i moved my form out the while loop. also everytime i refresh page it asks for form resubmission. i think i screwed something up.

Barand · May 18, 2023

This is my version. All code is in same file (ie the form calls itself to process the updates)

<?php
require 'db_inc.php';               // USE YOUR OWN
$conn = myConnect('db2');           // CONNECTION CODE

################################################################################
## UNCOMMENT THIS SECTION TO RECREATE TEST DATA                                #
################################################################################
/*
    $conn->query("drop table if exists test");
    $conn->query("create table test (
                      id int not null primary key,
                      descrip varchar(20)
                    )
                  ");
    for ($i=1; $i<=10; $i++) {
        $conn->query("insert into test (id, descrip) 
                        values ($i, 'Record $i')
                     ");
    }
*/
################################################################################
## HANDLE POSTED DATA TO DELETE SELECTED RECORDS                               #
################################################################################
   
    if ($_SERVER['REQUEST_METHOD'] == 'POST') {
        if (isset($_POST['del_id'])) {
            $res = $conn->prepare("DELETE FROM test
                                   WHERE id = ?
                                  ");
            $res->bind_param('i', $id);
                                  
            foreach ($_POST['del_id'] as $id) {
                $res->execute();
            }
        }
        header("Location: ?");
        exit;
    }
    
################################################################################
## GET REMAINING RECORDS AND DISPLAY                                           #
################################################################################

$tdata = '';
$res = $conn->query("SELECT id
                          , descrip
                     FROM test
                     ORDER BY id     
                     ");
foreach ($res as $r) {
    $tdata .= "<tr><td>{$r['id']}</td>
                   <td>{$r['descrip']}</td>
                   <td><input type='checkbox' name='del_id[]' value='{$r['id']}'></td>
               </tr> 
              ";
}
?>
<!DOCTYPE=html>
<html lang="en">
<head>
<meta charset='utf-8'>
<title>Example</title>
<style type='text/css'>
    th, td {
        padding: 4px 20px;
    }
    th {
        background-color: black;
        color: white;
    }
</style>
</head>
<body>
<form method='post'>
    <table border='1'>
        <tr><th>ID</th><th>Description</th><th>Select</th></tr>
        <?=$tdata?>
    </table>
    <br>
    <input type='submit' value='Delete Selected'><br>
</form>
</body>
</html>

mrguyhawkes · May 18, 2023

39 minutes ago, Barand said:

This is my version. All code is in same file (ie the form calls itself to process the updates)

<?php
require 'db_inc.php';               // USE YOUR OWN
$conn = myConnect('db2');           // CONNECTION CODE

################################################################################
## UNCOMMENT THIS SECTION TO RECREATE TEST DATA                                #
################################################################################
/*
    $conn->query("drop table if exists test");
    $conn->query("create table test (
                      id int not null primary key,
                      descrip varchar(20)
                    )
                  ");
    for ($i=1; $i<=10; $i++) {
        $conn->query("insert into test (id, descrip) 
                        values ($i, 'Record $i')
                     ");
    }
*/
################################################################################
## HANDLE POSTED DATA TO DELETE SELECTED RECORDS                               #
################################################################################
   
    if ($_SERVER['REQUEST_METHOD'] == 'POST') {
        if (isset($_POST['del_id'])) {
            $res = $conn->prepare("DELETE FROM test
                                   WHERE id = ?
                                  ");
            $res->bind_param('i', $id);
                                  
            foreach ($_POST['del_id'] as $id) {
                $res->execute();
            }
        }
        header("Location: ?");
        exit;
    }
    
################################################################################
## GET REMAINING RECORDS AND DISPLAY                                           #
################################################################################

$tdata = '';
$res = $conn->query("SELECT id
                          , descrip
                     FROM test
                     ORDER BY id     
                     ");
foreach ($res as $r) {
    $tdata .= "<tr><td>{$r['id']}</td>
                   <td>{$r['descrip']}</td>
                   <td><input type='checkbox' name='del_id[]' value='{$r['id']}'></td>
               </tr> 
              ";
}
?>
<!DOCTYPE=html>
<html lang="en">
<head>
<meta charset='utf-8'>
<title>Example</title>
<style type='text/css'>
    th, td {
        padding: 4px 20px;
    }
    th {
        background-color: black;
        color: white;
    }
</style>
</head>
<body>
<form method='post'>
    <table border='1'>
        <tr><th>ID</th><th>Description</th><th>Select</th></tr>
        <?=$tdata?>
    </table>
    <br>
    <input type='submit' value='Delete Selected'><br>
</form>
</body>
</html>

your method actually works combined with AKADRUMMIN's method. But now i have a problem. as you can see in my code i have 8 rows with information. but some rows are not inserted because when i input data in db i have to choose which product description i need so others are left aas 0 ( null). https://docs.google.com/document/d/1wu2J2Jp4KAYEVyQ6B7KSGFp_7oeDttH7DwOPLMARfws/edit . This is how my data submition form looks like. But when i look at dvd for example, it shows only the size in mb, but the fields where it shows furniture dimensions are left as zeroes. how do i filter out zeroes and only show entries with data? it should look like this https://docs.google.com/document/d/1PzYObzyNIMBnzvkg22qTTmEk7H4jSsO4P6Bj9IABa2Y/edit

Barand · May 18, 2023

Can we see your table structure and example data?

Drummin · May 19, 2023

On 5/17/2023 at 4:36 AM, mrguyhawkes said:

i thought i needed to make new form for each record but i must be wrong.

You can individually delete a single record by adding a delete button to each record using the record ID as the name array key like so. There would only be one <form> tag around all records so this could be used with your bulk delete checkboxes.

<input type="submit" name="delete[<?php echo $row['id'];?>]" value="Del">

You would then look for the POST KEY "delete" then do array_search against $_POST['delete'] for the value "Del", which will return the key for the matching value, i.e. ['record_id'] =>"Del". This can then be used to delete the record.

if ($_SERVER['REQUEST_METHOD'] === 'POST' && isset($_POST['delete'])):
	$id = array_search("Del",$_POST['delete']);
	
	$sqlDel = "DELETE FROM test WHERE id = ?";	
	$queryDel = $conn->prepare($sqlDel);	
	$queryDel->bind_param("i", $id); 		
	$queryDel->execute();
	$num = mysqli_stmt_affected_rows($queryDel);
	if(!empty($num)){	
		echo "Record was Deleted";
	}
	
endif;

Sign In

Can't execute php to delete multiple rows in database

Recommended Posts

mrguyhawkes

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

Drummin

Link to comment

Share on other sites

mrguyhawkes

Link to comment

Share on other sites

mrguyhawkes

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

mrguyhawkes

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

Drummin

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information