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.

I'm creating an online learning courseware using PHP 5.5

Why? Php 5.5 has already reached end of life and is no longer supported.
Click Here

@benanamen, Thanks for stopping by and for pointing out the end of life issue.

I'm aware of the discontinuation of PHP 5.5. Actually I started developing this script in 2014 and then abandoned it halfway. So, as I resumed work on it, I decided to upgrade everything to PHP 7 or 7.2, when I get a fully working script. That would be easier for me to upgrade then.

Do you have any idea how I can get the courseware to display weeks like week 1, week 2, week 3, etc, for each of the user's registered course.

I just thought I should make more clarifications, to see if I would get the much needed help.

I noticed that id mix-up might be causing the issue, but I'm confused.

If a student named Ben has an id "1" in the "users" DB table and registered for "Digital Branding" course, which has an id "1" in the "courses" DB table, the student can only access the first week named "Week 1" in the courseware. Interestingly, the URL bears "id=1" like this http://script/courseware/week.php?id=1.

No other week added by the admin for the same course can be accessed, as the script is currently.

Even when another student named Ken with an id "2" who registered for a different course (Social Media Marketing) logs in, the courseware still displays the "Week 1" content for "Digital Branding" course. But, the URL bears id=2 like this http://script/courseware/week.php?id=2 Coincidentally, Ken has an id "2" in the users table and the course (Social Media Marketing) he registered for has an id "2" as well.

The "users", "courses" and "courseware" tables all have columns named "id". So, which "id" exactly appears in the URL? Any idea how to fix the issue?

Thanks in advance for your time and help.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.