summary refs log tree commit diff stats
path: root/mysql-php
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-php')
-rw-r--r--mysql-php/buildall5
-rw-r--r--mysql-php/code/a2.php24
-rw-r--r--mysql-php/code/a4.php107
-rw-r--r--mysql-php/code/a5.php188
-rw-r--r--mysql-php/contents.typ49
-rw-r--r--mysql-php/cover.tpl.typ37
-rw-r--r--mysql-php/output/2.pngbin75186 -> 0 bytes
-rw-r--r--mysql-php/output/2/1.pngbin0 -> 13870 bytes
-rw-r--r--mysql-php/output/2/2.pngbin0 -> 63112 bytes
-rw-r--r--mysql-php/output/4/1.pngbin0 -> 17641 bytes
-rw-r--r--mysql-php/output/4/2.pngbin0 -> 28914 bytes
-rw-r--r--mysql-php/output/5/1.pngbin0 -> 5705 bytes
-rw-r--r--mysql-php/output/5/2.pngbin0 -> 18989 bytes
-rw-r--r--mysql-php/output/5/3.pngbin0 -> 43525 bytes
-rw-r--r--mysql-php/output/5/4.pngbin0 -> 42575 bytes
-rw-r--r--mysql-php/prelude.typ8
-rw-r--r--mysql-php/text/a1.typ13
-rw-r--r--mysql-php/text/a2.typ8
-rw-r--r--mysql-php/text/a3.typ13
-rw-r--r--mysql-php/text/a4.typ17
-rw-r--r--mysql-php/text/a5.typ42
-rw-r--r--mysql-php/tpl.typ103
22 files changed, 546 insertions, 68 deletions
diff --git a/mysql-php/buildall b/mysql-php/buildall
new file mode 100644
index 0000000..f3035b6
--- /dev/null
+++ b/mysql-php/buildall
@@ -0,0 +1,5 @@
+#!/bin/sh
+cat prelude.typ > all.typ
+ls text/a*.typ | awk '{ printf("#include(\"%s\")\n", $1) }' >> all.typ
+typst compile --root $PWD all.typ docs/all.pdf
+pdftk docs/all.pdf cat 1 4-5 8-end output docs/myphp-all.pdf
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());
+}
+
diff --git a/mysql-php/contents.typ b/mysql-php/contents.typ
new file mode 100644
index 0000000..6f7e302
--- /dev/null
+++ b/mysql-php/contents.typ
@@ -0,0 +1,49 @@
+#import "@preview/tablex:0.0.7": tablex, cellx
+#import "/tpl.typ": apply, signature, list-of-assignments, apply-page-borders
+
+#apply(page-numbering: "(i)" , [
+  #let heading-format(content) = cellx(align: center + horizon, content)
+  #let column-alignments = (right, auto, center + horizon, center + horizon, auto)
+  #let preprocess-alist(assignment-list, last-page-number) = {
+    let index = 0
+    let last-index = assignment-list.len() - 1
+    let page-number-list = ()
+    while index < last-index {
+      let item = assignment-list.at(index)
+      let next-item = assignment-list.at(index + 1)
+      let starting-page-number = item.page-number
+      let finishing-page-number = next-item.page-number - 1
+      page-number-list.push((starting-page-number, finishing-page-number))
+      index = index + 1
+    }
+    page-number-list.push((assignment-list.at(last-index).page-number, last-page-number))
+    let new-assignment-list = ()
+    index = 0
+    for (start, end) in page-number-list {
+      let page-number = if start == end [#start] else [#start - #end]
+      let assignment = assignment-list.at(index)
+      let serial-number = [#{assignment.number}. ]
+      let description = stack(dir: ltr, v(5em), assignment.description)
+      let item = (serial-number, description, page-number, assignment.date, [])
+      new-assignment-list.push(item)
+      index = index + 1
+    }
+    new-assignment-list
+  }
+  #list-of-assignments((assignment-list, last-page-number) => {
+    counter(page).update(1)
+    align(center, [== Contents])
+    tablex(
+      columns: (3em, 1fr, 4em, 6em, 11em),
+      stroke: 1pt + gray,
+      repeat-header: true,
+      map-cols: (i, cells) => (cells.first(), ..cells.slice(1).map(cell => (..cell, align: column-alignments.at(i)))),
+      heading-format[*Sl.* \ *No.*], heading-format[*Description*], heading-format[*Page No.*], heading-format[*Date*], heading-format[*Teacher’s* \ *Signature*],
+      ..preprocess-alist(assignment-list, last-page-number).flatten(),
+    )
+    // signature()
+  })
+])
+
+#colbreak()
+
diff --git a/mysql-php/cover.tpl.typ b/mysql-php/cover.tpl.typ
new file mode 100644
index 0000000..de38f47
--- /dev/null
+++ b/mysql-php/cover.tpl.typ
@@ -0,0 +1,37 @@
+#import "@preview/tablex:0.0.7": tablex, cellx
+#import "/template.typ": apply
+
+#let cover(page-count) = apply(page-numbering: (..nums) => {}, [
+  #set align(center)
+  #v(1in)
+  #set text(font: "Hanken Grotesk")
+  #set text(size: 40pt)
+  University of ---
+  #v(0.5in)
+  #set text(size: 30pt)
+  B.Sc. Honours Semester V --- \
+  Examination 20---
+  #v(0.35in)
+  #set text(font: "Inter", size: 25pt)
+  Practical Assignments \ _on_ \ Object-oriented programming Lab \ using Java
+  #v(1in)
+  #set text(font: "Hanken Grotesk", size: 20pt)
+  #tablex(
+    columns: 2,
+    stroke: none,
+    align: left,
+    gutter: 5pt,
+    [*Roll No.:*], [---],
+    [*Registration No.:*], [---],
+    [*Subject Code:*], [---],
+    [*Paper Code:*], [---],
+    [*Number of pages:*], page-count
+  )
+])
+
+#locate(loc => { 
+  let last-page-number = counter(page).final(loc).first()
+  cover(last-page-number)
+  colbreak()
+})
+
diff --git a/mysql-php/output/2.png b/mysql-php/output/2.png
deleted file mode 100644
index b324c1c..0000000
--- a/mysql-php/output/2.png
+++ /dev/null
Binary files differdiff --git a/mysql-php/output/2/1.png b/mysql-php/output/2/1.png
new file mode 100644
index 0000000..25ce48f
--- /dev/null
+++ b/mysql-php/output/2/1.png
Binary files differdiff --git a/mysql-php/output/2/2.png b/mysql-php/output/2/2.png
new file mode 100644
index 0000000..301f91c
--- /dev/null
+++ b/mysql-php/output/2/2.png
Binary files differdiff --git a/mysql-php/output/4/1.png b/mysql-php/output/4/1.png
new file mode 100644
index 0000000..0de52dc
--- /dev/null
+++ b/mysql-php/output/4/1.png
Binary files differdiff --git a/mysql-php/output/4/2.png b/mysql-php/output/4/2.png
new file mode 100644
index 0000000..0496df4
--- /dev/null
+++ b/mysql-php/output/4/2.png
Binary files differdiff --git a/mysql-php/output/5/1.png b/mysql-php/output/5/1.png
new file mode 100644
index 0000000..0806a3a
--- /dev/null
+++ b/mysql-php/output/5/1.png
Binary files differdiff --git a/mysql-php/output/5/2.png b/mysql-php/output/5/2.png
new file mode 100644
index 0000000..ae12842
--- /dev/null
+++ b/mysql-php/output/5/2.png
Binary files differdiff --git a/mysql-php/output/5/3.png b/mysql-php/output/5/3.png
new file mode 100644
index 0000000..7dcbc0d
--- /dev/null
+++ b/mysql-php/output/5/3.png
Binary files differdiff --git a/mysql-php/output/5/4.png b/mysql-php/output/5/4.png
new file mode 100644
index 0000000..bafd7fb
--- /dev/null
+++ b/mysql-php/output/5/4.png
Binary files differdiff --git a/mysql-php/prelude.typ b/mysql-php/prelude.typ
new file mode 100644
index 0000000..ecb5543
--- /dev/null
+++ b/mysql-php/prelude.typ
@@ -0,0 +1,8 @@
+#import "@preview/tablex:0.0.7": tablex, cellx
+#import "/tpl.typ": apply, signature, list-of-assignments, apply-page-borders
+
+#include "/cover.typ"
+#include "/contents.typ"
+
+#counter(page).update(0)
+
diff --git a/mysql-php/text/a1.typ b/mysql-php/text/a1.typ
index 416d56c..f4d918b 100644
--- a/mysql-php/text/a1.typ
+++ b/mysql-php/text/a1.typ
@@ -27,15 +27,4 @@
   #align(center, image("/output/1/7.png", width: 80%))
 ]
 
-== Records in `STUDENTS_DB.STUDENT`
-#table(
-    columns: 5,
-    [*`ROLL`*], [*`NAME`*], [*`CITY`*], [*`EMAIL`*], [*`DATE_OF_BIRTH`*],
-    [`24354946`], [`Hu Shengti`], [`Chengdu`], [`hst@m15342.cn`], [`2001-05-10`], 
-    [`35464647`], [`Albert Rechtsburger`], [`Düsseldorf`], [`arb@gmx.de`], [`1999-06-17`], 
-    [`76879140`], [`Nora Akagami`], [`Nara`], [`norakiki@mail.co.jp`], [`2002-07-04`], 
-    [`78324598`], [`Grzegorz Brzezinski`], [`Łódź`], [`grzbrzski@fastmail.com`], [`1997-07-04`], 
-    [`89764243`], [`Park Jihoon`], [`Busan`], [`pjh2346@naver.com`], [`2002-10-01`], 
-    [`97381425`], [`Mark Sanders`], [`San Francisco`], [`marksanders@gmail.com`], [`2004-03-24`], 
-)
-
+#signature()
diff --git a/mysql-php/text/a2.typ b/mysql-php/text/a2.typ
index 1f3c115..869745c 100644
--- a/mysql-php/text/a2.typ
+++ b/mysql-php/text/a2.typ
@@ -10,5 +10,11 @@
 ]
 
 #scos(2)[
-    #image("/output/2.png", width: 100%)
+  #{ /* image("/output/2.png", width: 100%)*/ []}
+  === Login form
+  #image("/output/2/1.png", width: 80%)
+  === Display records on success
+  #image("/output/2/2.png", width: 90%)
 ]
+
+#signature()
diff --git a/mysql-php/text/a3.typ b/mysql-php/text/a3.typ
index 9f30e6c..4a48b48 100644
--- a/mysql-php/text/a3.typ
+++ b/mysql-php/text/a3.typ
@@ -14,15 +14,4 @@
   #align(center, image("/output/3/2.png", width: 70%))
 ]
 
-== Records in `STUDENTS_DB.STUDENT`
-#table(
-    columns: 5,
-    [*`ROLL`*], [*`NAME`*], [*`CITY`*], [*`EMAIL`*], [*`DATE_OF_BIRTH`*],
-    [`24354946`], [`Hu Shengti`], [`Chengdu`], [`hst@m15342.cn`], [`2001-05-10`], 
-    [`35464647`], [`Albert Rechtsburger`], [`Düsseldorf`], [`arb@gmx.de`], [`1999-06-17`], 
-    [`76879140`], [`Nora Akagami`], [`Nara`], [`norakiki@mail.co.jp`], [`2002-07-04`], 
-    [`78324598`], [`Grzegorz Brzezinski`], [`Łódź`], [`grzbrzski@fastmail.com`], [`1997-07-04`], 
-    [`89764243`], [`Park Jihoon`], [`Busan`], [`pjh2346@naver.com`], [`2002-10-01`], 
-    [`97381425`], [`Mark Sanders`], [`San Francisco`], [`marksanders@gmail.com`], [`2004-03-24`], 
-)
-
+#signature()
diff --git a/mysql-php/text/a4.typ b/mysql-php/text/a4.typ
new file mode 100644
index 0000000..ec5d8e6
--- /dev/null
+++ b/mysql-php/text/a4.typ
@@ -0,0 +1,17 @@
+#import "/tpl.typ": *
+#show: A => apply(A)
+#set raw(lang: "php")
+#set par(leading: 0.7em)
+
+#assignment(4)[
+  Create an HTML form to input employee salary components such as Basic Pay, House Rent Allowance (HRA) percentage, Dearness Allowance (DA) percentage, and Professional Tax. Upon submission, the PHP script should calculate the HRA, DA, and Professional Tax for each employee based on the provided percentages and store these details in a database table named `Salary`. Ensure that records in the `Salary` table contain entries only for employee names and basic pay. Finally, display the stored salary records including employee names, basic pay, calculated HRA, DA, and Professional Tax.
+]
+
+#scos(4, cont: true)[
+  === Login form
+  #image("/output/4/1.png", width: 60%)
+  === Display records
+  #image("/output/4/2.png", width: 80%)
+]
+
+#signature()
diff --git a/mysql-php/text/a5.typ b/mysql-php/text/a5.typ
new file mode 100644
index 0000000..4f63dfc
--- /dev/null
+++ b/mysql-php/text/a5.typ
@@ -0,0 +1,42 @@
+#import "@preview/tablex:0.0.6": tablex, vlinex, hlinex
+#import "/tpl.typ": *
+#show: A => apply(A)
+#set raw(lang: "php")
+#set par(leading: 0.7em)
+
+#assignment(5)[
+  Design a HTML form to take inputs from the user and store the records into a table `Employee` using a PHP script. The form should contain the following fields:
+
+  #align(center, tablex(
+    columns: 2,
+    auto-lines: false,
+    hlinex(),
+    vlinex(), vlinex(), vlinex(),
+    [*Fields*], [*Form input control*],
+    hlinex(),
+    [`Ename`], [textbox],
+    [`Address`], [textarea],
+    [`Phno`], [textbox],
+    [`Salary`], [textbox],
+    [`Category` \ (out of GEN, SC, ST, OBC)], [radio buttons],
+    [`Language`], [checkbox],
+    hlinex(),
+  ))
+  (Multiple languages have to be concatenated into a string separated by commas and then stored into the database like `Bengali, English, Hindi`.)
+
+  Once the form is submitted, the PHP script should process the input data and insert a new record into the `Employee` table in the database. Ensure that appropriate validation is implemented to handle user input correctly.
+
+  Additionally, implement functionality to display the employee records from the `Employee` table. The records should be ordered based on the user's choice of field, which can be selected from a dropdown menu containing all fields of the table. Optionally, provide a checkbox to allow the user to specify descending order for the displayed records.
+]
+
+#scos(5, cont: true)[
+  === Menu
+  #align(center, image("/output/5/1.png", width: 20%))
+  === Input Form
+  #align(center, image("/output/5/2.png", width: 30%))
+  === Display records
+  #align(center, image("/output/5/3.png", width: 90%))
+  #align(center, image("/output/5/4.png", width: 90%))
+]
+
+#signature()
diff --git a/mysql-php/tpl.typ b/mysql-php/tpl.typ
index 28c8343..b645605 100644
--- a/mysql-php/tpl.typ
+++ b/mysql-php/tpl.typ
@@ -1,55 +1,104 @@
 #import "@preview/codelst:1.0.0": sourcefile
-#let hlfile(filename) = sourcefile(read(filename), file: filename)
-#let apply(body) = {
-  let body-font-settings = (font: "Nunito Sans 10pt", size: 12pt, stretch: 75%)
-//  let body-font-settings = (font: "Hanken Grotesk", size: 12pt, stretch: 75%)
-  let page-margin = (left: 0.75in, right: 0.25in, top: 0.5in, bottom: 0.25in)
-  let margin = (left: 0.75in, right: 0.25in, top: 2em, bottom: 2em)
-  let page-frame-thickness = 1.5pt
+
+/* Highlights the source code file. */
+#let highlight-code-file(filename) = sourcefile(read(filename), file: filename)
+
+/* The state variable to indicate whether the end of an assignment is reached. */
+#let eoa = state("is-at-end-of-assignment", false)
+
+/* Updates the state variable to indicate the end of an assignment. */
+#let signature() = {
+  eoa.update(true)
+}
+
+/* Draws the signature construct at the bottom right corner in the footer of the last page of an assignment. */
+#let signature-footer(loc) = {
+  if eoa.at(loc) {
+    align(bottom + right,
+      move(dy: -4em, dx: -1em,
+        block(width: 15em)[
+          #v(3em)
+          #line(length: 100%) \
+          #v(-2.5em)
+          #align(center)[Teacher’s signature]
+        ]))
+    eoa.update(false)
+  }
+}
+
+/* Draws page border around the provided content, taking an optional function to be called at the footer. */
+#let apply-page-borders(body, font-options: (), footer-special-func: none, page-numbering: none) = {
+  let page-margin = (left: 0.75in, right: 0.25in, top: 0.25in, bottom: 0.25in)
+  let margin = (left: 0.65in, right: 0.15in, top: 1.5em, bottom: 1.5em)
+  let page-border-thickness = 1.25pt
   set page(
     margin: (..page-margin, bottom: margin.bottom + 2em),
-    numbering: "1",
-    background: align(top + start, pad(..margin, rect(width: 100%, height: 100%, stroke: page-frame-thickness + gray))),
-    footer: locate(loc => align(center, move(dy: -margin.bottom + 1em, text(..body-font-settings, size: 9pt, counter(page).display(loc.page-numbering())))))
+    numbering: if page-numbering != none { page-numbering } else { "1" },
+    background: align(top + start, pad(..margin, rect(width: 100%, height: 100%, stroke: page-border-thickness + gray, radius: 5pt))),
+    footer: locate(loc => {
+      align(center, move(dy: -margin.bottom + 1em, text(..font-options, size: 9pt, counter(page).display(loc.page-numbering()))))
+      if footer-special-func != none {
+        footer-special-func(loc)
+      }
+    })
   )
-  show: block.with(breakable: true, width: 100%, inset: page-frame-thickness + 1em)
-  
-  set text(..body-font-settings)
+  show: block.with(breakable: true, width: 100%, inset: page-border-thickness + 1em) 
+  body
+}
 
-  let code-color = rgb("#f4f4f4")
-//  show raw: set text(font: "CommitMono", size: 1.1em)
-//  show raw: set text(font: "Source Code Pro", size: 1.1em)
+#let apply(body, page-numbering: none) = {
+  let body-font-settings = (font: "Nunito Sans 10pt", size: 12pt, stretch: 75%)
+  set text(..body-font-settings)
   show raw: set text(font: "Iosevka Fixed", size: 1.1em)
   show raw.where(block: false): box.with(
-    fill: code-color,
     inset: (x: 3pt, y: 0pt),
     outset: (y: 3pt),
     radius: 2pt,
   )
   show raw.where(block: true): block.with(
-    fill: code-color,
     inset: 10pt,
     radius: 4pt,
     width: 100%,
   )
   show raw.where(block: true): it => align(left, it)
   set raw(theme: "vendor/gr.tmTheme")
-  set list(marker: ([--]))
-  set par(leading: 0.6em)
-  body
+  set list(marker: ([$square.filled.tiny$], [--]))
+  set par(leading: 0.5em)
+  apply-page-borders(body, font-options: body-font-settings, footer-special-func: signature-footer, page-numbering: page-numbering)
 }
 
-#let assignment(number, description) = align(center, [
+
+#let alist = state("assignment-list", ())
+
+#let list-of-assignments(contents) = locate(loc => {
+  let assignment-list = alist.final(loc)
+  let last-page-number = counter(page).final(loc).first()
+  contents(assignment-list, last-page-number)
+})
+
+#let list-of-dates = ([11/09/2023], [16/09/2023], [26/09/2023], [04/10/2023], [09/10/2023], [10/10/2023], [13/10/2023], [16/10/2023], [21/11/2023], [28/11/2023], [02/12/2023], [04/12/2023], [04/12/2023], [05/12/2023], [19/12/2023], [02/01/2024], [08/01/2024], [09/01/2024], [15/01/2024], [18/01/2024])
+#let assignment-dates-indices = (15, 16, 17, 18, 19)
+
+#let assignment(number, description, reduce-gap: false, pad: false) = align(center, [
 = #text(weight: 600, [Assignment #number])
-== #text(weight: 500, [Program description:]) #text(weight: 400, description)
+  #{
+    let date = list-of-dates.at(assignment-dates-indices.at(number - 1) - 1)
+    v(-1.65em)
+    align(right, [Date: #date])
+    if reduce-gap { v(-0.75em) }
+    align(left)[
+      #set par(justify: true)
+== #text(weight: 500, [Program statement:]) #text(weight: 400, description)
+    ]
+    locate(loc => alist.update(lst => (..lst, (number: number, description: description, page-number: counter(page).at(loc).first(), date: date))))
+  }
 ])
-#let objective(body) = align(center, [*Objective*: #body])
-#let oset(kind) = block(spacing: 0.6em, [===== #h(1em) #kind])
 
-#let scos(n, obody) = [
+#let scos(n, obody, cont: false) = [
   === Source Code
   #show raw: set text(size: 10pt)
-  #hlfile("/code/a" + str(n) + ".php")
+  #highlight-code-file("/code/a" + str(n) + ".php")
+  #if cont == true [ #colbreak() ]
   === Output
   #obody
 ]