diff options
Diffstat (limited to 'mysql-php/code')
| -rw-r--r-- | mysql-php/code/a2.php | 24 | ||||
| -rw-r--r-- | mysql-php/code/a4.php | 107 | ||||
| -rw-r--r-- | mysql-php/code/a5.php | 188 |
3 files changed, 303 insertions, 16 deletions
diff --git a/mysql-php/code/a2.php b/mysql-php/code/a2.php index 9d9a1a5..84a56f8 100644 --- a/mysql-php/code/a2.php +++ b/mysql-php/code/a2.php @@ -3,7 +3,7 @@ declare(strict_types=1); error_reporting(E_ALL); function connect_to_database() { - mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT); + # 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'); @@ -11,21 +11,14 @@ function connect_to_database() { 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 - )')) + ROLL INT(20) PRIMARY KEY, NAME VARCHAR(255), CITY VARCHAR(255), + EMAIL VARCHAR(255), DATE_OF_BIRTH DATE)')) 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), - UNIQUE (`USERNAME`) - )')) + if (!mysqli_query($dbh, 'CREATE TABLE IF NOT EXISTS `LOGIN` (`USERNAME` VARCHAR(255), `PASSWORD` VARCHAR(255), UNIQUE (`USERNAME`))')) display_failure('Could not create `LOGIN` table: ' . mysqli_error($dbh)); if (!mysqli_query($dbh, 'IF NOT EXISTS (SELECT * FROM `LOGIN` WHERE `USERNAME` = "admin") THEN - INSERT INTO `LOGIN` (`USERNAME`, `PASSWORD`) VALUES ("admin", "$2y$10$3cq2joFu6kEYccaTxDkRXexrsd3GAnq4rGTip9erOucM9H9E8q5ly"); + INSERT INTO `LOGIN` (`USERNAME`, `PASSWORD`) VALUES ("admin", "$2y$10$3cq2joFu6kEYccaTxDkRXexrsd3GAnq4rGTip9erOucM9H9E8q5ly"), + ("user123", "$2y$10$F1gZoOfRUMcMduyokgOKcevNAZ9GXmUHrtjWoZYkgN38NJ6pKgPAC"); END IF')) display_failure('Could not create `LOGIN` table: ' . mysqli_error($dbh)); return $dbh; @@ -45,8 +38,7 @@ function check_credentials($dbh, $username, $password) { function update_credentials($dbh) { if (!check_credentials($dbh, @$_POST['previous_username'], @$_POST['previous_password'])) display_failure('Can not update credentials, both previous usernames and passwords need to be provided and they need to be valid.'); - $new_username = null; - $new_password = null; + $new_username = $new_password = null; $successful = []; if (!empty($_POST['new_username'])) $new_username = $_POST['new_username']; @@ -61,7 +53,7 @@ function update_credentials($dbh) { mysqli_stmt_bind_param($stmt, 'ss', password_hash($new_password, PASSWORD_DEFAULT), $_POST['previous_username']); $successful['password'] = mysqli_stmt_execute($stmt); } - if ($new_username !== NULL) { + if ($new_username !== null) { $stmt = mysqli_prepare($dbh, 'UPDATE `LOGIN` SET `USERNAME` = ? WHERE `USERNAME` = ?'); mysqli_stmt_bind_param($stmt, 'ss', $_POST['new_username'], $_POST['previous_username']); $successful['username'] = mysqli_stmt_execute($stmt); diff --git a/mysql-php/code/a4.php b/mysql-php/code/a4.php new file mode 100644 index 0000000..baa1b45 --- /dev/null +++ b/mysql-php/code/a4.php @@ -0,0 +1,107 @@ +<?php +declare(strict_types=1); +error_reporting(E_ALL); + +function connect_to_database() { + mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT); + $dbh = new mysqli('localhost', 'root', ''); + $dbh->set_charset('utf8mb4'); + $dbh->query('CREATE DATABASE IF NOT EXISTS COMPANY_DB'); + $dbh->select_db('COMPANY_DB'); + $dbh->query('CREATE TABLE IF NOT EXISTS `SALARY` (`EMP_ID` INT PRIMARY KEY AUTO_INCREMENT, `EMP_NAME` VARCHAR(255), `BASIC_PAY` DOUBLE NOT NULL, `HRA` DOUBLE, `DA` DOUBLE, `PROFESSIONAL_TAX` DOUBLE)'); + $result = $dbh->query('SELECT COUNT(*) AS count FROM `SALARY`'); + $row = $result->fetch_assoc(); + if ($row['count'] == 0) { + $dbh->query('INSERT INTO `SALARY` (`EMP_NAME`, `BASIC_PAY`) VALUES + ("Yu Jae-hoon", 70000), + ("Meng Cilin", 65000), + ("Mike Smith", 60000), + ("Natsukashii Morimoto", 55000) + '); + } + 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; } +h2 { font-size: 1.8rem; font-weight: 500; } +form { margin: 2em auto; width: 20em; } +form > * { padding: 0.5em; } +table, tr, th, td { border-collapse: collapse; border: 1px solid black; } +table.noborder, table.noborder * { border: none; } +table.salary td:nth-child(2) { text-align: end; } +</style> +<?php +} + +function display_failure($reason) { + html_prologue('Operation failure'); + ?> +<h2>Operation failed</h2> +<p>Reason: <?php echo $reason; ?></p> +<?php + die(); +} + +function display_records($dbh) { + html_prologue('Employee Salary Records'); + $result = $dbh->query('SELECT * FROM `SALARY`'); + ?> +<h1>Employee Salary Records</h1> +<table class="salary"> +<tr><th>Employee Name</th><th>Basic Pay</th><th>HRA</th><th>DA</th><th>Professional Tax</th></tr> +<?php while ($row = $result->fetch_assoc()) { + echo '<tr><td>', $row['EMP_NAME'], '</td><td>', number_format(+$row['BASIC_PAY'], 2), '</td><td>', number_format(+$row['HRA'], 2), '</td><td>', number_format(+$row['DA'], 2), '</td><td>', number_format(+$row['PROFESSIONAL_TAX'], 2), '</td></tr>'; +} ?> +</table> +<?php +} + +function show_salary_form() { + html_prologue('Enter Salary Components'); + ?> +<form method="post" action="<?php echo $_SERVER['PHP_SELF']; ?>"> +<h1>Enter Salary Components</h1> +<table class="noborder"> +<tr><td><label for="hra_percentage">HRA Percentage:</label></td><td><input type="number" name="hra_percentage" id="hra_percentage" step="0.01">%</td></tr> +<tr><td><label for="da_percentage">DA Percentage:</label></td><td><input type="number" name="da_percentage" id="da_percentage" step="0.01">%</td></tr> +<tr><td><label for="professional_tax">Professional Tax:</label></td><td><input type="number" name="professional_tax" id="professional_tax" step="0.01">%</td></tr> +<tr><td colspan="2"><input type="submit" value="Calculate Salary"></td></tr> +</table> +</form> + <?php +} + +function calculate_salary($dbh) { + $hra_percentage = $_POST['hra_percentage'] ?? 0; + $da_percentage = $_POST['da_percentage'] ?? 0; + $professional_tax = $_POST['professional_tax'] ?? 0; + $result = $dbh->query('SELECT * FROM `SALARY`'); + while ($row = $result->fetch_assoc()) { + $hra = $row['BASIC_PAY'] * ($hra_percentage / 100); + $da = $row['BASIC_PAY'] * ($da_percentage / 100); + $ptax = $row['BASIC_PAY'] * ($professional_tax / 100); + $stmt = $dbh->prepare('UPDATE `SALARY` SET `HRA` = ?, `DA` = ?, `PROFESSIONAL_TAX` = ? WHERE `EMP_ID` = ?'); + $stmt->bind_param('dddi', $hra, $da, $ptax, $row['EMP_ID']); + $stmt->execute(); + } + display_records($dbh); +} + +if ($_SERVER['REQUEST_METHOD'] === 'POST') { + try { + $dbh = connect_to_database(); + calculate_salary($dbh); + $dbh->close(); + } catch (mysqli_sql_exception $e) { + display_failure($e->getMessage()); + } +} else { + show_salary_form(); +} diff --git a/mysql-php/code/a5.php b/mysql-php/code/a5.php new file mode 100644 index 0000000..e8c4284 --- /dev/null +++ b/mysql-php/code/a5.php @@ -0,0 +1,188 @@ +<?php +declare(strict_types=1); +error_reporting(E_ALL); + +function connect_to_database() { + $dbh = new mysqli('localhost', 'root', ''); + $dbh->set_charset('utf8mb4'); + $dbh->query('CREATE DATABASE IF NOT EXISTS EMPLOYEE_DB'); + $dbh->select_db('EMPLOYEE_DB'); + $dbh->query('CREATE TABLE IF NOT EXISTS `Employee` (`EID` INT PRIMARY KEY AUTO_INCREMENT, `Ename` VARCHAR(255), `Address` TEXT, `Phno` VARCHAR(20), `Salary` DECIMAL(10,2), `Category` ENUM("GEN", "SC", "ST", "OBC"), `Language` VARCHAR(255))'); + return $dbh; +} + +function html_prologue($title) { + ?> + <!DOCTYPE html> + <html lang="en"> + <head> + <meta charset="UTF-8"> + <meta name="viewport" content="width=device-width, initial-scale=1.0"> + <title><?php echo $title; ?></title> + <style> + body { font-family: sans-serif; } + table { width: 80%; margin: 20px auto; border-collapse: collapse; } + th, td { border: 1px solid #ddd; padding: 8px; text-align: left; } + th { background-color: #f2f2f2; } + </style> + </head> + <body> + <?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 show_employee_form() { + html_prologue('Employee Form'); + ?> + <form method="post" action="<?php echo $_SERVER['PHP_SELF']; ?>"> + <h2>Employee Form</h2> + <label for="ename">Name:</label><br> + <input type="text" id="ename" name="ename" required><br> + <label for="address">Address:</label><br> + <textarea id="address" name="address" required></textarea><br> + <label for="phno">Phone Number:</label><br> + <input type="tel" id="phno" name="phno" required><br> + <label for="salary">Salary:</label><br> + <input type="text" id="salary" name="salary" required><br> + <label for="category">Category:</label><br> + <input type="radio" id="gen" name="category" value="GEN" checked> + <label for="gen">GEN</label> + <input type="radio" id="sc" name="category" value="SC"> + <label for="sc">SC</label> + <input type="radio" id="st" name="category" value="ST"> + <label for="st">ST</label> + <input type="radio" id="obc" name="category" value="OBC"> + <label for="obc">OBC</label><br> + <label for="language">Language:</label><br> + <input type="checkbox" id="bengali" name="language[]" value="Bengali"> + <label for="bengali">Bengali</label> + <input type="checkbox" id="english" name="language[]" value="English"> + <label for="english">English</label> + <input type="checkbox" id="hindi" name="language[]" value="Hindi"> + <label for="hindi">Hindi</label><br><br> + <input type="submit" value="Submit"> + </form> + <?php +} + +function insert_employee_record($dbh) { + if ($_SERVER['REQUEST_METHOD'] === 'POST') { + $ename = $_POST['ename']; + $address = $_POST['address']; + $phno = $_POST['phno']; + $salary = $_POST['salary']; + $category = $_POST['category']; + $languages = implode(", ", $_POST['language']); + $stmt = $dbh->prepare('INSERT INTO `Employee` (`Ename`, `Address`, `Phno`, `Salary`, `Category`, `Language`) VALUES (?, ?, ?, ?, ?, ?)'); + $stmt->bind_param('sssdss', $ename, $address, $phno, $salary, $category, $languages); + $stmt->execute(); + $stmt->close(); + display_success(); + } +} + +function display_employee_records($dbh) { + $order_by = isset($_POST['order_by']) ? $_POST['order_by'] : 'Ename'; + $desc_order = isset($_POST['desc_order']); + $field_labels = [ + 'EID' => 'Employee ID', + 'Ename' => 'Name', + 'Address' => 'Address', + 'Phno' => 'Phone Number', + 'Salary' => 'Salary', + 'Category' => 'Category', + 'Language' => 'Language' + ]; + $allowed_fields = array_keys($field_labels); + if (!in_array($order_by, $allowed_fields)) + display_failure('Invalid order_by field.'); + $order_clause = $order_by . ($desc_order ? ' DESC' : ''); + $result = $dbh->query('SELECT * FROM `Employee` ORDER BY ' . $order_clause); + html_prologue('Employee Records'); + ?> + <h2>Employee Records</h2> + <form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post"> + <label for="order_by">Order By:</label> + <select name="order_by" id="order_by"><?php + foreach ($field_labels as $field => $label) { + echo '<option value="' . $field . '"'; + if ($order_by === $field) { + echo ' selected'; + } + echo '>' . $label . '</option>'; + } ?> + </select> + <input type="checkbox" name="desc_order" id="desc_order" <?php if ($desc_order) echo 'checked'; ?>> + <label for="desc_order">Descending Order</label> + <input type="submit" value="Order"> + </form> + <table> + <tr> + <?php foreach ($field_labels as $label) { ?> + <th><?php echo $label; ?></th> + <?php } ?> + </tr> + <?php while ($row = $result->fetch_assoc()) { ?> + <tr> + <?php foreach ($field_labels as $field => $label) { ?> + <td><?php echo $row[$field]; ?></td> + <?php } ?> + </tr> + <?php } ?> + </table> + </body> + </html> + <?php +} + +function display_menu() { + html_prologue('Home'); ?> + <h2>Menu</h2> + <ul class="menu"> + <li><a href="<?php echo $_SERVER['PHP_SELF']; ?>/insert">Insert Employee</a></li> + <li><a href="<?php echo $_SERVER['PHP_SELF']; ?>/display">Display Employees</a></li> + </ul><?php +} + +try { + if (!empty($_SERVER['PATH_INFO']) || $_SERVER['PATH_INFO'] !== '/') { + $path = $_SERVER['PATH_INFO']; + if ($path === '/insert') { + if ($_SERVER['REQUEST_METHOD'] === 'POST') { + $dbh = connect_to_database(); + insert_employee_record($dbh); + $dbh->close(); + } else { + show_employee_form(); + } + } elseif ($path === '/display') { + $dbh = connect_to_database(); + display_employee_records($dbh); + $dbh->close(); + } else { + display_menu(); + echo '<p class="error">Path <code>' . htmlspecialchars($path) . '</code> was not found.</p>'; + die(); + } + } else { + display_menu(); + } +} catch (mysqli_sql_exception $e) { + display_error($e->getMessage()); +} + |