خرید بک لینک

درباره سایت

CodingForums

آخرین مطالب

امکانات وب

Help me to correctly display data from MySQL tables

I'm trying to create an online learning courseware and coecting 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 x93Week 0x94, "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:
Code:
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=IoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=4 ;
The "courses" table:
Code:
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=IoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='PRIMARY KEY (id, coursecode)';

The "courseware" table:
Code:
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=IoDB  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 Code:
<?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.'userinfo.php?id=%d';</script>";
    
        subrender("week_template.php", ["title" => "My Online Training Week", "courseware" => $courseware]);

?>
Please know that I use a custom PHP function named x93queryx94.

And this is my week_template.php code - template file:
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.

برچسب: نویسنده: آیلین رضوانی تاريخ: دوشنبه 2 بهمن 1396 ساعت: 18:47

صفحه بندی

آرشیو مطالب

پيوندهای روزانه

لینک دوستان

خبرنامه