From 02884d29e4f5aea71364a203dcaecd53600d8aa4 Mon Sep 17 00:00:00 2001 From: Sudipto Mallick Date: Thu, 8 Feb 2024 11:08:33 +0000 Subject: Complete PHP assignments --- mysql-php/code/a2.php | 24 +++---- mysql-php/code/a4.php | 107 ++++++++++++++++++++++++++++ mysql-php/code/a5.php | 188 ++++++++++++++++++++++++++++++++++++++++++++++++++ 3 files changed, 303 insertions(+), 16 deletions(-) create mode 100644 mysql-php/code/a4.php create mode 100644 mysql-php/code/a5.php (limited to 'mysql-php/code') 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 @@ +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) { +?> + +<?php echo $title; ?> + + +

Operation failed

+

Reason:

+query('SELECT * FROM `SALARY`'); + ?> +

Employee Salary Records

+ + +fetch_assoc()) { + echo ''; +} ?> +
Employee NameBasic PayHRADAProfessional Tax
', $row['EMP_NAME'], '', number_format(+$row['BASIC_PAY'], 2), '', number_format(+$row['HRA'], 2), '', number_format(+$row['DA'], 2), '', number_format(+$row['PROFESSIONAL_TAX'], 2), '
+ +
+

Enter Salary Components

+ + + + + +
%
%
%
+
+ 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 @@ +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) { + ?> + + + + + + <?php echo $title; ?> + + + + +

Insertion successful.

+ +

Operation failed

+

Reason:

+ +
+

Employee Form

+
+
+
+
+
+
+
+
+
+ + + + + + + +
+
+ + + + + +

+ +
+ 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'); + ?> +

Employee Records

+
+ + + > + + +
+ + + + + + + fetch_assoc()) { ?> + + $label) { ?> + + + + +
+ + + +

Menu

+ close(); + } else { + show_employee_form(); + } + } elseif ($path === '/display') { + $dbh = connect_to_database(); + display_employee_records($dbh); + $dbh->close(); + } else { + display_menu(); + echo '

Path ' . htmlspecialchars($path) . ' was not found.

'; + die(); + } + } else { + display_menu(); + } +} catch (mysqli_sql_exception $e) { + display_error($e->getMessage()); +} + -- cgit 1.4.1-2-gfad0