PHPize Online / SQLize Online  /  SQLtest Online

A A A
Share      Blog   Popular
Copy Format Clear
CREATE TABLE users ( user_id int NOT NULL AUTO_INCREMENT, user_name varchar(50) NOT NULL, password varchar(50) NOT NULL, role varchar(50) NOT NULL, PRIMARY KEY (user_id), INDEX (user_name), INDEX (user_id) ); CREATE TABLE records ( record_id int NOT NULL AUTO_INCREMENT, user_name varchar(50), user_id int, work_type varchar(100), time_start datetime, time_end datetime, record_date datetime, approved boolean, PRIMARY KEY (record_id), FOREIGN KEY (user_id) REFERENCES users(user_id), FOREIGN KEY (user_name) REFERENCES users(user_name) ); INSERT INTO users(user_name,password,role) VALUES ('Orlandson Asturiano','password123','employee'); INSERT INTO users(user_name,password,role) VALUES ('Bob Billy','password12356','admin'); SELECT * FROM users; INSERT INTO records(user_name, user_id, work_type, time_start, time_end, record_date, approved) VALUES( (SELECT user_name FROM users WHERE user_id = 1), (SELECT user_id FROM users WHERE user_id = 1), 'Programming', '2023-05-23 08:30:00', '2023-05-23 14:30:00', '2023-05-23', false); INSERT INTO records(user_name, user_id, work_type, time_start, time_end, record_date, approved) VALUES( (SELECT user_name FROM users WHERE user_id = 1), (SELECT user_id FROM users WHERE user_id = 1), 'Testing', '2023-05-24 010:30:00', '2023-05-24 16:30:00', '2023-05-24', true); SELECT * FROM records;
Copy Clear
Copy Format Clear
<?php use Carbon\Carbon; /**/ session_start(); //Login Info $username="Orlandson Asturiano"; $password="password123"; $now = Carbon::now()->format('d/m/Y'); $employee_id = 1;//Represents $record_id = 1;//Represents selected record //Create record form data $fuserName="Orlandson Asturiano"; $fuserId=1; $fworkType="Final Draft"; $ftimeStart="2023-05-25 08:30:00"; $ftimeEnd="2023-05-25 07:30:00"; $frecordDate="2023-05-25"; $fapproved="false"; /*LOGIN*/ $sql = "SELECT * FROM users WHERE user_name = '{$username}' AND password = '{$password}'"; $result = $mysqli->query($sql); if (mysqli_num_rows($result)!=0) { while($row = $result->fetch_assoc()) { //var_dump($row); //printf($row["role"]); $_SESSION["user_name"] = $row["user_name"]; $_SESSION["user_id"] = $row["user_id"]; $_SESSION["password"] = $row["password"]; $_SESSION["role"] = $row["role"]; $_SESSION["auth"] = true; printf("Logged In!\n"); if($_SESSION["role"] == "admin"){ //TODO redirect to admin view }else{ //TODO redirect to employee view } } }else{ //Wront login printf("Credentials Incorrect!\n"); } $sql = " INSERT INTO records (user_name, user_id, work_type, time_start, time_end, record_date, approved) VALUES( (SELECT user_name FROM users WHERE user_id = {$_SESSION["user_id"]}), (SELECT user_id FROM users WHERE user_id = {$_SESSION["user_id"]}), '{$fworkType}', '{$ftimeStart}', '{$ftimeEnd}', '{$frecordDate}', '{$fapproved}')"; $result = $mysqli->query($sql); printf("Today is %s\nCurrent PHP versi (on: %s \n\n", $now, phpversion()); //Testing $sql = "SELECT * FROM records"; $result = $mysqli->query($sql); $records = $result->fetch_object(); while($row = $result->fetch_assoc()) { var_dump($records); } printf("\nEmployee Get Records-------------------------------------\n"); //Employee get unapproved records $sql = "SELECT * FROM records WHERE user_id = {$_SESSION["user_id"]} AND approved = FALSE"; $result = $mysqli->query($sql); while($row = $result->fetch_assoc()) { var_dump($row); } printf("\nAdmin Get All Unapproved Records-------------------------------------\n"); //Admin get unapproved records $sql = "SELECT * FROM records WHERE approved = FALSE"; $result = $mysqli->query($sql); while($row = $result->fetch_assoc()) { var_dump($row); } printf("\nAdmin Get All Records-------------------------------------\n"); //Admin get unapproved records $sql = "SELECT * FROM records"; $result = $mysqli->query($sql); while($row = $result->fetch_assoc()) { var_dump($row); } printf("\nDelete Employee Records-------------------------------------\n"); //Employee delete records $sql = "DELETE FROM records WHERE record_id = {$record_id} AND user_id = {$employee_id}"; $result = $mysqli->query($sql); $sql = "SELECT * FROM records"; $result = $mysqli->query($sql); while($row = $result->fetch_assoc()) { var_dump($row); } printf("\nAdmin Delete Employee Records-------------------------------------\n"); //Admin Delete record $sql = "DELETE FROM records WHERE record_id = {$record_id}"; $result = $mysqli->query($sql); $sql = "SELECT * FROM records"; $result = $mysqli->query($sql); while($row = $result->fetch_assoc()) { var_dump($row); }
Show:  
Copy Clear