<?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; }
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_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');
/* $_SERVER['PHP_SELF'] is approximately equal to $_SERVER['SCRIPT_NAME'] . $_SERVER['PATH_INFO'] */
?>
<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']; ?>">
<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">
</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_bind_param($stmt, 'sssss', $vars['roll'], $vars['name'], $vars['email'], $vars['city'], $vars['date_of_birth']);
mysqli_stmt_execute($stmt);
}
function show_table($result) { ?>
<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 show_table_normal($dbh) {
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><?php echo mysqli_num_rows($result); ?> record(s) found.</p>
<?php
show_table($result);
}
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>
<?php
show_table($result);
}
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();
show_table_normal($dbh);
mysqli_close($dbh);
} elseif ($path === 'disp-dob-range') {
$dbh = connect_to_database();
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_menu();
echo '<p class="error">Path <code>' . htmlspecialchars($path) . '</code> was not found.</p>';
die();
}