I'm trying to create an online learning courseware and connecting to MySQL database via PDO. What I'm trying to achieve is: if a user is logged in after registering for a particular course, the courseware should display the registered course, as well as its course weeks. I want each course added by the admin to have weeks of learning. For example, the course "Digital Marketing" will have “Week 0”, "Week 1", "Week 2", "Week 3", etc in the courseware, depending of the number of weeks the admin has added to that very course. But what I've done so far causes id mix-up that is making the course weeks to be improperly displayed.
The following is what I've done: I created a MySQL 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) DEFAULT 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=6 ;

The following is the week.php code, to display the weeks for student's registered course, so that each course week can open on a new page:

<?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" => "My Online Training Week", "courseware" => $courseware]);

?>

Be advised that I’m using a custom function named “query”.

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']}&coursename={$courseware[0]["coursename"]}'>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 -->

The issue:
If a student, for example, 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 (e.g. 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?
Please, look at my MySQL tables and PHP code, to help me figure out the issue. Thanks for your time and help in advance.

The ID that appear in the URL depends on the field names of the form that sends the data. A field in your form probably has a name attribute that equals to id and information entered into that field goes into the id variable in the URL. I would recommend that you name the fields in the form more descriptively like userid and courseid. Then you will know what ID is in the URL.

Also looking at your code you actually never use the variables from the URL in the queries. The session variable is used in your example in all queries. The above recommendation goes for the session variables, too. So $_SESSION['userid'] and $_SESSION['courseid'].

@broj1, thanks for your input and insight. I've already renamed the table colunm names to user_id, course_id and courseware_id. I've also made the users table to only hold unique/one-time user information and no longer hold any registration/course information. In effect, I created another table for course registration.

And do the same for your form fields since it seems that this is the cause of most of the above problems.

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.