Update Data in MySQL Using PHP 7

Database Table (employee table):

CREATE TABLE `employee` (
  `userid` int(8) NOT NULL,
  `first_name` varchar(55) NOT NULL,
  `last_name` varchar(55) NOT NULL,
  `city_name` varchar(55) NOT NULL,
  `email` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

database.php – Database connection:

<?php
$servername = 'localhost';
$username = 'root';
$password = '';
$dbname = "db_php_test";
$conn = mysqli_connect($servername, $username, $password, $dbname);
if(!$conn) {
    die('Could not Connect MySQL: ' . mysql_error());
}
?>

Insert.php – Form for inserting new data:

<?php
include_once 'process.php';
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <title>PHP INSERT</title>
    <!-- Include necessary CSS and JS libraries here -->
</head>
<body>
    <div class="container">
        <h3 class="mt33">PHP INSERT</h3>
        <h5 class="text-center text-success" id="message"><?= $sucess; ?></h5>
        <form class="mt33" action="insert.php" method="post">
            <!-- Form fields for first_name, last_name, city_name, and email -->
            <button type="submit" id="save" name="save" class="btn btn-primary">Save</button>
        </form>
    </div>
</body>
</html>

process.php – Logic for inserting data into the database:

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

if(isset($_POST['save'])) {
    $first_name = $_POST['first_name'];
    $last_name  = $_POST['last_name'];
    $city_name  = $_POST['city_name'];
    $email      = $_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)) {
        $sucess = "Insert has been successfully!";
    } else {
        echo "Error: " . $sql . " " . mysqli_error($conn);
    }
    mysqli_close($conn);
}
?>

update-process.php – Process to update employee data:

<?php
include_once 'database.php';
$city_name = mysqli_query($conn, "SELECT DISTINCT city_name FROM employee");
$update = '';
if(count($_POST) > 0) {
    mysqli_query($conn, "UPDATE employee SET first_name='" . $_POST['first_name'] . "', last_name='" . $_POST['last_name'] . "', city_name='" . $_POST['city_name'] . "', email='" . $_POST['email'] . "' WHERE id='" . $_GET['id'] . "'");
    $update = "Update has been successfully!";
}
$edite = mysqli_query($conn, "SELECT * FROM employee WHERE id='" . $_GET['id'] . "'");
$row = mysqli_fetch_array($edite);
?>

<!-- Update Form -->
<form action="" method="post">
    <!-- Form fields for first_name, last_name, city_name, and email with pre-filled values -->
    <button type="submit" id="submit" name="submit" class="btn btn-primary">Update</button>
</form>

retrieve.php – Display employee records with update options:

<?php
include_once 'database.php';
$update = '';
$result = mysqli_query($conn, "SELECT * FROM employee");
?>
<html>
<body>
    <table>
        <thead>
            <tr>
                <th>First Name</th>
                <th>Last Name</th>
                <th>City Name</th>
                <th>Email</th>
                <th>Action</th>
            </tr>
        </thead>
        <tbody>
            <?php while($row = mysqli_fetch_array($result)) { ?>
            <tr>
                <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></td>
            </tr>
            <?php } ?>
        </tbody>
    </table>
</body>
</html>

This setup includes:

A database connection (database.php),
Insertion of employee records (process.php),
A form to update existing records (update-process.php), and
A table to display and edit records (retrieve.php).

Souy Soeng

Update Data in MySQL Using PHP 7

Post a Comment

Souy Soeng