Codeigniter add multiple category to a course (Academy-LMS)

Question

I am using Academy LMS Source code is in Github it's a Learning Management system and it is designed to have one category per course

I want to add multiple categories to one course

In /controllers/API.php I have

  // Fetch all the categories
  public function categories_get($category_id = "") {
    $categories = array();
    $categories = $this->api_model->categories_get($category_id);
    $this->set_response($categories, REST_Controller::HTTP_OK);
  }

  // Fetch all the courses belong to a certain category
  public function category_wise_course_get() {
    $category_id = $_GET['category_id'];
    $courses = $this->api_model->category_wise_course_get($category_id);
    $this->set_response($courses, REST_Controller::HTTP_OK);
  }

then in /models/Api_model.php, I have

// Get categories
public function categories_get($category_id)
{
    if ($category_id != "") {
        $this->db->where('id', $category_id);
    }
    $this->db->where('parent', 0);
    $categories = $this->db->get('category')->result_array();
    foreach ($categories as $key => $category) {
        $categories[$key]['thumbnail'] = $this->get_image('category_thumbnail', $category['thumbnail']);
        $categories[$key]['number_of_courses'] = $this->crud_model->get_category_wise_courses($category['id'])->num_rows();
    }
    return $categories;
}

// Get category wise courses
public function category_wise_course_get($category_id)
{
    $category_details = $this->crud_model->get_category_details_by_id($category_id)->row_array();

    if ($category_details['parent'] > 0) {
        $this->db->where('sub_category_id', $category_id);
    } else {
        $this->db->where('category_id', $category_id);
    }
    $this->db->where('status', 'active');
    $courses = $this->db->get('course')->result_array();

    // This block of codes return the required data of courses
    $result = array();
    $result = $this->course_data($courses);
    return $result;
}

then in /model/Crud_model.php, I got

    public function get_category_details_by_id($id)
    {
        return $this->db->get_where('category', array('id' => $id));
    }

 function get_category_wise_courses($category_id = "")
    {
        $category_details = $this->get_category_details_by_id($category_id)->row_array();

        if ($category_details['parent'] > 0) {
            $this->db->where('sub_category_id', $category_id);
        } else {
            $this->db->where('category_id', $category_id);
        }
        $this->db->where('status', 'active');
        return $this->db->get('course');
    }

in SQL course table has a column named category_id int(11) which stores one category per course I've changed it to TEXT format and put comma-separated values like 1,2,3 and used the ways like

$this->db->where_in('category_id',$category_id)

and

$this->db->like('category_id',$category_id)

and

$this->db->where("FIND_IN_SET(".$category_id.",category_id) >", 0);

and got no result I just need courses to have comma-separated values in the category_id column

Simply, I want courses to have multiple categories

DB tables https://pasteboard.co/JNSxO2kz.png

course table https://pasteboard.co/JNSy7g0.png

category table https://pasteboard.co/JNSyhlk.png

category_id table (Mapping Table suggested by @micheal-la-ferla ) https://pasteboard.co/JNSyGGn.png

anybody could help?

Answer 1

The way Academy-LMS is designed, it lets you only add 1 category per course. It limits the user and it can be frustrating.

One workaround which could work is to create a new mapping table with 3 columns as follows:

Field Name  | Data Type
------------+------------------
ID          | int(11) 
Course ID   | int(11)
Category ID | int(11)

Obviously you need to have the permission to create new tables to use this workaround. Not sure if this is possible for your implementation of Academy-LMS.

The step above will essentially create a one to many relationship between the course and the category, so that 1 course can then be part of multiple categories. (In reality, this would be a many to many relationship since I am assuming that a category may obviously belong to multiple courses).

Therefore the database design will be similar to the one I created here.

If you implement this change, you would then need to make the following changes to the code you have:

// Get categories
public function categories_get($category_id)
{
    if ($category_id != "") {
        $this->db->where('category_id', $category_id);
    }
    $this->db->where('parent', 0);
    $categories = $this->db->get('course_id')->result_array();
    foreach ($categories as $key => $category) {
        $categories[$key]['thumbnail'] = $this->get_image('category_thumbnail', $category['thumbnail']);
        $categories[$key]['number_of_courses'] = $this->crud_model->get_category_wise_courses($category['id'])->num_rows();
    }
    return $categories;
}

Essentially, what I did here was replace the parent in the categories with category_id and category with course_id. Not sure if these are correct. You will need to review these when reading from the database as I never used CodeIgniter.