diff options
Diffstat (limited to 'mysql-php/code')
| -rw-r--r-- | mysql-php/code/a1.php | 24 | ||||
| -rw-r--r-- | mysql-php/code/a2.php | 13 | ||||
| -rw-r--r-- | mysql-php/code/a3.php | 132 |
3 files changed, 95 insertions, 74 deletions
diff --git a/mysql-php/code/a1.php b/mysql-php/code/a1.php index 4569ca8..614f044 100644 --- a/mysql-php/code/a1.php +++ b/mysql-php/code/a1.php @@ -1,7 +1,7 @@ <?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', ''))) @@ -103,7 +103,7 @@ function display_failure($reason) { function check_post_vars($vardesc) { $vars = []; foreach ($vardesc as $name => $desc) { - if (!isset($_POST[$name]) || empty($_POST[$name])) + if (empty($_POST[$name])) display_failure('The ' . $desc . ' field can not be empty'); $vars[$name] = $_POST[$name]; } @@ -186,7 +186,7 @@ function show_record($record) { <?php } -if (!isset($_SERVER['PATH_INFO']) || empty($_SERVER['PATH_INFO']) || $_SERVER['PATH_INFO'] == '/') { +if (empty($_SERVER['PATH_INFO']) || $_SERVER['PATH_INFO'] == '/') { display_menu(); die(); } @@ -211,17 +211,17 @@ if ($path === 'insert') { 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 { + if (empty($_GET['email'])) { display_search_form(); + die(); } + $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_menu(); echo '<p class="error">Path <code>' . htmlspecialchars($path) . '</code> was not found.</p>'; diff --git a/mysql-php/code/a2.php b/mysql-php/code/a2.php index 10a7a33..9d9a1a5 100644 --- a/mysql-php/code/a2.php +++ b/mysql-php/code/a2.php @@ -1,7 +1,7 @@ <?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', ''))) @@ -32,8 +32,7 @@ function connect_to_database() { } function check_credentials($dbh, $username, $password) { - if (!isset($username) || empty($username) || !isset($password) || empty($password)) - return false; + if (empty($username) || 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); @@ -44,17 +43,17 @@ function check_credentials($dbh, $username, $password) { } function update_credentials($dbh) { - if (!check_credentials($dbh, $_POST['previous_username'], $_POST['previous_password'])) + 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; $successful = []; - if (isset($_POST['new_username']) && !empty($_POST['new_username'])) + if (!empty($_POST['new_username'])) $new_username = $_POST['new_username']; - if (isset($_POST['new_password']) && !empty($_POST['new_password'])) + if (!empty($_POST['new_password'])) $new_password = $_POST['new_password']; if ($new_password !== null) { - if (!isset($_POST['new_password2']) || empty($_POST['new_password2'])) + if (empty($_POST['new_password2'])) display_failure('Need to provide new password twice'); if ($new_password !== $_POST['new_password2']) display_failure('New password provided twice need to match'); diff --git a/mysql-php/code/a3.php b/mysql-php/code/a3.php index f029dd8..65fa2b9 100644 --- a/mysql-php/code/a3.php +++ b/mysql-php/code/a3.php @@ -1,23 +1,24 @@ <?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)); + $dbh = new mysqli('localhost', 'root', ''); + $dbh->set_charset('utf8mb4'); + $dbh->query('CREATE DATABASE IF NOT EXISTS EATERY_DB'); + $dbh->select_db('EATERY_DB'); + $dbh->query('CREATE TABLE IF NOT EXISTS `FOOD_DETAILS` (`FOOD_ID` INT PRIMARY KEY, `FOOD_ITEM` VARCHAR(255), `PRICE_PER_ITEM` DOUBLE, UNIQUE (`FOOD_ID`))'); + $dbh->query('INSERT INTO `FOOD_DETAILS` VALUES + (1, "Egg Biriyani (w/ 2 eggs)", 200), + (2, "Chicken Biriyani", 300), + (3, "Mutton Biriyani", 350), + (4, "Veg Pulao", 200), + (5, "Shahi Paneer", 220), + (6, "Schezwan Paneer", 240), + (7, "Steam Rice", 90) + ON DUPLICATE KEY UPDATE `FOOD_ITEM` = VALUES(`FOOD_ITEM`)'); + $dbh->query('CREATE TABLE IF NOT EXISTS `CUSTOMER_DETAILS` (`BILL_ID` INT PRIMARY KEY AUTO_INCREMENT, `CUSTOMER_NAME` VARCHAR(255), `TOTAL_AMOUNT_PAID` DOUBLE, `DATE_OF_PAYMENT` DATE)'); return $dbh; } @@ -32,22 +33,14 @@ 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.invoice td:nth-child(2), table.invoice td:nth-child(3) { text-align: end; } +p.right { text-align: right; } th, td { padding: 5px; } </style> <?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_failure($reason) { html_prologue('Operation failure'); ?> @@ -57,39 +50,68 @@ function display_failure($reason) { die(); } -function search_and_show($dbh, $email) { - $stmt = mysqli_prepare($dbh, 'SELECT * FROM STUDENT WHERE EMAIL = ?'); - $result = mysqli_query($dbh, ); - html_prologue('Students\' details'); +function show_menu($dbh) { + html_prologue('Eatery Menu'); ?> -<h2>Students' details</h2> -<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 +<form method="post" action="<?php echo $_SERVER['PHP_SELF']; ?>"> +<h1>Welcome to Delish Eatery</h1> +<table class="noborder"> +<tr><td><label for="name">Name:</label></td><td colspan="2"><input type="text" name="customer_name" id="name"></td></tr> +<tr><th>Servings</th><th>Food item</th><th>Price</th></tr> + <?php $result = $dbh->query('SELECT * FROM `FOOD_DETAILS`'); + while ($row = $result->fetch_assoc()) { + echo '<tr><td><input type="number" min="0" placeholder="0" name="servings[', $row['FOOD_ID'], ']"></td>'; + echo '<td>', $row['FOOD_ITEM'], '</td>', '<td>₹', number_format(+$row['PRICE_PER_ITEM'], 2), '</td></tr>'; } ?> +<tr><td colspan="3"><input type="submit" value="Order"></td></tr> </table> -<?php +</form> + <?php +} + +function process_order($dbh) { + if (empty($_POST['customer_name'])) + display_failure('Can not order without the customer name'); + $servings = empty($_POST['servings']) ? [] : array_filter(array_map('intval', $_POST['servings'])); + if (count($servings) == 0) + display_failure('No serving selected in the order'); + $orders = array_filter(array_map('intval', array_keys($servings))); + $items = $dbh->query('SELECT * FROM `FOOD_DETAILS` WHERE `FOOD_ID` IN (' . implode(', ', $orders) . ')')->fetch_all(MYSQLI_ASSOC); + $total_price = 0.0; + foreach ($items as $i => $item) { + $items[$i]['price'] = $servings[$item['FOOD_ID']] * $item['PRICE_PER_ITEM']; + $total_price += $items[$i]['price']; + } + $tax = 0.15 * $total_price; + $net_price = $total_price + $tax; + $stmt = $dbh->prepare('INSERT INTO `CUSTOMER_DETAILS` (`CUSTOMER_NAME`, `TOTAL_AMOUNT_PAID`, `DATE_OF_PAYMENT`) VALUES (?, ?, ?)'); + $stmt->bind_param('sds', $_POST['customer_name'], $net_price, @date('Y-m-d')); + $stmt->execute(); + $bill_id = $dbh->insert_id; + html_prologue('Customer invoice'); + ?> +<h1>Customer Invoice</h1> +<p class="right">Bill No.: D.E./<?php echo date('Y.m.d'), '/', $bill_id; ?></p> +<p><b>Name</b>: <?php echo $_POST['customer_name']; ?></p> +<table class="invoice"> +<tr><th>Food item</th><th>Servings</th><th>Price</th></tr> +<?php foreach ($items as $item) { + echo '<tr><td>', implode('</td><td>', [$item['FOOD_ITEM'], $servings[$item['FOOD_ID']], number_format($item['price'], 2)]), '</td></tr>'; +} ?> +<tr><td colspan="2">Total:</td><td><?php echo number_format($total_price, 2); ?></td></tr> +<tr><td colspan="2">GST (15%):</td><td><?php echo number_format($tax, 2); ?></td></tr> +<tr><td colspan="2">Net price:</td><td><?php echo number_format($net_price, 2); ?></td></tr> +</table> + <?php } -if (isset($_GET['email']) && !empty($_GET['email'])) { +try { $dbh = connect_to_database(); - search_and_show($dbh, $_GET['email']); - mysqli_close($dbh); -} else { - html_prologue('Search for a student'); - display_search_form(); + if ($_SERVER['REQUEST_METHOD'] == 'POST') + process_order($dbh); + else show_menu($dbh); + $dbh->close(); +} catch (mysqli_sql_exception $e) { + display_failure($e->getMessage()); } + |