11 files changed, 172 insertions, 19 deletions
diff --git a/mysql-php/code/a1t.php b/mysql-php/code/a1t.php
new file mode 100644
index 0000000..e67832a
--- /dev/null
+++ b/mysql-php/code/a1t.php
@@ -0,0 +1,44 @@
+<?php
+declare(strict_types=1);
+error_reporting(E_ALL);
+// student: name roll city email date_of_birth
+function connect_to_database() {
+    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');
+    if (!mysqli_query($dbh, 'CREATE DATABASE IF NOT EXISTS STUDENTS_DB'))
+        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
+    )'))
+        display_failure('Could not create table: ' . mysqli_error($dbh));
+    return $dbh;
+}
+
+// Generate the Typst table for records
+function show_table($result) { ?>
+#table(
+    columns: 5,
+    [*`ROLL`*], [*`NAME`*], [*`CITY`*], [*`EMAIL`*], [*`DATE_OF_BIRTH`*],
+<?php while ($row = mysqli_fetch_assoc($result)) { ?>
+    <?php echo '[`' . implode('`], [`', array_map('htmlspecialchars', [$row['ROLL'], $row['NAME'], $row['CITY'], $row['EMAIL'], $row['DATE_OF_BIRTH']])) . '`], ' . "\n"; ?>
+<?php } ?>
+)
+<?php }
+
+function show_table_normal($dbh) {
+    if(!($result = mysqli_query($dbh, 'SELECT * FROM STUDENT')))
+        display_failure('Could not perform query: ' . mysqli_error($dbh));
+    show_table($result);
+}
+
+$dbh = connect_to_database();
+show_table_normal($dbh);
+mysqli_close($dbh);
+
diff --git a/mysql-php/code/a2.php b/mysql-php/code/a2.php
index c03789f..23dd54d 100644
--- a/mysql-php/code/a2.php
+++ b/mysql-php/code/a2.php
@@ -17,17 +17,37 @@ function connect_to_database() {
         EMAIL VARCHAR(255),
         DATE_OF_BIRTH DATE
     )'))
-        display_failure('Could not create table: ' . mysqli_error($dbh));
+        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)
+        )'))
+            display_failure('Could not create `LOGIN` table: ' . mysqli_error($dbh));
     return $dbh;
 }
 
-function display_failure($reason) {
-    html_prologue('Operation failure');
-    ?>
-<h2>Operation failed</h2>
-<p>Reason: <?php echo $reason; ?></p>
-<?php
-    die();
+function check_credentials($dbh, $username, $password) {
+    if (!isset($username) || empty($username) || !isset($password) || empty($password)) 
+        return false;
+    $stmt = mysqli_prepare($dbh, 'SELECT `PASSWORD` FROM `LOGIN` WHERE `USERNAME` = ?');
+    mysqli_stmt_bind_param($stmt, 's', $username);
+    mysqli_stmt_execute($stmt);
+    $result = mysqli_stmt_get_result($stmt);
+    if (mysqli_num_rows($result) === 0) return false;
+    $record = mysqli_fetch_array($result);
+    return password_verify($username, $record['PASSWORD']);
+}
+
+function update_credentials($dbh) {
+    if (!check_credentials($dbh, $_POST['previous_username'], $_POST['previous_password'])) return false;
+    $changes = '';
+    $new_username = false;
+    if (isset($_POST['new_username']) && !empty($_POST['new_username'])) {
+        $changes .= 'SET `USERNAME` = ?';
+        $new_username = true;
+    }
+    
+    $query = 'UPDATE `LOGIN`';
 }
 
 function html_prologue($title) {
@@ -43,15 +63,31 @@ th, td { padding: 5px; }
 <?php
 }
 
+function display_failure($reason) {
+    html_prologue('Operation failure');
+    ?>
+<h2>Operation failed</h2>
+<p>Reason: <?php echo $reason; ?></p>
+<?php
+    die();
+}
+
+
+function display_success() {
+    html_prologue('Operation successful');
+    ?>
+<h2>Updation successful.</h2>
+<?php
+}
+
 function show_table($dbh) {
-    if (!($result = mysqli_query($dbh, 'SELECT * FROM STUDENT 
-            WHERE YEAR(DATE_OF_BIRTH) BETWEEN 2000 AND 2005')))
+    if (!($result = mysqli_query($dbh, 'SELECT * FROM STUDENT')))
         display_failure('Could not perform query: ' . mysqli_error($dbh));
     html_prologue('Students\' details');
     ?>
 <h2>Students' details</h2>
-<p>The students who were born in years 2000 to 2005, inclusive.</p>
 <p><?php echo mysqli_num_rows($result); ?> record(s) found.</p>
+<p><a href="<?php echo $_SERVER['PHP_SELF']; ?>?change">Change credentials</a></p>
 <table>
     <tr>
         <th>Roll No.</th>
@@ -73,6 +109,51 @@ function show_table($dbh) {
 <?php
 }
 
-$dbh = connect_to_database();
-show_table($dbh);
-mysqli_close($dbh);
+function display_login_form() {
+    html_prologue('Authorization required');
+    ?>
+<form method="post" action="<?php echo $_SERVER['PHP_SELF']; ?>">
+    <h1>Provide credentials</h1>
+    <label>Username: <input type="text" name="username"></label>
+    <label>Password: <input type="password" name="password"></label>
+    <input type="submit" value="Log in">
+</form>
+<?php
+}
+
+function display_credential_change_form() {
+    html_prologue('Change credentails');
+    ?>
+<form method="post" action="<?php echo $_SERVER['PHP_SELF']; ?>?change">
+    <h1>Change credentials</h1>
+    <p>Fields for new value can be left empty to keep the value unchanged.</p>
+    <label>Previous Username: <input type="text" name="previous_username"></label>
+    <label>New Username: <input type="text" name="new_username"></label>
+    <label>Previous Password: <input type="password" name="previous_password"></label>
+    <label>New Password: <input type="password" name="new_password"></label>
+    <label>New Password again: <input type="password" name="new_password2"></label>
+    <input type="submit" value="Change">
+</form>
+<?php
+}
+
+if ($_SERVER['REQUEST_METHOD'] === 'POST') {
+    $dbh = connect_to_database();
+    if ($_SERVER['QUERY_STRING'] === 'change') {
+        if (update_credentials($dbh)) {
+            display_success();
+        } else {
+            display_failure('Unable to update credentials');
+        }
+    } else if (check_credentials($dbh, )) {
+        show_table($dbh);
+    } else {
+        display_failure('Invalid credentials, try again');
+    }
+    mysqli_close($dbh);
+} else if ($_SERVER['QUERY_STRING'] === 'change') {
+    display_credential_change_form();
+} else {
+    display_login_form();
+}
+
diff --git a/mysql-php/output/1/1.png b/mysql-php/output/1/1.png
index a165e62..c56467b 100644
--- a/mysql-php/output/1/1.png
+++ b/mysql-php/output/1/1.png
Binary files differdiff --git a/mysql-php/output/1/2.png b/mysql-php/output/1/2.png
index 8d99484..61df4dc 100644
--- a/mysql-php/output/1/2.png
+++ b/mysql-php/output/1/2.png
Binary files differdiff --git a/mysql-php/output/1/3.png b/mysql-php/output/1/3.png
new file mode 100644
index 0000000..9830ba0
--- /dev/null
+++ b/mysql-php/output/1/3.png
Binary files differdiff --git a/mysql-php/output/1/4.png b/mysql-php/output/1/4.png
new file mode 100644
index 0000000..13d98a6
--- /dev/null
+++ b/mysql-php/output/1/4.png
Binary files differdiff --git a/mysql-php/output/1/5.png b/mysql-php/output/1/5.png
new file mode 100644
index 0000000..0b9cc15
--- /dev/null
+++ b/mysql-php/output/1/5.png
Binary files differdiff --git a/mysql-php/output/1/6.png b/mysql-php/output/1/6.png
new file mode 100644
index 0000000..4f0eb41
--- /dev/null
+++ b/mysql-php/output/1/6.png
Binary files differdiff --git a/mysql-php/output/1/7.png b/mysql-php/output/1/7.png
new file mode 100644
index 0000000..cd302a3
--- /dev/null
+++ b/mysql-php/output/1/7.png
Binary files differdiff --git a/mysql-php/text/a1.typ b/mysql-php/text/a1.typ
index efdc981..b2ae406 100644
--- a/mysql-php/text/a1.typ
+++ b/mysql-php/text/a1.typ
@@ -8,8 +8,34 @@
 ]
 
 #scos(1)[
-    === Form page
-    #align(center, image("/output/1/1.png", width: 60%))
-    === Display record
-    #image("/output/1/2.png", width: 100%)
+  === Menu page
+  #align(center, image("/output/1/1.png", width: 80%))
+  === Form page
+  #align(center, image("/output/1/2.png", width: 70%))
+  #v(4em)
+  === Display record
+  #image("/output/1/3.png", width: 80%)
+  === Display record (2000--2005)
+  #image("/output/1/4.png", width: 80%)
+  === Search record by email
+  ==== Form
+  #align(center, image("/output/1/5.png", width: 50%))
+  #v(3em)
+  ==== Record found
+  #align(center, image("/output/1/6.png", width: 70%))
+  ==== Record not found
+  #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`], 
+)
+
diff --git a/mysql-php/text/a2.typ b/mysql-php/text/a2.typ
index 989f30f..1f3c115 100644
--- a/mysql-php/text/a2.typ
+++ b/mysql-php/text/a2.typ
@@ -4,7 +4,9 @@
 #set par(leading: 0.65em)
 
 #assignment(2)[
-  Write a PHP script to insert the records in the table in Assignment 1 and display the records in a separate table of all those students who were born in between `01/01/2000` to `31/12/2005`.
+  Design a `Login` table to take `username` and `password`, and
+  - Show all records of the `Student` table for an authorized user, whose username and password exists in the `Login` table.
+  - Allow the authenticated users to change their username and password.
 ]
 
 #scos(2)[