CODEWITHSUNDEEP
phpmysqlsql
Anthony
Anthony

Reputation: 17

Deleting data from database using PHP

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.

Upvotes: 1

Views: 4890

Answers (1)

Dharman
Dharman

Reputation: 33242

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();
}

Upvotes: 1

Related Questions