diff options
Diffstat (limited to 'mysql-php/code/a5.php')
| -rw-r--r-- | mysql-php/code/a5.php | 188 |
1 files changed, 188 insertions, 0 deletions
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()); +} + |