Auto Incrementing value to be assigned as input then using value to delete item from database;

Question

I am a student with a couple of weeks into PHP. What I am trying to do is Generate A table containing all users messages. Then I need a check box in each row. When the user hits the delete button rows with checkboxes marked will be deleted from table. The issue is that I can not figure out how to assign an individual value to each row of the HTML table to target with PHP. So I will only delete the rows that have been selected. I've only been able to delete all rows.

Here is what I got so far

<?php
//connect to database 
include("../partials/.connect.php");
// select rows from contacts
$query = "SELECT * FROM contacts";
 // display table headers     
echo '<form>
     <table width="100%" border="0" cellspacing="4" cellpadding="6"> 
      <tr> 
          <th class="center">ID</th> 
          <th class="center">Name</th> 
          <th class="center">Phone</th> 
          <th class="center">Email</tdclass=center> 
          <th class="center">Message</th> 
          <th class="center"> <button type="submit" name="button1">Delete Selected</button</th>
      </tr>';
// create loop to fetch all rows from DataBase
if ($result = $conn->query($query)) {
    while ($row = $result->fetch_assoc()) {
        $field1name = $row["id"];
        $field2name = $row["name"];
        $field3name = $row["phone"];
        $field4name = $row["email"];
        $field5name = $row["message"]; 
// If delete button is clicked delete user
        if(isset($_POST['button1'])) {
            $sql = "DELETE FROM contacts WHERE id=$field1name";
        
        if ($conn->query($sql) === TRUE) {
          echo "Record deleted successfully";
        } else {
          echo "Error deleting record: " . $conn->error;
        }
        }
// display table data 
        echo '<tr> 
                  <td class="center">'.$field1name.'</td> 
                  <td class="center">'.$field2name.'</td> 
                  <td class="center">'.$field3name.'</td> 
                  <td class="center">'.$field4name.'</td> 
                  <td class="center">'.$field5name.'</td>
                  <td class="center"> <input type="checkbox" id="if_checked" name="if_checked"></td>
              </tr>
              </form>';
    }
    $result->free();
} 
?>
</body>
</html>

**Warning:** You are wide open to [SQL Injections](https://php.net/manual/en/security.database.sql-injection.php) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/5741187) — Dharman, Oct 04 '21 at 09:37

score 1 · Answer 1 · answered Sep 29 '21 at 04:08

Your code is wrong, you just delete all your data along with your foreach run, so I just want to say keep learn more about programming logic since you're student

ok, for your question you have to put delete query before

$query = "SELECT * FROM contacts";

and your delete query should be based from foreach of checkbox value, and the most important thing is your checkbox name should be an array name since you will post multiple checkbox with the same name

<input type="checkbox" class="if_checked" name="if_checked[]">

and then you can use code like this

..................
include("../partials/.connect.php");

if(isset($_POST['button1'])) {
  foreach($_POST['if_checked'] as $id) {
    ......your delete query
  }
}

// select rows from contacts
$query = "SELECT * FROM contacts";
...................

score 0 · Answer 2 · answered Sep 29 '21 at 01:09

0

You can use the checkbox as an array where you will store the ids when you select checkboxes for delete, you will loop through checkboxes and will be able to delete all selected rows.

answered Sep 29 '21 at 01:09

Mainul Hasan

691
3
12

score -1 · Answer 3 · answered Sep 29 '21 at 19:53

I Was Able to solve this by storing the $row['id'] variable in the checkbox value instead of $Field1name. allowing me to specify which row to delete.

<?php
//connect to database 
include("../partials/.connect.php");


// select rows from contacts
$query = "SELECT * FROM contacts";

//if delete button is set
  if(isset($_POST['delete'])) {
     $selected = $_POST['if_checked'];
     //confirm record target
     $confirm = mysqli_query($conn, "SELECT * FROM contacts WHERE id = '$selected'") or die("Not Found");
     if(mysqli_num_rows($confirm)>0){
         //record found delete
         $delete_query = mysqli_query($conn, "DELETE from contacts where id = '$selected'")
         or die("Not Deleted");
         echo "<div><p>Record Deleted</p></div>";
     } else {
         //record not DataBase
         die("Not deleted".mysqli_error());  
  }
  }
 // display table headers     
echo '<form method="post" action="" role="form">
     <table width="100%" border="0" cellspacing="4" cellpadding="6"> 
      <tr> 
          <th class="center">ID</th> 
          <th class="center">Name</th> 
          <th class="center">Phone</th> 
          <th class="center">Email</th> 
          <th class="center">Message</th> 
          <th class="center"><input class="center" type="submit" name="delete" value="Delete"></th>
          
      </tr>';
      
// create loop to fetch all rows from DataBase
if ($result = $conn->query($query)) {
    while ($row = $result->fetch_assoc()) {
        $field1name = $row["id"];
        $field2name = $row["name"];
        $field3name = $row["phone"];
        $field4name = $row["email"];
        $field5name = $row["message"];
// If delete button is clicked delete user
     
// display table data 
        echo '<tr> 
                  <td class="center">'.$field1name.'</td> 
                  <td class="center">'.$field2name.'</td> 
                  <td class="center">'.$field3name.'</td> 
                  <td class="center">'.$field4name.'</td> 
                  <td class="center">'.$field5name.'</td>
                  <td class="center"> 
                  <input type="checkbox" id="if_checked" name="if_checked" value="'.$row['id'].'"></td>
              </tr>
              
              </form>';

               
  }
  
    $result->free();
} 
?>
</body>
</html>

Auto Incrementing value to be assigned as input then using value to delete item from database;

3 Answers3