diff options
Diffstat (limited to 'mysql-php/code')
| -rw-r--r-- | mysql-php/code/a1t.php | 44 | ||||
| -rw-r--r-- | mysql-php/code/a2.php | 109 |
2 files changed, 139 insertions, 14 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(); +} + |