1 files changed, 139 insertions, 0 deletions
diff --git a/mysql-php/code/a1.php b/mysql-php/code/a1.php
new file mode 100644
index 0000000..022b9ee
--- /dev/null
+++ b/mysql-php/code/a1.php
@@ -0,0 +1,139 @@
+<?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;
+}
+
+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; }
+form { margin: 2em auto; width: 20em; }
+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; }
+th, td { padding: 5px; }
+</style>
+<?php
+}
+
+function display_form() {
+    html_prologue('Student details');
+    ?>
+<form method="post" action="<?php echo $_SERVER['PHP_SELF']; ?>">
+    <h1>Enter student's details</h1>
+    <label>Name: <input type="text" name="name"></label>
+    <label>Roll No.: <input type="number" name="roll"></label>
+    <label>City: <input type="text" name="city"></label>
+    <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
+}
+
+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 check_post_vars($vardesc) {
+    $vars = [];
+    foreach ($vardesc as $name => $desc) {
+        if (!isset($_POST[$name]) || empty($_POST[$name]))
+            display_failure('The ' . $desc . ' field can not be empty');
+        $vars[$name] = $_POST[$name];
+    }
+    return $vars;
+}
+
+function insert_details($dbh) {
+    $vars = check_post_vars([
+        'name' => 'Name',
+        'email' => 'E-mail address',
+        'date_of_birth' => 'Date of birth',
+        'city' => 'City',
+        'roll' => 'Roll No.'
+    ]);
+    $stmt = mysqli_prepare($dbh, 'INSERT INTO STUDENT (
+        ROLL, NAME, EMAIL, CITY, DATE_OF_BIRTH
+    ) VALUES (?,?,?,?,?)');
+    mysqli_stmt_execute($stmt, [
+        $vars['roll'], $vars['name'], $vars['email'], $vars['city'], 
+        $vars['date_of_birth']
+    ]);
+}
+
+function show_table($dbh) {
+    $result = mysqli_query($dbh, 'SELECT * FROM STUDENT');
+    html_prologue('Students\' details');
+    ?>
+<h2>Students' details</h2>
+<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
+}
+
+if ($_SERVER['REQUEST_METHOD'] === 'POST') {
+    $dbh = connect_to_database();
+    insert_details($dbh);
+    display_success();
+    mysqli_close($dbh);
+} elseif (isset($_SERVER['QUERY_STRING']) 
+        && $_SERVER['QUERY_STRING'] === 'details') {
+    $dbh = connect_to_database();
+    show_table($dbh);
+    mysqli_close($dbh);
+} else {
+    display_form();
+}