◈ TASK_02 // CRUD OPS
[ DATABASE OPERATIONS ]
connect_error)die("Connection failed: ".$conn->connect_error);
// Tables
$sqls=[
"CREATE TABLE IF NOT EXISTS Modules(moduleID INT AUTO_INCREMENT PRIMARY KEY,moduleCode VARCHAR(20) UNIQUE NOT NULL,moduleName VARCHAR(100) NOT NULL)",
"CREATE TABLE IF NOT EXISTS Lecturers(lecturerID INT AUTO_INCREMENT PRIMARY KEY,firstName VARCHAR(50) NOT NULL,lastName VARCHAR(50) NOT NULL,email VARCHAR(100) UNIQUE NOT NULL)",
"CREATE TABLE IF NOT EXISTS Roles(roleID INT AUTO_INCREMENT PRIMARY KEY,roleName VARCHAR(50) NOT NULL,roleDescription TEXT)",
"CREATE TABLE IF NOT EXISTS ModuleLecturerRole(moduleLecturerRoleID INT AUTO_INCREMENT PRIMARY KEY,moduleID INT NOT NULL,lecturerID INT NOT NULL,roleID INT NOT NULL,FOREIGN KEY(moduleID)REFERENCES Modules(moduleID)ON DELETE CASCADE,FOREIGN KEY(lecturerID)REFERENCES Lecturers(lecturerID)ON DELETE CASCADE,FOREIGN KEY(roleID)REFERENCES Roles(roleID)ON DELETE CASCADE,UNIQUE KEY unique_assignment(moduleID,lecturerID,roleID))"
];
foreach($sqls as $s)$conn->query($s);
// Sample data
$ck=$conn->query("SELECT COUNT(*) as c FROM Modules");if($ck->fetch_assoc()['c']==0){
$conn->query("INSERT INTO Modules(moduleCode,moduleName)VALUES('ICT2611','Internet Programming'),('ICT2613','Internet Programming'),('ICT3612','Advanced Internet Programming'),('ICT3715','Web App Development'),('ICT3721','Database Design')");
$conn->query("INSERT INTO Lecturers(firstName,lastName,email)VALUES('John','Smith','smithj@unisa.ac.za'),('Mary','Johnson','johnsonm@unisa.ac.za'),('Peter','Williams','williamsp@unisa.ac.za'),('Susan','Brown','browns@unisa.ac.za'),('David','Jones','jonesd@unisa.ac.za')");
$conn->query("INSERT INTO Roles(roleName,roleDescription)VALUES('Primary Lecturer','Main lecturer'),('Secondary Lecturer','Supporting'),('Internal Moderator','QA internal'),('External Moderator','QA external'),('Critical Reader','Content review')");
$assign=[[1,1,1],[1,2,2],[1,3,3],[2,1,1],[2,4,2],[2,5,4],[3,2,1],[3,3,2],[3,1,3],[4,4,1],[4,5,2],[4,1,4],[5,3,1],[5,5,2],[5,2,3]];
foreach($assign as $a)$conn->query("INSERT INTO ModuleLecturerRole(moduleID,lecturerID,roleID)VALUES($a[0],$a[1],$a[2])");
}
// CRUD
if(isset($_POST['add_mod'])){$c=$_POST['code'];$n=$_POST['name'];$s=$conn->prepare("INSERT INTO Modules(moduleCode,moduleName)VALUES(?,?)");$s->bind_param("ss",$c,$n);$s->execute()?print'
+ Module added
':print'
! Error
';$s->close();}
if(isset($_POST['upd_assign'])){$id=$_POST['aid'];$mid=$_POST['mid'];$lid=$_POST['lid'];$rid=$_POST['rid'];$s=$conn->prepare("UPDATE ModuleLecturerRole SET moduleID=?,lecturerID=?,roleID=? WHERE moduleLecturerRoleID=?");$s->bind_param("iiii",$mid,$lid,$rid,$id);$s->execute()?print'
+ Assignment updated
':print'
! Error
';$s->close();}
if(isset($_POST['del_lec'])){$id=$_POST['lid'];$ck2=$conn->query("SELECT*FROM ModuleLecturerRole WHERE lecturerID=$id");if($ck2->num_rows>0)print'
! Has assignments
';else{$s=$conn->prepare("DELETE FROM Lecturers WHERE lecturerID=?");$s->bind_param("i",$id);$s->execute()?print'
+ Lecturer deleted
':print'
! Error
';$s->close();}}
?>
// TABLES
▸ $t";
$r=$c->query("SELECT*FROM $t");
if($r->num_rows>0){
echo"
";foreach($cols as $col)echo"| $col | ";if($id)echo"ACT | ";echo"
";
while($row=$r->fetch_assoc()){echo"";foreach($cols as $col)echo"| ".htmlspecialchars($row[$col])." | ";if($id)echo"[EDIT] | ";echo"
";}
echo"
";
}else echo"
∅ No data
";
}
render($conn,"Modules",['moduleID','moduleCode','moduleName']);
render($conn,"Lecturers",['lecturerID','firstName','lastName','email'],'lecturerID');
render($conn,"Roles",['roleID','roleName','roleDescription']);
echo"
▸ ModuleLecturerRole
";
$r=$conn->query("SELECT mlr.moduleLecturerRoleID,m.moduleCode,CONCAT(l.firstName,' ',l.lastName)as lecturer,r.roleName FROM ModuleLecturerRole mlr JOIN Modules m ON mlr.moduleID=m.moduleID JOIN Lecturers l ON mlr.lecturerID=l.lecturerID JOIN Roles r ON mlr.roleID=r.roleID");
if($r->num_rows>0){
echo"
| ID | Module | Lecturer | Role | ACT |
|---|
";
while($row=$r->fetch_assoc()){echo"| {$row['moduleLecturerRoleID']} | {$row['moduleCode']} | {$row['lecturer']} | {$row['roleName']} | [EDIT] |
";}
echo"
";
}
?>
// CRUD FORMS
query("SELECT*FROM ModuleLecturerRole WHERE moduleLecturerRoleID=$id");if($row=$r->fetch_assoc()){?>
// SELECT QUERIES
"SELECT*FROM Modules ORDER BY moduleCode",'q2'=>"SELECT*FROM Lecturers WHERE email LIKE '%@unisa.ac.za'",'q3'=>"SELECT m.moduleCode,CONCAT(l.firstName,' ',l.lastName)as lecturer,r.roleName FROM ModuleLecturerRole mlr JOIN Modules m ON mlr.moduleID=m.moduleID JOIN Lecturers l ON mlr.lecturerID=l.lecturerID JOIN Roles r ON mlr.roleID=r.roleID",'q4'=>"SELECT*FROM Lecturers WHERE firstName LIKE 'J%' AND email LIKE '%@unisa.ac.za'",'q5'=>"SELECT MAX(moduleID)as maxID FROM Modules"][$sel];$r=$conn->query($sql);echo"
// $sql
";if($r->num_rows>0){echo"
";$f=$r->fetch_fields();echo"";foreach($f as$col)echo"| {$col->name} | ";echo"
";while($row=$r->fetch_assoc()){echo"";foreach($row as$v)echo"| ".htmlspecialchars($v)." | ";echo"
";}echo"
";}else echo"
∅ No results
";echo"
";}?>