Delete Data From MySQL Using PHP​ 7
Filter

Delete Data From MySQL Using PHP​ 7

by Soeng Souy /
Share


1. database.php (connection file)

<?php
$servername = 'localhost';
$username = 'root';
$password = '';
$dbname = "db_php_test";

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);

// Check connection
if (!$conn) {
    die('Could not Connect MySQL: ' . mysqli_connect_error());
}
?>

2. insert.php (Insert form page)

<?php
include_once 'process.php';
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <title>PHP Insert</title>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css"/>
    <script src="https://code.jquery.com/jquery-3.2.1.slim.min.js"></script>
    <script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.12.3/umd/popper.min.js"></script>
    <script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0-beta.2/js/bootstrap.min.js"></script>
</head>
<body>
    <div class="container">
        <div class="shadow p-3 mb-5 bg-white rounded">
            <h3 class="mt-3">PHP Insert</h3>
            <h5 class="text-center text-success" id="message"><?= $success ?? ''; ?></h5>
            <br>
            <form class="mt-3" action="insert.php" method="post">
                <div class="form-group row">
                    <label for="first_name" class="control-label col-sm-3">First Name *</label>
                    <div class="col-sm-9">
                        <input type="text" class="form-control" id="first_name" name="first_name" required>
                    </div>
                </div>

                <div class="form-group row">
                    <label for="last_name" class="control-label col-sm-3">Last Name *</label>
                    <div class="col-sm-9">
                        <input type="text" class="form-control" id="last_name" name="last_name" required>
                    </div>
                </div>

                <div class="form-group row">
                    <label for="city_name" class="control-label col-sm-3">City Name *</label>
                    <div class="col-sm-9">
                        <select class="custom-select" id="city_name" name="city_name" required>
                            <option value="">Select city</option>
                            <option value="Tokyo">Tokyo</option>
                            <option value="Phnom Penh">Phnom Penh</option>
                            <option value="USA">USA</option>
                        </select>
                    </div>
                </div>

                <div class="form-group row">
                    <label for="email" class="control-label col-sm-3">Email *</label>
                    <div class="col-sm-9">
                        <input type="email" class="form-control" id="email" name="email" required>
                    </div>
                </div>

                <div class="form-group row">
                    <div class="col-sm-9 offset-sm-3">
                        <button type="submit" name="save" class="btn btn-primary">Save</button>
                    </div>
                </div>
            </form>
        </div>
    </div>

    <script>
        $(document).ready(function() {
            setTimeout(function() {
                $('#message').hide();
            }, 3000);
        });
    </script>
</body>
</html>

3. process.php (Insert Logic)

<?php
include_once 'database.php';
$success = "";

if (isset($_POST['save'])) {
    $first_name = mysqli_real_escape_string($conn, $_POST['first_name']);
    $last_name = mysqli_real_escape_string($conn, $_POST['last_name']);
    $city_name = mysqli_real_escape_string($conn, $_POST['city_name']);
    $email = mysqli_real_escape_string($conn, $_POST['email']);
    
    $sql = "INSERT INTO employee (first_name, last_name, city_name, email) VALUES ('$first_name', '$last_name', '$city_name', '$email')";

    if (mysqli_query($conn, $sql)) {
        $success = "Insert has been successfully!";
    } else {
        echo "Error: " . $sql . "<br>" . mysqli_error($conn);
    }

    mysqli_close($conn);
}
?>

4. update-process.php (Update logic)

<?php
include_once 'database.php';
$update_message = '';
$city_names = mysqli_query($conn, "SELECT DISTINCT city_name FROM employee");

if ($_SERVER['REQUEST_METHOD'] == 'POST') {
    $first_name = mysqli_real_escape_string($conn, $_POST['first_name']);
    $last_name = mysqli_real_escape_string($conn, $_POST['last_name']);
    $city_name = mysqli_real_escape_string($conn, $_POST['city_name']);
    $email = mysqli_real_escape_string($conn, $_POST['email']);
    $id = $_GET['id'];

    $sql = "UPDATE employee SET first_name = '$first_name', last_name = '$last_name', city_name = '$city_name', email = '$email' WHERE id = '$id'";

    if (mysqli_query($conn, $sql)) {
        $update_message = "Update has been successfully!";
    } else {
        echo "Error: " . mysqli_error($conn);
    }
}

$edit_query = mysqli_query($conn, "SELECT * FROM employee WHERE id = '" . $_GET['id'] . "'");
$employee = mysqli_fetch_assoc($edit_query);
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <title>Update Employee Data</title>
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css"/>
</head>
<body>
    <div class="container">
        <div class="shadow p-3 mb-5 bg-white rounded">
            <h3>Update Employee Data</h3>
            <h5 class="text-center text-success" id="message"><?= $update_message; ?></h5>
            <form action="" method="post">
                <div class="form-group row">
                    <label for="first_name" class="control-label col-sm-3">First Name *</label>
                    <div class="col-sm-9">
                        <input type="text" class="form-control" id="first_name" name="first_name" value="<?= $employee['first_name']; ?>" required>
                    </div>
                </div>
                <div class="form-group row">
                    <label for="last_name" class="control-label col-sm-3">Last Name *</label>
                    <div class="col-sm-9">
                        <input type="text" class="form-control" id="last_name" name="last_name" value="<?= $employee['last_name']; ?>" required>
                    </div>
                </div>
                <div class="form-group row">
                    <label for="city_name" class="control-label col-sm-3">City Name *</label>
                    <div class="col-sm-9">
                        <select class="custom-select" id="city_name" name="city_name" required>
                            <?php while ($city = mysqli_fetch_assoc($city_names)): ?>
                                <option value="<?= $city['city_name']; ?>" <?= $employee['city_name'] == $city['city_name'] ? 'selected' : ''; ?>><?= $city['city_name']; ?></option>
                            <?php endwhile; ?>
                        </select>
                    </div>
                </div>
                <div class="form-group row">
                    <label for="email" class="control-label col-sm-3">Email *</label>
                    <div class="col-sm-9">
                        <input type="email" class="form-control" id="email" name="email" value="<?= $employee['email']; ?>" required>
                    </div>
                </div>
                <div class="form-group row">
                    <div class="col-sm-9 offset-sm-3">
                        <button type="submit" class="btn btn-primary">Update</button>
                    </div>
                </div>
            </form>
        </div>
    </div>
</body>
</html>

5. delete-process.php (Delete logic)

<?php
include_once 'database.php';

if (isset($_GET['id'])) {
    $id = $_GET['id'];
    $sql = "DELETE FROM employee WHERE id = '$id'";

    if (mysqli_query($conn, $sql)) {
        header('Location: retrieve.php?message=deleted');
    } else {
        echo "Error deleting record: " . mysqli_error($conn);
    }
    mysqli_close($conn);
}
?>

6. retrieve.php (Display records with update and delete options)

<?php
include_once 'database.php';
$records = mysqli_query($conn, "SELECT * FROM employee");

$message = isset($_GET['message']) ? $_GET['message'] : '';
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <title>Retrieve Employees</title>
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css"/>
</head>
<body>
    <div class="container">
        <h3>Employee Records</h3>
        <?php if ($message): ?>
            <div class="alert alert-success"><?= ucfirst($message); ?> successfully.</div>
        <?php endif; ?>
        <table class="table table-bordered">
            <thead>
                <tr>
                    <th>ID</th>
                    <th>First Name</th>
                    <th>Last Name</th>
                    <th>City</th>
                    <th>Email</th>
                    <th>Actions</th>
                </tr>
            </thead>
            <tbody>
                <?php while ($row = mysqli_fetch_assoc($records)): ?>
                    <tr>
                        <td><?= $row['id']; ?></td>
                        <td><?= $row['first_name']; ?></td>
                        <td><?= $row['last_name']; ?></td>
                        <td><?= $row['city_name']; ?></td>
                        <td><?= $row['email']; ?></td>
                        <td>
                            <a href="update-process.php?id=<?= $row['id']; ?>">Edit</a> | 
                            <a href="delete-process.php?id=<?= $row['id']; ?>" onclick="return confirm('Are you sure?')">Delete</a>
                        </td>
                    </tr>
                <?php endwhile; ?>
            </tbody>
        </table>
    </div>
</body>
</html>

Explanation of Changes:

  1. Improved SQL injection prevention by using mysqli_real_escape_string for user inputs.

  2. Changed error handling and added better feedback messages.

  3. Updated form submission logic to work properly and redirect after operations like delete.

  4. Cleaned up HTML structure and styles for better readability.

Tag
Soeng Souy

Soeng Souy

Website that learns and reads, PHP, Framework Laravel, How to and download Admin template sample source code free.

Post a Comment

CAN FEEDBACK
close