How does course creation work in php and mysql

Question

I am working on an E-learning application and by far I have completed many things from a simple admin to teacher chat system to a virtual live classroom in PHP and MySQL.

Now came the most difficult part course creation and management operations with courses. You might be thinking that doing that in MySQL ain't hard and yes It is not difficult but the part where my logic stops working is the relations between tables like e.g. the relation of the course table and the lessons table used for the lessons inside the courses.

My question is that is there a way to relate both of the tables or should I create a separate database for the courses part of this project or something like that.

At this point I don't need any help in the code I need help in the logic used for relating these tables if possible.

This is my courses table below:

-- phpMyAdmin SQL Dump
-- version 4.9.2
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1:3306
-- Generation Time: May 22, 2020 at 06:11 AM
-- Server version: 10.4.10-MariaDB
-- PHP Version: 7.3.12

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `e-learning remake`
--

-- --------------------------------------------------------

--
-- Table structure for table `courses`
--

DROP TABLE IF EXISTS `courses`;
CREATE TABLE IF NOT EXISTS `courses` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `courseAuthor` varchar(1000) NOT NULL,
  `courseName` varchar(255) NOT NULL,
  `courseDesc` varchar(255) NOT NULL,
  `courseTags` varchar(1000) NOT NULL,
  `courseImg` varchar(255) NOT NULL,
  `isApproved` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=18 DEFAULT CHARSET=latin1;

This is my lesson table below:

-- phpMyAdmin SQL Dump
-- version 4.9.2
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1:3306
-- Generation Time: May 22, 2020 at 06:14 AM
-- Server version: 10.4.10-MariaDB
-- PHP Version: 7.3.12

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `e-learning remake`
--

-- --------------------------------------------------------

--
-- Table structure for table `lesson`
--

DROP TABLE IF EXISTS `lesson`;
CREATE TABLE IF NOT EXISTS `lesson` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `LessonName` varchar(255) NOT NULL,
  `LessonContent` varchar(255) NOT NULL,
  `CourseOfTheLesson` varchar(255) NOT NULL,
  `LessonPostDate` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
COMMIT;

The thing is that I want to know how to relate them so that If a student clicks on a course he/she will be redirected to the first lesson of that particular course and then so on.

Multiple tables can exist in the same database. Its recommended that related content be kept in the same database for ease of management. By relate are you talking about foriegn key relationships between tables like `course` and `lessons`? If you you should include `SHOW CREATE TABLE {tablename}` in the question by editing it. Its unclear how this relates to CRUD. — danblack, May 22 '20 at 06:04
The `lesson` table should contain a foreign key reference to the `courses` table, i.e. each lesson should point back to its parent course. If you also add a `sequence` column (or whatever name you want to give it) to the `lessons` table, that can specify the order of the lessons in a course. — kmoser, May 22 '20 at 07:09
you don't click on databases. On relations between lesson and course. If a lesson always relates to one course, add a column `course_id INT` as a column on `lessons`, and a [FK](https://dev.mysql.com/doc/refman/5.7/en/create-table-foreign-keys.html) constraint to keep it within the bounds of existing courses. Change ENGINE=MyISAM to ENGINE=Innodb otherwise these will not be enforced. — danblack, May 22 '20 at 07:10
https://stackoverflow.com/questions/757181/basics-of-foreign-keys-in-mysql — kmoser, May 22 '20 at 07:19
Note also that course tags would 'normally' be stored in a separate table — Strawberry, May 22 '20 at 07:19

How does course creation work in php and mysql

0 Answers0