I'm creating an online learning courseware using PHP 5.5 and connecting via PDO. But I'm confused as to how to get the courseware to work, as I want. This is what I've done: I created a table for students and named it "users" and another for courses and named it "courses". Moreover, I created another table called "courseware", so that whenever a student registers for a course, the student would gain a restricted access to that particular course alone. The following are the MySQL tables:
The "users" table:
CREATE TABLE IF NOT EXISTS `users` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`username` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`firstname` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
`lastname` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
`email` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`phone` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
`birthday` date NOT NULL,
`hash` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`usersex` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
`userimage` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
`reg_course` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`regdate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`courseduration` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`fees` decimal(65,4) unsigned NOT NULL,
`advance` decimal(65,4) unsigned NOT NULL,
`balance` decimal(65,4) unsigned NOT NULL,
`status` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
`start` date NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `username` (`username`,`email`,`userimage`,`phone`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=4 ;The "courses" table:
CREATE TABLE IF NOT EXISTS `courses` (
`id` int(10) unsigned NOT NULL,
`coursename` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`course_title` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`meta_keywords` text COLLATE utf8_unicode_ci NOT NULL,
`meta_description` text COLLATE utf8_unicode_ci NOT NULL,
`short_desc` text COLLATE utf8_unicode_ci NOT NULL,
`coursedesc` text COLLATE utf8_unicode_ci NOT NULL,
`courseduration` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`coursecode` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`fees` decimal(65,4) unsigned NOT NULL,
`courseimage` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
`date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
UNIQUE KEY `id_2` (`id`,`coursecode`),
KEY `id` (`id`,`coursecode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='PRIMARY KEY (id, coursecode)';The "courseware" table:
CREATE TABLE IF NOT EXISTS `courseware` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`grading` text COLLATE utf8_unicode_ci NOT NULL,
`due_dates` text COLLATE utf8_unicode_ci NOT NULL,
`main_content` text COLLATE utf8_unicode_ci NOT NULL,
`weekname` int(10) unsigned NOT NULL,
`coursename` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `id_2` (`id`,`coursename`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='PRIMARY KEY (id, coursename)' AUTO_INCREMENT=4 ;The following is the courseware PHP code, to display the student's registered course:
The following is the week.php code, to display the weeks for student's registered course:
<?php
// include configuration file
require("../../includes/config.php");
// query users table to retrieve current user's profile
$users = query("SELECT * FROM users WHERE id = ?", $_SESSION["id"]);
$courses = query("SELECT * FROM courses WHERE id = ?", $_SESSION["id"]);
if (!$users)
{
redirect("../login.php");
}
if (!$users[0]["reg_course"])
{
redirect("../userinfo.php");
}
$courseware = query("SELECT * FROM courseware ORDER BY weekname");
//$courseware = query("SELECT * FROM courseware WHERE id = ?", $_SESSION["id"]);
//header("Location: userinfo.php?id=%d", $row[0]['id']);
//echo "<script>window.location.href='userinfo.php?id=%d';</script>";
subrender("week_template.php", ["title" => "FlamyTech Online Training Week", "courseware" => $courseware]);
?>And this is my week_template.php code:
<?php
// query users table to retrieve its contents
$users = query("SELECT * FROM users WHERE id = ?", $_SESSION["id"]);
if (!empty($users[0]["reg_course"]))
{
if (!empty($users[0]['id']))
//print the user's registered course
printf("<h1>{$users[0]['reg_course']}</h1>");
// query courses' table to retrieve its contents
$courses = query("SELECT * FROM courses WHERE id = ?", $_SESSION["id"]);
printf('<div class="sidenav_wrap">');
printf('<div class="sidenav_coat">');
printf('<div class="sidenav">');
printf('<div class="sidenav_caption">Study Weeks</div>');
$courseware = query("SELECT * FROM courseware ORDER By weekname LIMIT 20");
//$courseware = query("SELECT * FROM courseware WHERE id = ?", $_SESSION["id"]);
foreach($courseware as $weekname)
{
echo "<div class='left_button'><a href='/courseware/week.php?id={$courseware[0]['id']}'>Week {$courseware[0]['weekname']}</a></div>";
}
}
?>
</div>
</div>
</div>
<!-- side nav ends -->
<!-- week's content starts -->
<div class="week">
<?php if (!empty($courseware[0]['id']))
printf("<h2 class='week_h2'>Week {$courseware[0]['weekname']}</h2>");
if (!empty($courseware[0]['grading']))
{
printf("<p><h3>Grading Policy</h3> {$courseware[0]['grading']}</p>");
}
if (!empty($courseware[0]['due_dates']))
{
printf("<p><h3>Dues Dates</h3> {$courseware[0]['due_dates']}</p>");
}
if (!empty($courseware[0]['main_content']))
{
printf("<p>{$courseware[0]['main_content']}</p>");
}
?>
<p>
</p>
</div>
<!-- week's content ends -->What I actually want to achieve is for each course added by the admin to have weeks of learning. For example, the course "Digital Marketing" will have "Week 1", "Week 2", "Week 3", etc in the courseware, depending of the number of weeks the admin deems fit to add for that particular course. But what I've done so far is not working the way I want it to.
Please, look into the MySQL tables and PHP code, to find out what is wrong. I would appreciate any help I can get. Thanks in advance for your time and insight.
