From 57145e7b51a682ab7fc872a373cc2d31975847cb Mon Sep 17 00:00:00 2001
From: Sudipto Mallick <smlckz@bccr>
Date: Wed, 3 Jan 2024 16:28:18 +0530
Subject: Complete the PHP assignment #1

---
 mysql-php/code/a1.php | 128 ++++++++++++++++++++++++++++++++++++++++++++++----
 1 file changed, 119 insertions(+), 9 deletions(-)

(limited to 'mysql-php/code')

diff --git a/mysql-php/code/a1.php b/mysql-php/code/a1.php
index 022b9ee..107f07a 100644
--- a/mysql-php/code/a1.php
+++ b/mysql-php/code/a1.php
@@ -33,12 +33,42 @@ form input { float: right; }
 form input[type=submit] { float: none; display: block; margin: 0 auto;  }
 form > * { display: block; padding: 0.5em; }
 table, tr, th, td { border-collapse: collapse; border: 1px solid black; }
+table.record th { text-align: left; }
 th, td { padding: 5px; }
+main ul li { list-style-type: square; }
+p.error { padding: 2em; background-color: #eeccbb; }
 </style>
 <?php
 }
 
-function display_form() {
+function display_menu() {
+    html_prologue('Student operations menu');
+    $php = $_SERVER['SCRIPT_NAME'];
+    ?>
+<main>
+    <h1>Menu of operations on records of students</h1>
+    <ul>
+        <li><a href="<?php echo $php; ?>/insert">Insert record of a student</a></li>
+        <li><a href="<?php echo $php; ?>/display">Display records of all students</a></li>
+        <li><a href="<?php echo $php; ?>/disp-dob-range">Display records of students born in between 2000-01-01 and 2005-12-31</a></li>
+        <li><a href="<?php echo $php; ?>/search">Search for the record of a student using E-mail address</a></li>
+    </ul>
+</main>
+<?php
+}
+
+function display_search_form() {
+    html_prologue('Student details');
+    ?>
+<form action="<?php echo $_SERVER['PHP_SELF']; ?>">
+    <h2>Enter E-mail address to search for the record of a student</h2>
+    <label>E-mail: <input type="email" name="email"></label>
+    <input type="submit" value="Search">
+</form>
+<?php
+}
+
+function display_insert_form() {
     html_prologue('Student details');
     ?>
 <form method="post" action="<?php echo $_SERVER['PHP_SELF']; ?>">
@@ -49,7 +79,6 @@ function display_form() {
     <label>Email: <input type="email" name="email"></label>
     <label>Date of birth: <input type="date" name="date_of_birth"></label>
     <input type="submit" value="Submit">
-    <a href="<?php echo $_SERVER['PHP_SELF']; ?>?details">Show students' details</a>
 </form>
 <?php
 }
@@ -124,16 +153,97 @@ function show_table($dbh) {
 <?php
 }
 
-if ($_SERVER['REQUEST_METHOD'] === 'POST') {
+function show_table_dob_range($dbh) {
+    if (!($result = mysqli_query($dbh, 'SELECT * FROM STUDENT 
+            WHERE YEAR(DATE_OF_BIRTH) BETWEEN 2000 AND 2005')))
+        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>
+<table>
+    <tr>
+        <th>Roll No.</th>
+        <th>Name</th>
+        <th>E-mail</th>
+        <th>City</th>
+        <th>Date of birth</th>
+    </tr><?php
+    while ($row = mysqli_fetch_assoc($result)) { ?>
+    <tr><td>
+        <?php echo implode('</td><td>', array_map('htmlspecialchars', [
+            $row['ROLL'], $row['NAME'], $row['EMAIL'], $row['CITY'],
+            $row['DATE_OF_BIRTH']		
+        ])); ?>
+    </td></tr>
+    <?php
+    } ?>
+</table>
+<?php
+}
+
+function search_student($dbh, $email) {
+    $stmt = mysqli_prepare($dbh, 'SELECT * FROM STUDENT WHERE EMAIL = ?');
+    mysqli_stmt_bind_param($stmt, 's', $email);
+    mysqli_stmt_execute($stmt);
+    $result = mysqli_stmt_get_result($stmt);
+    if (mysqli_num_rows($result) === 0) return null;
+    return mysqli_fetch_array($result);
+}
+
+function show_record($record) {
+    html_prologue('Student record');
+    ?>
+<h1>Record of the student</h1>
+<table class="record">
+    <tr><th>Roll No.</th> <td><?php echo $record['ROLL']; ?></td></tr>
+    <tr><th>Name</th> <td><?php echo $record['NAME']; ?></td></tr>
+    <tr><th>E-mail</th> <td><?php echo $record['EMAIL']; ?></td></tr>
+    <tr><th>City</th> <td><?php echo $record['CITY']; ?></td></tr>
+    <tr><th>Date of birth</th> <td><?php echo $record['DATE_OF_BIRTH']; ?></td></tr>
+</table>
+<?php
+}
+
+if (!isset($_SERVER['PATH_INFO']) || empty($_SERVER['PATH_INFO']) || $_SERVER['PATH_INFO'] == '/') {
+    display_menu();
+    die();
+}
+
+$path = ltrim($_SERVER['PATH_INFO'], '/');
+
+if ($path === 'insert') {
+    if ($_SERVER['REQUEST_METHOD'] === 'POST') {
+        $dbh = connect_to_database();
+        insert_details($dbh);
+        display_success();
+        mysqli_close($dbh);
+    } else {
+        display_insert_form();
+    }
+} elseif ($path === 'display') {
     $dbh = connect_to_database();
-    insert_details($dbh);
-    display_success();
+    show_table($dbh);
     mysqli_close($dbh);
-} elseif (isset($_SERVER['QUERY_STRING']) 
-        && $_SERVER['QUERY_STRING'] === 'details') {
+} elseif ($path === 'disp-dob-range') {
     $dbh = connect_to_database();
-    show_table($dbh);
+    show_table_dob_range($dbh);
     mysqli_close($dbh);
+} elseif ($path === 'search') {
+    if (isset($_GET['email']) && !empty($_GET['email'])) {
+        $dbh = connect_to_database();
+        if (!($record = search_student($dbh, $_GET['email']))) {
+            mysqli_close($dbh);
+            display_failure('Record not found for given E-mail: ' . htmlspecialchars($_GET['email']));
+        }
+        show_record($record);
+        mysqli_close($dbh);
+    } else {
+        display_search_form();
+    }
 } else {
-    display_form();
+    display_menu();
+    echo '<p class="error">Path <code>' . htmlspecialchars($path) . '</code> was not found.</p>';
+    die();
 }
-- 
cgit 1.4.1-2-gfad0