diff options
Diffstat (limited to 'mysql-php/code')
| -rw-r--r-- | mysql-php/code/a1.php | 128 |
1 files changed, 119 insertions, 9 deletions
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(); } |