///////////////////////////////////// menu.inc ////////////////////////////////////////
Index |
Task 1 |
Task 2 |
Task 3 |
Task 4 |
Task 5 |
Task 6 |
Task 7 |
Task 8 |
Task 9
///////////////////////////////////// index.php ////////////////////////////////////////
query("INSERT INTO `members` VALUES (NULL, '$name', '$email')");
include("task2.php");
}
// process the update group form
if(isset($_POST["updateMemberGroup"])){
$member_id = $_POST["memberName"];
$group_id = $_POST["memberGroup"];
$insert = $connect->query("UPDATE membergroup SET groupID = '$group_id' WHERE memberID = '$member_id'");
include("task2.php");
}
// process the delete member form
if(isset($_POST["deleteGroup"])){
$group_id = $_POST["delGroup"];
$del = $connect->query("DELETE FROM groups WHERE groupID = '$group_id'");
header("Location: task2.php");
}
if($_POST["submitQuery"]){
// get the clicked radio button
$clicked = $_POST["select"];
switch($clicked){
case "orderby":
// handle the action
$orderby = $connect->query("SELECT * FROM groups ORDER BY groupName ASC");
include("task2.php");
break;
case "like":
// handle the action
$like = $connect->query("SELECT * FROM groups WHERE groupName LIKE '%2613%'");
include("task2.php");
break;
case "innerjoin":
// handle the action
$inner = $connect->query("SELECT members.memberID, members.memberName, members.memberEmail, groups.groupName
FROM members
INNER JOIN membergroup ON members.memberID = membergroup.memberID
INNER JOIN groups ON membergroup.groupID = groups.groupID WHERE members.memberID = '1'");
include("task2.php");
break;
case "limit":
// handle the action
$limit = $connect->query("SELECT members.memberID, members.memberName, members.memberEmail, groups.groupName
FROM members
INNER JOIN membergroup ON members.memberID = membergroup.memberID
INNER JOIN groups ON membergroup.groupID = groups.groupID LIMIT 10");
include("task2.php");
break;
case "max":
// handle the action
$max = $connect->query("SELECT MAX(memberID) AS 'member' FROM members")->fetchColumn();
include("task2.php");
break;
default:
include("task2.php");
break;
}
}
}else{
include("details.php");
}
?>
///////////////////////////////////// details.php ////////////////////////////////////////
ICT3612
Student Name:
Student Number:
Semester 2024 ICT3612 Portofolio
";
?>
////////////////////////////////////////task 1/////////////////////////////////////////////////
ICT3612
Member: contains the data about members. Its primary key is MemberID that uniquely identifies each member.
";
echo "MemberGroup: contains the data about the group in which a member belongs to. Its primary key is combination of MemberID and GroupID. It has MemberID foreign key that references Member table and GroupID foreign key that references Group table.
";
echo "Group: contains the data about groups. Its primary key is GroupID that uniquely identifies each group.
";
///////////////////////// task 1b //////////////////////////////
/*
CREATE DATABASE music_album_db;
USE music_album_db;
CREATE TABLE artiste(
artisteID int(10) AUTO_INCREMENT PRIMARY KEY,
artisteFirstName varchar(100) DEFAULT NULL,
artisteLastName varchar(100) DEFAULT NULL,
artisteStageName varchar(100) DEFAULT NULL
);
CREATE TABLE album(
albumID int(10) AUTO_INCREMENT PRIMARY KEY,
albumTitle varchar(100) DEFAULT NULL,
albumDate DATE DEFAULT CURRENT_TIMESTAMP,
albumNoOfTracks int(10) NOT NULL,
artisteID int(10) NOT NULL,
FOREIGN KEY (artisteID) REFERENCES artiste(artisteID)
);
CREATE TABLE tracks(
id int(10) AUTO_INCREMENT PRIMARY KEY,
trackNumber int(11) NOT NULL,
trackTitle varchar(100) DEFAULT NULL,
trackDuration time,
albumID int(10) NOT NULL,
FOREIGN KEY (albumID) REFERENCES album(albumID)
);
*/
echo "
";
?>
//////////////////////////////////////////// task 2///////////////////////////////////////////////////
ICT3612
| Group ID | Group Name | Description |
";
$groups = $connect->query("SELECT * FROM groups");
foreach($groups as $g){
$id = $g["groupID"];
$name = $g["groupName"];
$desc = $g["groupDescription"];
echo "
| $id | $name | $desc |
";
}
echo "";
echo "
";
echo "
| Member ID | Member Name | Member Email |
";
$members = $connect->query("SELECT * FROM members LIMIT 10");
foreach($members as $m){
$id = $m["memberID"];
$name = $m["memberName"];
$email = $m["memberEmail"];
echo "
| $id | $name | $email |
";
}
echo "
";
echo "
";
echo "
| Member ID | Group | Join Date |
";
$mgs = $connect->query("SELECT * FROM membergroup LIMIT 10");
foreach($mgs as $mg){
$id = $mg["memberID"];
$group_id = $connect->query("SELECT groupName FROM groups WHERE groupID = " . $mg["groupID"] . ";")->fetchColumn();
//$group_id = $mg["groupID"];
$join_date = $mg["joiningDate"];
echo "
| $id | $group_id | $join_date |
";
}
echo "
";
echo "
";
echo "Add a member
";
echo "
";
echo "
";
echo "Modify Member Group
";
echo "
";
echo "
";
echo "Delete Group
";
echo "
";
echo "
";
echo "Other Queries
";
echo "";
echo "
";
if(isset($orderby)){
foreach($orderby as $res){
echo $res["groupID"] . ", " . $res["groupName"] . ", " . $res["groupDescription"] . "
";
}
}
if(isset($like)){
foreach($like as $res){
echo $res["groupID"] . ", " . $res["groupName"] . ", " . $res["groupDescription"] . "
";
}
}
if(isset($inner)){
foreach($inner as $res){
echo $res["memberID"] . ", " . $res["memberName"] . ", " . $res["memberEmail"] . ", " . $res["groupName"] . "
";
}
}
if(isset($limit)){
foreach($limit as $res){
echo $res["memberID"] . ", " . $res["memberName"] . ", " . $res["memberEmail"] . ", " . $res["groupName"] . "
";
}
}
if(isset($max)){
echo $max;
}
?>