php mysql delete row

Syed M Kashif September 04, 2021

How to delete a row in MySQL using PHP

 Welcome back, dear readers! This article is focused on deleting a single row of data in PHP. It's a simple five-step process to delete a specific row of content from our database. Before learning delete operation we must know how to insert and fetch data from the database.  Once we have fetched the data, we need to create a delete button or link with every row of the fetched data. When a user clicks on the button, the id of the row will be sent to our delete PHP page. On that page, we will use the id to run a MySQL delete query to delete a specific row of data from our database. Now Let's see the five-step process in detail.

How to delete a row in MySQL using PHP?

  • Create a delete button with every row of data fetched from the database.
  • When the delete button is clicked, warn the user with a confirmation message.
  •  Send the id of the row to the "delete.inc.php" page in the URL.
  • Fetch the id of the row available in the GET variable sent in URL as a parameter.
  • Execute the delete query to delete the specific row of data from our database.
Now let's discuss all the above-mentioned steps one by one in detail.

How to add a delete button on every row?

  • Create the "viewbook.php" page.
  • The first step, as always, is to establish connectivity with your database.
  • Now fetch the desired data from the database in a table.
  • Create a new column in your table to create your delete button.
  • Inside that column, create a button using the HTML anchor tag.
  • You can use Bootstrap "btn btn-danger" class to give it a nice red color. 

 <!--creating table -->
        <table class="table table-bordered">
          <thead class="thead">
            <!--writing column names -->
            <tr>
              <th scope="col">Title </th>
              <th scope="col">Author</th>
              <th scope="col">Description</th>
              <th scope="col">Date</th>
              <th scope="col">Delete</th>
            </tr>
          </thead>
          <tbody>
            <!-- fetching data from database -->
            <?php
            require 'includes/db.inc.php';
            $user_id = $_SESSION['id'];
            $query  = "SELECT * FROM book WHERE user_id= $user_id;";
            $result = mysqli_query($conn, $query);
            if (mysqli_num_rows($result) > 0) {
              while ($Row = mysqli_fetch_assoc($result)) {
                //displaying data in table rows dynamically
            ?>
                <tr>
                  <td><?php
                      echo $Row['title'];
                      ?></td>
                  <td><?php
                      echo $Row['author'];
                      ?></td>
                  <td><?php
                      echo $Row['description'];
                      ?></td>
                  <td><?php
                      echo $Row['date'];
                      ?></td>
                  <!-- creating delete button -->
                  <td>
                    <a class="btn btn-danger my-3">
                      Delete
                    </a>
                  </td>
                </tr>
            <?php
              }
            }
            ?>
          </tbody>
        </table>

How to show a confirmation box before deleting data?

  • Open a script tag to write a simple javascript function.
  • Create a function "DeleteConfirm" inside the script tag.
  • Use the javascript confirm() function with a proper message like "Are you sure you want to delete" inside our "DeleteConfirm" function. 
<script>
    function DeleteConfirm() {
      confirm("Are you sure to delete the record");
     }
 </script>
  • To show the confirmation box, use the "onclick" attribute and call our "DeleteConfirm" function here. 
<a class="btn btn-danger my-3" onclick="DeleteConfirm()" >
  Delete
</a>

Now when the user clicks the delete button, a confirmation message box will be displayed to the user.

How to send the id of the selected row to the "delete.inc.php" page?

  • First of all, Set the "href" attribute to the Delete page address "includes/delete.inc.php".  
  • Then write the question mark "?" to send the name-value pair through GET method. Note that the URL part before question marks is the address and the URL part after the question mark contains the data to be sent.
  • Embed PHP and Set the parameter "id" to $Row['id']. Here id is the name and $Row['id'] is its value. 
<a class="btn btn-danger my-3" onclick="DeleteConfirm()" href="includes/DeleteBook.inc.php?id=<?php echo $Row['id']; ?>">
  Delete
</a>

The complete code of "Viewbook.php" is visible below.
<!--creating table -->
        <table class="table table-bordered">
          <thead class="thead">
            <!--writing column names -->
            <tr>
              <th scope="col">Title </th>
              <th scope="col">Author</th>
              <th scope="col">Description</th>
              <th scope="col">Date</th>
              <th scope="col">Download</th>
              <th scope="col">Update</th>
              <th scope="col">Delete</th>
            </tr>
          </thead>
          <tbody>
            <!-- fetching data from database -->
            <?php
            require 'includes/db.inc.php';
            $user_id = $_SESSION['id'];
            $query  = "SELECT * FROM book WHERE user_id= $user_id;";
            $result = mysqli_query($conn, $query);
            if (mysqli_num_rows($result) > 0) {
              while ($Row = mysqli_fetch_assoc($result)) {
                //displaying data in table rows dynamically
            ?>
                <tr>
                  <td><?php
                      echo $Row['title'];
                      ?></td>
                  <td><?php
                      echo $Row['author'];
                      ?></td>
                  <td><?php
                      echo $Row['description'];
                      ?></td>
                  <td><?php
                      echo $Row['date'];
                      ?></td>
                  <td>
                    <a class="btn btn-primary my-3" href="<?php echo "uploads/" . $Row['filename']; ?>">
                      Download
                    </a>
                  </td>
                  <td>
                    <a class="btn btn-warning my-3" href="EditBook.php?id=<?php echo $Row['id']; ?>">
                      Update
                    </a>
                  </td>
                  <!-- creating delete button -->
                  <td>
                    <a class="btn btn-danger my-3" onclick="DeleteConfirm()" href="includes/DeleteBook.inc.php?id=<?php echo $Row['id']; ?>">
                      Delete
                    </a>
                  </td>
                </tr>

                <script>
                  function DeleteConfirm() {
                    confirm("Are you sure to delete the record");
                  }
                </script>
            <?php
              }
            }
            ?>
          </tbody>
        </table>


How to retrieve id from URL:

 When a user clicks the delete button, he/she will be sent to "delete.inc.php" with the parameter "id". This parameter contains the id of the selected row in the URL. It's very easy to fetch it.
  • Use $_GET superglobal variable to fetch the value of the parameter from the URL.
  • The syntax is also simple. Just write $_GET['id'] and store it in a variable. 
<?php
$id = $_GET['id'];
echo $id;
?>

How to execute the DELETE query?

  • To execute the DELETE query, again we need database connectivity. 
  • Write a delete query and use the WHERE clause to delete only the row of data selected by the user through id.
  • Use php Mysqli_query() function to execute query and store its result in $result variable.
  • Now apply a check to see if the query is executed successfully.
  • If it's successfully executed, send the user back to "viewbook.php" using the header() function.
  • But if there is an error, print an error message.
Now check out the complete code of "DeleteBook.inc.php".
<?php
require 'db.inc.php';

$id = $_GET['id'];
$query = "DELETE FROM book WHERE id = '$id';";
$result = mysqli_query($conn, $query);

if ($result) {
    mysqli_close($conn);
    header("location: ../viewbook.php");
    exit();
} else {
    echo "Error deleting record";
}
?>
that's all. We have successfully deleted a single row of data in PHP. I hope you understood the concept and found this article valuable. Here is the end of the article. We will meet again with another informative and interesting article soon. 
Tags

Post a Comment

0 Comments