Deleting record outputs else statement of else 3 times after deleting record in PHP and SQL

I am trying to delete a record in php mysql but my code jumps me to the else statement where I have “record not deleted” message printed out. Strangely it prints out 3 times. I am attempting to have it simply show an alert when the record is deleted to keep things simple. I know I am connected to the db as I can see my records on the screen.

I can see the sql id record in the address bar so I also know it is going for the correct record but on the screen, I see – Error Deleting RecordError Deleting RecordError Deleting Record

I am sure it is a simple thing but for the life of my, I just can’t seam to identify where I could have gone wrong. If anyone can help me spot the issue, I would surely appreciate it.

My code is as follows –

user-data.php

<?php require_once('protector.php'); ?>
<?php 
  $title = "Zitalk | User Data";
  include('header.php');
?>
    <main style="padding-top: 150px; text-align: center;">
    <div class="row justify-content-center">
        <div class="col-auto mb-5">
    <table class="table table-dark table-striped">
        <tr>
            <th scope="col">Id</th>
            <th scope="col">First Name</th>
            <th scope="col">Last Name</th>
            <th scope="col">Email</th>
            <th scope="col">Telephone Number</th>
            <th scope="col">Action</th>
        </tr>
        <?php
            $conn = mysqli_connect("MYDBHOST", "MYDBUSERNAME", "MYDBPASS", "MYDBNAME");
            if($conn-> connect_error) {
                die("Connection failed:". $conn-> connect_error);
            }
            $sql = "SELECT id, firstname, lastname, email, tel FROM users";
            $result = $conn->query($sql);
            if($result-> num_rows > 0) {
                while($row = $result-> fetch_assoc()) {
        ?>
                    <tr>
                      <td><?php echo $row["id"]; ?></td>
                      <td><?php echo $row["firstname"]; ?></td>
                      <td><?php echo $row["lastname"]; ?></td>
                      <td><?php echo $row["email"]; ?></td>
                      <td><?php echo $row["tel"];?></td>
                      <td>
                      <a href="deletecode.php?id=<?php echo $row['id']; ?>" class="btn btn-xs btn-danger del_btn deletebtn"  style="max-width: 75px; max-height: 50px; font-size: 12px;padding: 5px 7px;margin: 0;">DELETE</a></td>
                    <tr>
                    
               
        <?php
            }
          }
        ?>
        </table>

    </div>
   </div>

deletecode.php

<?php 
$conn = mysqli_connect("MYDBHOST", "MYDBUSERNAME", "MYDBPASS", "MYDBNAME");
if($conn-> connect_error) {
    die("Connection failed:". $conn-> connect_error);
}
$sql = "SELECT id, firstname, lastname, email, tel FROM users";
$result = $conn->query($sql);
if($result-> num_rows > 0) {
    while($row = $result-> fetch_assoc()) {
$id = $_GET['id'];
$del = mysqli_query($db, "DELETE FROM users WHERE id='$id'");
    if($del) {
        echo '<script>alert("User Deleted");</script>';
        mysqli_close($db);
        header('Location: user-data.php');
        exit;
    } else {
        echo 'Error Deleting Record';
    }
 }
}

Again, if anyone can help me out here, I would surely appreciate it. Thank you in advance.

Answer

Okay… Firstly, let’s reformat your code:

<?php 
$conn = mysqli_connect("MYDBHOST", "MYDBUSERNAME", "MYDBPASS", "MYDBNAME");

if($conn->connect_error) {
    die("Connection failed:". $conn->connect_error);
}

$sql = "SELECT id, firstname, lastname, email, tel FROM users";

$result = $conn->query($sql);

if($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        $id = $_GET['id'];
        $del = mysqli_query($db, "DELETE FROM users WHERE id='$id'");
        if($del) {
            echo '<script>alert("User Deleted");</script>';
            mysqli_close($db);
            header('Location: user-data.php');
            exit;
        } else {
            echo 'Error Deleting Record';
        }
    }
}

Basically what is happening here is that you firstly SELECT all of your users (I’m going to assume that you have four in your DB?) and then you iterate over each of them in your while loop.

In the first iteration it probably deletes the record and the script code should be output (obviously you won’t see this) without viewing source.

In every subsequent iteration the DELETE fails because the record has already been deleted. Therefore the error message is output.

N.B.

1. You will never redirect with the header function because you’ve already output something with echo.
2. You must use a prepared statement in this scenario (where you have user supplied data) to protect your DB
3. You really should be checking that your user who is doing the delete has the permission to do it (e.g. is logged in as admin); otherwise anyone could do it?
4. Most people would advise you against using a GET request for a DELETE action.
5. Most people, again, would advise you to start to learn PDO and do away with the procedural mysqli_* calls. Or, at least, use the OOP mysqli-> format.
6. You should really ensure that you enable error reporting in mysqli as well

What you actually want to do is just run the delete query and check that it completed:

<?php
$mysqli = new mysqli("MYDBHOST", "MYDBUSERNAME", "MYDBPASS", "MYDBNAME");

$sql   = "DELETE FROM users WHERE id = ?";
$query = $mysqli->prepare($sql);
$query->bind_param("i", $_GET["id"]);
$query->execute();

if ($mysqli->affected_rows) {
    header("Location:user-data.php");
    exit;
} else {
    echo "Error deleting record";
}

Preferred solution

• Using PDO to connect to the DB
• Using a form to POST the delete request
• With a check to make sure permissions are correct
• Using prepared statements

user-data.php

<?php
    require_once('protector.php');
    $title = "Zitalk | User Data";
    include('header.php');
?>
    <main style="padding-top: 150px; text-align: center;">
    <div class="row justify-content-center">
        <div class="col-auto mb-5">
    <table class="table table-dark table-striped">
        <tr>
            <th scope="col">Id</th>
            <th scope="col">First Name</th>
            <th scope="col">Last Name</th>
            <th scope="col">Email</th>
            <th scope="col">Telephone Number</th>
            <th scope="col">Action</th>
        </tr>
        <?php
            $pdo = new pdo(
                "mysql:host={$host};dbname={$database}",
                $username,
                $password,
                [
                    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                    PDO::ATTR_EMULATE_PREPARES => FALSE
                ]
            );
            $sql    = "SELECT id, firstname, lastname, email, tel FROM users";
            $result = $pdo->query($sql);
            while($row = $pdo->fetch(PDO::FETCH_ASSOC)) {
                echo <<<EOT
                <tr>
                  <td>{$row["id"]}</td>
                  <td>{$row["firstname"]}</td>
                  <td>{$row["lastname"]}</td>
                  <td>{$row["email"]}</td>
                  <td>{$row["tel"]}/td>
                  <td>
                  <form method="POST" action="deletecode.php"><button name="id" value="{$row['id']}" class="btn btn-xs btn-danger del_btn deletebtn" style="max-width: 75px; max-height: 50px; font-size: 12px;padding: 5px 7px;margin: 0;>DELETE</button></form>
                </tr>
EOT;
            }
        ?>
        </table>

    </div>
   </div>

deletecode.php

<?php
$host     = "MYDBHOST";
$username = "MYDBUSERNAME";
$password = "MYDBPASS";
$database = "MYDBNAME";

$pdo = new pdo(
    "mysql:host={$host};dbname={$database}",
    $username,
    $password,
    [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_EMULATE_PREPARES => FALSE
    ]
);

// Some form of check to ensure the "user" has permission to do this!
if (!$userHasPermission) {
    echo "ERROR: Permission denied.";
    exit;
}

$sql   = "DELETE FROM users WHERE id = ?";
$query = $pdo->prepare($sql);
$query->execute( [$_POST["id"]] );

if ($query->rowCount()) {
    header("Location:user-data.php");
    exit;
} else {
    echo "Error deleting record";
}