MySQL PHP view more detailed file and form design

Step 1: Database Table (db.php)

Create the employees table with columns for id, name, position, office, age, start_date, and salary.

CREATE TABLE employees (
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    position VARCHAR(255) NOT NULL,
    office VARCHAR(255) NOT NULL,
    age VARCHAR(255) NOT NULL,
    start_date DATETIME NOT NULL,
    salary INT(10) NOT NULL
);

Step 2: Config File (config.php)

This file sets up the MySQL database connection and defines the credentials to access the database.

<?php
define('DB_SERVER', 'localhost');
define('DB_USERNAME', 'root');
define('DB_PASSWORD', '654321');
define('DB_NAME', 'name_db');

$connection = mysqli_connect(DB_SERVER, DB_USERNAME, DB_PASSWORD, DB_NAME);

if ($connection === false) {
    die("ERROR: Could not connect. " . mysqli_connect_error());
}
?>

Step 3: Index Page (index.php)

Displays a table with employee records fetched from the database. It allows viewing, updating, and deleting records.

<?php
require_once "config.php";
$sql = "SELECT * FROM employees";
if($result = mysqli_query($connection, $sql)) {
    if(mysqli_num_rows($result) > 0) {
        // Table display logic here
    } else {
        echo "<p>No records were found.</p>";
    }
}
mysqli_close($connection);
?>

Step 4: Create Record (create.php)

Allows users to create a new employee record by filling out a form. The form data is validated and then inserted into the database.

if ($_SERVER["REQUEST_METHOD"] == "POST") {
    // Input validation and data insertion logic here
}
?>

Step 5: Read Record (read.php)

Fetches and displays a single employee record based on the id passed via the URL.

if (isset($_GET["id"]) && !empty(trim($_GET["id"]))) {
    // Fetch and display record logic here
}
?>

Step 6: Error Page (error.php)

A page is displayed if there's an invalid request or a missing ID.

<div class="alert alert-danger">
    <p>Sorry, you've made an invalid request. Please <a href="index.php">go back</a> and try again.</p>
</div>

Step 7: CSS Styling (css/style.css)

Basic styling for the form and the table to make the UI visually appealing.

body {
    background-image: linear-gradient(315deg, #bdd4e7 0%, #8693ab 74%);
    font-family: 'Roboto', sans-serif;
}
.help-block {
    color: red;
}
.form-control {
    font-size: 15px;
}
.signup-form {
    padding: 30px;
    background: #fff;
    box-shadow: 0px 2px 2px rgba(0, 0, 0, 0.3);
}

Step 8: JavaScript for DataTables (js.js)

Initializes and configures the DataTable with options for exporting data and column visibility.

$(document).ready(function() {
    var table = $('#example').DataTable({
        lengthChange: false,
        buttons: ['copy', 'excel', 'pdf', 'colvis']
    });

    table.buttons().container()
        .appendTo('#example_wrapper .col-md-6:eq(0)');
});

With all the steps completed, you will have a functional CRUD application for managing employee data. The application will allow users to:

Create new employee records.
Read individual employee details.
Update existing records.
Delete records.

Additionally, you have implemented search, sorting, and export functionality using DataTables, making the app user-friendly and interactive.

Souy Soeng