Complete PHP assignments

3 files changed, 303 insertions, 16 deletions

diff --git a/mysql-php/code/a2.php b/mysql-php/code/a2.php
index 9d9a1a5..84a56f8 100644
--- a/mysql-php/code/a2.php
+++ b/mysql-php/code/a2.php
@@ -3,7 +3,7 @@ declare(strict_types=1);
 error_reporting(E_ALL);
 
 function connect_to_database() {
-    mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
+    # mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
     if (!($dbh = mysqli_connect('localhost', 'root', '')))
         display_failure('Could not connect to the database: ' . mysqli_connect_error($dbh));
     mysqli_set_charset($dbh, 'utf8mb4');
@@ -11,21 +11,14 @@ function connect_to_database() {
         display_failure('Could not create database: ' . mysqli_error($dbh));
     mysqli_select_db($dbh, 'STUDENTS_DB');
     if (!mysqli_query($dbh, 'CREATE TABLE IF NOT EXISTS STUDENT (
-        ROLL INT(20) PRIMARY KEY,
-        NAME VARCHAR(255),
-        CITY VARCHAR(255),
-        EMAIL VARCHAR(255),
-        DATE_OF_BIRTH DATE
-    )'))
+        ROLL INT(20) PRIMARY KEY, NAME VARCHAR(255), CITY VARCHAR(255),
+        EMAIL VARCHAR(255), DATE_OF_BIRTH DATE)'))
         display_failure('Could not create `STUDENT` table: ' . mysqli_error($dbh));
-    if (!mysqli_query($dbh, 'CREATE TABLE IF NOT EXISTS `LOGIN` (
-            `USERNAME` VARCHAR(255),
-            `PASSWORD` VARCHAR(255),
-            UNIQUE (`USERNAME`)
-        )'))
+    if (!mysqli_query($dbh, 'CREATE TABLE IF NOT EXISTS `LOGIN` (`USERNAME` VARCHAR(255), `PASSWORD` VARCHAR(255), UNIQUE (`USERNAME`))'))
             display_failure('Could not create `LOGIN` table: ' . mysqli_error($dbh));
     if (!mysqli_query($dbh, 'IF NOT EXISTS (SELECT * FROM `LOGIN` WHERE `USERNAME` = "admin") THEN
-            INSERT INTO `LOGIN` (`USERNAME`, `PASSWORD`) VALUES ("admin", "$2y$10$3cq2joFu6kEYccaTxDkRXexrsd3GAnq4rGTip9erOucM9H9E8q5ly");
+            INSERT INTO `LOGIN` (`USERNAME`, `PASSWORD`) VALUES ("admin", "$2y$10$3cq2joFu6kEYccaTxDkRXexrsd3GAnq4rGTip9erOucM9H9E8q5ly"),
+            ("user123", "$2y$10$F1gZoOfRUMcMduyokgOKcevNAZ9GXmUHrtjWoZYkgN38NJ6pKgPAC");
         END IF'))
             display_failure('Could not create `LOGIN` table: ' . mysqli_error($dbh));
     return $dbh;
@@ -45,8 +38,7 @@ function check_credentials($dbh, $username, $password) {
 function update_credentials($dbh) {
     if (!check_credentials($dbh, @$_POST['previous_username'], @$_POST['previous_password']))
         display_failure('Can not update credentials, both previous usernames and passwords need to be provided and they need to be valid.');
-    $new_username = null;
-    $new_password = null;
+    $new_username = $new_password = null;
     $successful = [];
     if (!empty($_POST['new_username']))
         $new_username = $_POST['new_username'];
@@ -61,7 +53,7 @@ function update_credentials($dbh) {
         mysqli_stmt_bind_param($stmt, 'ss', password_hash($new_password, PASSWORD_DEFAULT), $_POST['previous_username']);
         $successful['password'] = mysqli_stmt_execute($stmt);
     }
-    if ($new_username !== NULL) {
+    if ($new_username !== null) {
         $stmt = mysqli_prepare($dbh, 'UPDATE `LOGIN` SET `USERNAME` = ? WHERE `USERNAME` = ?');
         mysqli_stmt_bind_param($stmt, 'ss', $_POST['new_username'], $_POST['previous_username']);
         $successful['username'] = mysqli_stmt_execute($stmt);
diff --git a/mysql-php/code/a4.php b/mysql-php/code/a4.php
new file mode 100644
index 0000000..baa1b45
--- /dev/null
+++ b/mysql-php/code/a4.php
@@ -0,0 +1,107 @@
+<?php
+declare(strict_types=1);
+error_reporting(E_ALL);
+
+function connect_to_database() {
+    mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
+    $dbh = new mysqli('localhost', 'root', '');
+    $dbh->set_charset('utf8mb4');
+    $dbh->query('CREATE DATABASE IF NOT EXISTS COMPANY_DB');
+    $dbh->select_db('COMPANY_DB');
+    $dbh->query('CREATE TABLE IF NOT EXISTS `SALARY` (`EMP_ID` INT PRIMARY KEY AUTO_INCREMENT, `EMP_NAME` VARCHAR(255), `BASIC_PAY` DOUBLE NOT NULL, `HRA` DOUBLE, `DA` DOUBLE, `PROFESSIONAL_TAX` DOUBLE)');
+    $result = $dbh->query('SELECT COUNT(*) AS count FROM `SALARY`');
+    $row = $result->fetch_assoc();
+    if ($row['count'] == 0) {
+        $dbh->query('INSERT INTO `SALARY` (`EMP_NAME`, `BASIC_PAY`) VALUES
+            ("Yu Jae-hoon", 70000),
+            ("Meng Cilin", 65000),
+            ("Mike Smith", 60000),
+            ("Natsukashii Morimoto", 55000)
+        ');
+    }
+    return $dbh;
+}
+
+
+function html_prologue($title) {
+?><!doctype html>
+<meta charset="utf-8">
+<title><?php echo $title; ?></title>
+<style>
+body { font-family: sans-serif; font-size: 1.3rem; }
+h1 { font-size: 2rem; font-weight: 500; }
+h2 { font-size: 1.8rem; font-weight: 500; }
+form { margin: 2em auto; width: 20em; }
+form > * { padding: 0.5em; }
+table, tr, th, td { border-collapse: collapse; border: 1px solid black; }
+table.noborder, table.noborder * { border: none; }
+table.salary td:nth-child(2) { text-align: end; }
+</style>
+<?php
+}
+
+function display_failure($reason) {
+    html_prologue('Operation failure');
+    ?>
+<h2>Operation failed</h2>
+<p>Reason: <?php echo $reason; ?></p>
+<?php
+    die();
+}
+
+function display_records($dbh) {
+    html_prologue('Employee Salary Records');
+    $result = $dbh->query('SELECT * FROM `SALARY`');
+    ?>
+<h1>Employee Salary Records</h1>
+<table class="salary">
+<tr><th>Employee Name</th><th>Basic Pay</th><th>HRA</th><th>DA</th><th>Professional Tax</th></tr>
+<?php while ($row = $result->fetch_assoc()) {
+    echo '<tr><td>', $row['EMP_NAME'], '</td><td>', number_format(+$row['BASIC_PAY'], 2), '</td><td>', number_format(+$row['HRA'], 2), '</td><td>', number_format(+$row['DA'], 2), '</td><td>', number_format(+$row['PROFESSIONAL_TAX'], 2), '</td></tr>';
+} ?>
+</table>
+<?php
+}
+
+function show_salary_form() {
+    html_prologue('Enter Salary Components');
+    ?>
+<form method="post" action="<?php echo $_SERVER['PHP_SELF']; ?>">
+<h1>Enter Salary Components</h1>
+<table class="noborder">
+<tr><td><label for="hra_percentage">HRA Percentage:</label></td><td><input type="number" name="hra_percentage" id="hra_percentage" step="0.01">%</td></tr>
+<tr><td><label for="da_percentage">DA Percentage:</label></td><td><input type="number" name="da_percentage" id="da_percentage" step="0.01">%</td></tr>
+<tr><td><label for="professional_tax">Professional Tax:</label></td><td><input type="number" name="professional_tax" id="professional_tax" step="0.01">%</td></tr>
+<tr><td colspan="2"><input type="submit" value="Calculate Salary"></td></tr>
+</table>
+</form>
+    <?php
+}
+
+function calculate_salary($dbh) {
+    $hra_percentage = $_POST['hra_percentage'] ?? 0;
+    $da_percentage = $_POST['da_percentage'] ?? 0;
+    $professional_tax = $_POST['professional_tax'] ?? 0;
+    $result = $dbh->query('SELECT * FROM `SALARY`');
+    while ($row = $result->fetch_assoc()) {
+        $hra = $row['BASIC_PAY'] * ($hra_percentage / 100);
+        $da = $row['BASIC_PAY'] * ($da_percentage / 100);
+        $ptax = $row['BASIC_PAY'] * ($professional_tax / 100);
+        $stmt = $dbh->prepare('UPDATE `SALARY` SET `HRA` = ?, `DA` = ?, `PROFESSIONAL_TAX` = ? WHERE `EMP_ID` = ?');
+        $stmt->bind_param('dddi', $hra, $da, $ptax, $row['EMP_ID']);
+        $stmt->execute();
+    }
+    display_records($dbh);
+}
+
+if ($_SERVER['REQUEST_METHOD'] === 'POST') {
+    try {
+        $dbh = connect_to_database();
+        calculate_salary($dbh);
+        $dbh->close();
+    } catch (mysqli_sql_exception $e) {
+        display_failure($e->getMessage());
+    }
+} else {
+    show_salary_form();
+}
diff --git a/mysql-php/code/a5.php b/mysql-php/code/a5.php
new file mode 100644
index 0000000..e8c4284
--- /dev/null
+++ b/mysql-php/code/a5.php
@@ -0,0 +1,188 @@
+<?php
+declare(strict_types=1);
+error_reporting(E_ALL);
+
+function connect_to_database() {
+    $dbh = new mysqli('localhost', 'root', '');
+    $dbh->set_charset('utf8mb4');
+    $dbh->query('CREATE DATABASE IF NOT EXISTS EMPLOYEE_DB');
+    $dbh->select_db('EMPLOYEE_DB');
+    $dbh->query('CREATE TABLE IF NOT EXISTS `Employee` (`EID` INT PRIMARY KEY AUTO_INCREMENT, `Ename` VARCHAR(255), `Address` TEXT, `Phno` VARCHAR(20), `Salary` DECIMAL(10,2), `Category` ENUM("GEN", "SC", "ST", "OBC"), `Language` VARCHAR(255))');
+    return $dbh;
+}
+
+function html_prologue($title) {
+    ?>
+    <!DOCTYPE html>
+    <html lang="en">
+    <head>
+        <meta charset="UTF-8">
+        <meta name="viewport" content="width=device-width, initial-scale=1.0">
+        <title><?php echo $title; ?></title>
+        <style>
+            body { font-family: sans-serif; }
+            table { width: 80%; margin: 20px auto; border-collapse: collapse; }
+            th, td { border: 1px solid #ddd; padding: 8px; text-align: left; }
+            th { background-color: #f2f2f2; }
+        </style>
+    </head>
+    <body>
+    <?php
+}
+
+function display_success() {
+    html_prologue('Operation successful');
+    ?>
+<h2>Insertion successful.</h2>
+<?php
+}
+
+function display_failure($reason) {
+    html_prologue('Operation failure');
+    ?>
+<h2>Operation failed</h2>
+<p>Reason: <?php echo $reason; ?></p>
+<?php
+    die();
+}
+
+function show_employee_form() {
+    html_prologue('Employee Form');
+    ?>
+    <form method="post" action="<?php echo $_SERVER['PHP_SELF']; ?>">
+        <h2>Employee Form</h2>
+        <label for="ename">Name:</label><br>
+        <input type="text" id="ename" name="ename" required><br>
+        <label for="address">Address:</label><br>
+        <textarea id="address" name="address" required></textarea><br>
+        <label for="phno">Phone Number:</label><br>
+        <input type="tel" id="phno" name="phno" required><br>
+        <label for="salary">Salary:</label><br>
+        <input type="text" id="salary" name="salary" required><br>
+        <label for="category">Category:</label><br>
+        <input type="radio" id="gen" name="category" value="GEN" checked>
+        <label for="gen">GEN</label>
+        <input type="radio" id="sc" name="category" value="SC">
+        <label for="sc">SC</label>
+        <input type="radio" id="st" name="category" value="ST">
+        <label for="st">ST</label>
+        <input type="radio" id="obc" name="category" value="OBC">
+        <label for="obc">OBC</label><br>
+        <label for="language">Language:</label><br>
+        <input type="checkbox" id="bengali" name="language[]" value="Bengali">
+        <label for="bengali">Bengali</label>
+        <input type="checkbox" id="english" name="language[]" value="English">
+        <label for="english">English</label>
+        <input type="checkbox" id="hindi" name="language[]" value="Hindi">
+        <label for="hindi">Hindi</label><br><br>
+        <input type="submit" value="Submit">
+    </form>
+    <?php
+}
+
+function insert_employee_record($dbh) {
+    if ($_SERVER['REQUEST_METHOD'] === 'POST') {
+        $ename = $_POST['ename'];
+        $address = $_POST['address'];
+        $phno = $_POST['phno'];
+        $salary = $_POST['salary'];
+        $category = $_POST['category'];
+        $languages = implode(", ", $_POST['language']);
+        $stmt = $dbh->prepare('INSERT INTO `Employee` (`Ename`, `Address`, `Phno`, `Salary`, `Category`, `Language`) VALUES (?, ?, ?, ?, ?, ?)');
+        $stmt->bind_param('sssdss', $ename, $address, $phno, $salary, $category, $languages);
+        $stmt->execute();
+        $stmt->close();
+        display_success();
+    }
+}
+
+function display_employee_records($dbh) {
+    $order_by = isset($_POST['order_by']) ? $_POST['order_by'] : 'Ename';
+    $desc_order = isset($_POST['desc_order']);
+    $field_labels = [
+        'EID' => 'Employee ID',
+        'Ename' => 'Name',
+        'Address' => 'Address',
+        'Phno' => 'Phone Number',
+        'Salary' => 'Salary',
+        'Category' => 'Category',
+        'Language' => 'Language'
+    ];
+    $allowed_fields = array_keys($field_labels);
+    if (!in_array($order_by, $allowed_fields))
+        display_failure('Invalid order_by field.');
+    $order_clause = $order_by . ($desc_order ? ' DESC' : '');
+    $result = $dbh->query('SELECT * FROM `Employee` ORDER BY ' . $order_clause);
+    html_prologue('Employee Records');
+    ?>
+    <h2>Employee Records</h2>
+    <form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">
+        <label for="order_by">Order By:</label>
+        <select name="order_by" id="order_by"><?php
+            foreach ($field_labels as $field => $label) {
+                echo '<option value="' . $field . '"';
+                if ($order_by === $field) {
+                    echo ' selected';
+                }
+                echo '>' . $label . '</option>';
+            } ?>
+        </select>
+        <input type="checkbox" name="desc_order" id="desc_order" <?php if ($desc_order) echo 'checked'; ?>>
+        <label for="desc_order">Descending Order</label>
+        <input type="submit" value="Order">
+    </form>
+    <table>
+        <tr>
+            <?php foreach ($field_labels as $label) { ?>
+                <th><?php echo $label; ?></th>
+            <?php } ?>
+        </tr>
+        <?php while ($row = $result->fetch_assoc()) { ?>
+            <tr>
+                <?php foreach ($field_labels as $field => $label) { ?>
+                    <td><?php echo $row[$field]; ?></td>
+                <?php } ?>
+            </tr>
+        <?php } ?>
+    </table>
+    </body>
+    </html>
+    <?php
+}
+
+function display_menu() {
+    html_prologue('Home'); ?>
+    <h2>Menu</h2>
+    <ul class="menu">
+        <li><a href="<?php echo $_SERVER['PHP_SELF']; ?>/insert">Insert Employee</a></li>
+        <li><a href="<?php echo $_SERVER['PHP_SELF']; ?>/display">Display Employees</a></li>
+    </ul><?php
+}
+
+try {
+    if (!empty($_SERVER['PATH_INFO']) || $_SERVER['PATH_INFO'] !== '/') {
+        $path = $_SERVER['PATH_INFO'];
+        if ($path === '/insert') {
+            if ($_SERVER['REQUEST_METHOD'] === 'POST') {
+                $dbh = connect_to_database();
+                insert_employee_record($dbh);
+                $dbh->close();
+            } else {
+                show_employee_form();
+            }
+        } elseif ($path === '/display') {
+            $dbh = connect_to_database();
+            display_employee_records($dbh);
+            $dbh->close();
+        } else {
+            display_menu();
+            echo '<p class="error">Path <code>' . htmlspecialchars($path) . '</code> was not found.</p>';
+            die();
+        }
+    } else {
+        display_menu();
+    }
+} catch (mysqli_sql_exception $e) {
+    display_error($e->getMessage());
+}
+