Deleting data from database using PHP

Question

I am trying to delete data from MySQL using PHP

<?php

if (isset($_POST['delete'])) {
    $queryDelete = "Delete FROM info WHERE userID={$_POST['delete']}";

    if (!($database = mysqli_connect("localhost", "root", ""))) {
        die("Could not connect to database. </body></html>");
    }
    if (!mysqli_select_db($database, "project2")) {
        die("Could not open books database. </body></html>");
    }
    if (!(mysqli_query($database, $queryDelete))) {
        echo "<p>Could not execute query!</p>";
        die(mysqli_error($database) . "</body></html>");
    }
    mysqli_close($database);
}

this is my delete.php using it on this page

<!DOCTYPE html>
<html>
<head>
    <link rel="stylesheet" href="Style.css">
</head>
<header>
    <div>
        <p id="page">Users List</p>
        <img id="title_pic" src="images/title_pic.jpg" alt="#">
    </div>
</header>
<body>
    <?php include 'bar.php' ?>
    <?php include 'delete.php' ?>

    <br><br><br><br>
    <h1 style="color:yellow;"> List of all Users: </h1>
    <br>

    <?php

    $query = "SELECT userID, fName, email  FROM info";

    if (!($database = mysqli_connect("localhost", "root", ""))) {
        die("Could not connect to database. </body></html>");
    }
    if (!mysqli_select_db($database, "project2")) {
        die("Could not open project database. </body></html>");
    }
    if (!($result = mysqli_query($database, $query))) {
        echo "<p>Could not execute query!</p>";
        die(mysqli_error($database) . "</body></html>");
    }
    mysqli_close($database);

    while ($row = mysqli_fetch_row($result)) {
        foreach ($row as $value) {
            echo "<span style='color:white;'> $value </span>";
        }
        echo ' <form action = "delete.php" method = "POST">';
        echo '<input type="submit"  name= "delete" value="delete" class="btn">';
        echo '</form>';
        echo "<br>";
    }

    ?>
</html>

It's redirecting me to delete.php page but when I go back to the second one (Displayuser.php) all info are there and nothing is deleted I used the same technique to add info but I am having trouble to delete them from the table.

Warning: You are wide open to SQL Injections and should use parameterized prepared statements instead of manually building your queries. They are provided by PDO or by MySQLi. 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. Escaping is not enough! — Dharman
– Dharman ♦, Commented May 26, 2021 at 19:12
name= "delete" value="delete" which means that your code will generate the query userID=delete, which is invalid for a few reasons (sending the string delete instead of the id, strings need to be quoted, you should use prepared statements instead of injecting user data into your queries (not invalid, but very insecure)) — M. Eriksson
– M. Eriksson, Commented May 26, 2021 at 19:16
Then I'll recommend you to read up on prepared statements, as others have mentioned so you learn the correct way from the start. I would also suggest using PDO instead of MySQLi. It has an easier API and comes with other benefits as well. — M. Eriksson
– M. Eriksson, Commented May 26, 2021 at 19:21
If your school is teaching you to write code like this then you should RUN away. Don't ever learn to write code like this. You are only learning bad practices and how to write buggy code. Please listen to our advise and learn PHP properly — Dharman
– Dharman ♦, Commented May 26, 2021 at 19:24
I would happily flunk my class if it required me to write code like this. But I don't see how they could do that if you follow our advice. Just say that you noticed it was insecure and that you fixed that. If that doesn't work, talk to the principle and get him fired immediately. We're saying this for your and your class mates sake. — M. Eriksson
– M. Eriksson, Commented May 26, 2021 at 19:29

Dharman · Accepted Answer · 2021-05-26 19:48:19Z

Here is how your code should look like. First in your form, provide the ID of the user you want to delete. Make sure to enable mysqli error reporting and select the right database when connecting.

<?php
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$database = mysqli_connect("localhost", "root", "", 'project2');
$database->set_charset('utf8mb4'); // always set the charset

$users = $database->query("SELECT userID, fName, email  FROM info");

?>
<!DOCTYPE html>
<html>
<head>
    <link rel="stylesheet" href="Style.css">
</head>
<body>
    <header>
        <div>
            <p id="page">Users List</p>
            <img id="title_pic" src="images/title_pic.jpg" alt="#">
        </div>
    </header>
    <?php include 'bar.php' ?>
    <?php include 'delete.php' ?>
    <br><br><br><br>
    <h1 style="color:yellow;"> List of all Users: </h1>
    <br>
    <?php

    foreach ($users as $user) {
        foreach ($user as $value) {
            echo "<span style='color:white;'>'.htmlspecialchars($value).'</span>";
        }
        echo ' <form action = "delete.php" method = "POST">';
        echo '<button type="submit" name="delete" value="'.htmlspecialchars($user['userID']).'" class="btn">Delete</button>';
        echo '</form>';
        echo "<br>";
    }

    ?>
</html>

Then in your delete.php, read the POST value and delete the row with that ID using prepared statement.

<?php

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$database = mysqli_connect("localhost", "root", "", 'project2');
$database->set_charset('utf8mb4'); // always set the charset

if (isset($_POST['delete'])) {
    $stmt = $database->prepare("DELETE FROM info WHERE userID=?");
    $stmt->bind_param('s', $_POST['delete']);
    $stmt->execute();
}

Collectives™ on Stack Overflow

Deleting data from database using PHP

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related