Mysql Keeps deleting the written ID but the ID Doesn't exist in Database

Question

I have created a HTML form where you can delete the staff just by putting the ID which is directly connected to the database.

When I put the ID first time it will delete it if its existing but even if it doesnt exist it will still say that it just got deleted even though it was never there.

Here's the PHP part of it

<?php 
    if(isset($_POST['removeemployees']))
    {
        $error = "";
        if(!isset($_POST['employeeID']))
        {

            $employeeID = "";
        }
        else
        {
            $employeeID = $_POST['employeeID'];
        }

        if(empty($employeeID))
            {
                // Empty Employee
                $error .= "employeeID Cannot be Empty";
            }

    //echo "Your Firstname is : $firstname and last name is : $lastname";

        if($error == "")
        {

            $sql = "DELETE FROM employees WHERE ID = $employeeID ";
            $result = mysqli_query($con, $sql);
            if(mysqli_affected_rows($result)  > 1)
            {
                echo "Record Deleted";
            }
            else
            {
                echo "Error Deleting record:".mysqli_error($con);
            }
            
            
        }
        else
        {
            echo $error;
        }
    }
  ?>

And here's the HTML part of it, which is simple and working okay.

<div class="removeemployee">
        <h3> Remove Employees </h3>
        <p>Employee ID</p>
        <form action="<?php echo htmlspecialchars($_SERVER["PHP_SELF"]);?>" method="POST">
        <input type="text" name="employeeID"><br>
        <br><input type="submit" name="removeemployees" value="Submit Information">
        </form>
     </div>

I' m trying to make it work like this: if the ID is existing you can delete it, if it's not existing it should say that this ID is not existing in database or something like that. At first I thought I have to collect all the data from Mysql then compare it with input ID and go from there but I'm not sure.

**Warning:** Your code is vulnerable to SQL Injection attacks. You should use parameterised queries and prepared statements to help prevent attackers from compromising your database by using malicious input values. http://bobby-tables.com gives an explanation of the risks, as well as some examples of how to write your queries safely using PHP / mysqli. **Never** insert unsanitised data directly into your SQL. The way your code is written now, someone could easily steal, incorrectly change, or even delete your data. — ADyson, Dec 19 '22 at 23:24
https://phpdelusions.net/mysqli also contains good examples of writing safe SQL using mysqli. See also the [mysqli documentation](https://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php) and this: [How can I prevent SQL injection in PHP?](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) . Parameterising your queries will also greatly reduce the risk of accidental syntax errors as a result of un-escaped or incorrectly quoted input values. If you learnt your current technique from a tutorial or book, please don't use it again. — ADyson, Dec 19 '22 at 23:24
Also, if `mysqli_affected_rows` returns 0, that isn't an error and therefore `mysqli_error` won't report anything. If it returns 0, that would be the case where there was no record to delete. — ADyson, Dec 19 '22 at 23:25
This question must be closed, because described behavior is not consistent with the code provided. This code will never "say that it just got deleted" but rather emit an error due to incorrect argument to mysqli_affected_rows(). While when fixed, it would work as intended, checking the affected rows. Though still the else branch is still incorrect, as instead of "Error Deleting record" logically it should say "No such record" — Your Common Sense, Dec 20 '22 at 05:18

score 1 · Answer 1 · edited Dec 20 '22 at 05:21

No rows to delete is not an error.

If there's an error, mysqli_execute() returns false, not a result object.

mysqli_execute() only returns a result object when the query is SELECT (or some other type that returns a result set); for modification queries it just returns true or false. The argument to mysqli_affected_rows() must be the connection, not the return value.

$sql = "DELETE FROM employees WHERE ID = ?";
$stmt = mysqli_prepare($con, $sql);
$stmt->bind_param("i", $employeeID);
$stmt->execute();
if(mysqli_affected_rows($con) > 1)
{
    echo "Record Deleted";
}
else
{
    echo "Employee ID does not exist";
}

I've also shown how to recode using a prepared statement to prevent SQL injection.

Mysql Keeps deleting the written ID but the ID Doesn't exist in Database

1 Answers1