There are 2 lesson tables:

id | id_course | lesson_title 1 | 3 | Урок 1 2 | 3 | Урок 2 3 | 3 | Урок 3 4 | 4 | Урок 4 5 | 4 | Урок 5

history:

  id | id_lesson | email | date 43 | 1 | 123@gmail.com | 2018-07-09 13:17:05 44 | 2 | 123@gmail.com | 2018-07-09 13:18:05 45 | 3 | 123@gmail.com | 2018-07-09 13:19:05

The essence of what is, there are courses, they have lessons.

Different users go through a lesson -> a story is written that he passed a lesson.

It is necessary to draw the conclusion of people who have fully completed the entire course (i.e. all the lessons in the course).

Each course has its own conclusion of past people.

Here is my request, but not that:

 SELECT h.* FROM history h LEFT JOIN lesson l ON h.id_lesson = l.id WHERE l.id_course= '$id'

I need to display just a record with email and the date who and when finished the entire course.

Logically, as I think, you need to check the number of records in history with the id of each user's lessons and the number of lessons themselves in the lesson table for their specific course id ->

And then, if these count of records match, then display their email .

How to make a request?

 function total_score($id){ global $connection; $query = "SELECT l.id_course, h.email, MAX(h.date) FROM lesson l LEFT JOIN history h ON h.id_lesson = l.id WHERE l.id_course= '$id' GROUP BY l.id_course, h.email HAVING SUM(h.id_lesson IS NULL) = 0"; $res = mysqli_query($connection, $query); $total_score = array(); while($row = mysqli_fetch_assoc($res)){ $total_score[$row['email']] = $row; } return $total_score; }

Corrected, but now displays the user, if he passed at least one lesson, and not all three, as in this example.

  • What date to display? Last user lesson? And what is a "user"? How to distinguish one user from another? - Anton Shchyrov
  • Yes, the last lesson. User is different by email - Retarded Developer
  • ru.stackoverflow.com/questions/423439/… - Anton Shchyrov

2 answers 2

A set of queries with a sequential approximation to the result

 create table `lesson` ( `id` int, `id_course` int, `lesson_title` text ); 
 create table history ( `id` int, `id_lesson` int, `email` text, `date` date ); 
 Insert into `lesson` (`id`,`id_course`,`lesson_title`) values (1, 3, 'Урок 1'), (2, 3, 'Урок 2'), (3, 3, 'Урок 3'), (4, 4, 'Урок 4'), (5, 4, 'Урок 5') ; 
 Insert into `history` (`id`,`id_lesson`,`email`,`date`) values (43, 1, '100@gmail.com', '2018-07-10'), (44, 2, '100@gmail.com', '2018-07-11'), (45, 3, '100@gmail.com', '2018-07-12'), (46, 4, '110@gmail.com', '2018-07-13'), (47, 5, '110@gmail.com', '2018-07-14'), (48, 1, '200@gmail.com', '2018-07-15'), (49, 2, '200@gmail.com', '2018-07-16'), (50, 1, '300@gmail.com', '2018-07-17'), (51, 4, '300@gmail.com', '2018-07-18') ; 
 select * from `lesson`; 
 id |  id_course |  lesson_title
 -: |  --------: |  : -----------
  1 |  3 |  Lesson 1  
  2 |  3 |  Lesson 2  
  3 |  3 |  Lesson 3  
  4 |  4 |  Lesson 4  
  5 |  4 |  Lesson 5  

 select * from `history`; 
 id |  id_lesson |  email |  date      
 -: |  --------: |  : ------------ |  : ---------
 43 |  1 |  100@gmail.com |  2018-07-10
 44 |  2 |  100@gmail.com |  2018-07-11
 45 |  3 |  100@gmail.com |  2018-07-12
 46 |  4 |  110@gmail.com |  2018-07-13
 47 |  5 |  110@gmail.com |  2018-07-14
 48 |  1 |  200@gmail.com |  2018-07-15
 49 |  2 |  200@gmail.com |  2018-07-16
 50 |  1 |  300@gmail.com |  2018-07-17
 51 |  4 |  300@gmail.com |  2018-07-18

Get courses that the student took

 select distinct h.`email`, l.`id_course` from `lesson` l join `history` h on l.`id` = h.`id_lesson` 
 email |  id_course
 : ------------ |  --------:
 100@gmail.com |  3
 110@gmail.com |  four
 200@gmail.com |  3
 300@gmail.com |  3
 300@gmail.com |  four

Get a full list of classes that a student must take to complete the courses (but not all the classes the student has passed, for example 200@gmail.com did not complete 3 lessons 3)

 select cr.`email`, cr.`id_course`, L0.`id` as id_lesson from ( select distinct h.`email`, L.`id_course` from `lesson` L join `history` h on L.`id` = h.`id_lesson` ) as cr join `lesson` L0 on cr.`id_course` = L0.`id_course` order by cr.`email` asc, cr.`id_course` asc, L0.`id` asc 
 email |  id_course |  id_lesson
 : ------------ |  --------: |  --------:
 100@gmail.com |  3 |  one
 100@gmail.com |  3 |  2
 100@gmail.com |  3 |  3
 110@gmail.com |  4 |  four
 110@gmail.com |  4 |  five
 200@gmail.com |  3 |  one
 200@gmail.com |  3 |  2
 200@gmail.com |  3 |  3
 300@gmail.com |  3 |  one
 300@gmail.com |  3 |  2
 300@gmail.com |  3 |  3
 300@gmail.com |  4 |  four
 300@gmail.com |  4 |  five

Real table of passing / failing classes (lessons)

 -- полная таблица прохождения/непрохождения уроков select L1.`email`, L1.`id_course`, L1.`id_lesson`, H1.`email` from ( select cr.`email`, cr.`id_course`, L0.`id` as id_lesson from ( select distinct h.`email`, L.`id_course` from `lesson` L join `history` h on L.`id` = h.`id_lesson` ) as cr join `lesson` L0 on cr.`id_course` = L0.`id_course` ) L1 left outer join `history` H1 on H1.`email` = L1.`email` and H1.`id_lesson` = L1.`id_lesson` order BY L1.`email`, L1.`id_course`, L1.`id_lesson` 
 email |  id_course |  id_lesson |  email        
 : ------------ |  --------: |  --------: |  : ------------
 100@gmail.com |  3 |  1 |  100@gmail.com
 100@gmail.com |  3 |  2 |  100@gmail.com
 100@gmail.com |  3 |  3 |  100@gmail.com
 110@gmail.com |  4 |  4 |  110@gmail.com
 110@gmail.com |  4 |  5 |  110@gmail.com
 200@gmail.com |  3 |  1 |  200@gmail.com
 200@gmail.com |  3 |  2 |  200@gmail.com
 200@gmail.com |  3 |  3 |  null         
 300@gmail.com |  3 |  1 |  300@gmail.com
 300@gmail.com |  3 |  2 |  null         
 300@gmail.com |  3 |  3 |  null         
 300@gmail.com |  4 |  4 |  300@gmail.com
 300@gmail.com |  4 |  5 |  null

Choosing who failed the course

 -- ученик не прошел весь курс select distinct L1.`email`, L1.`id_course` from ( select cr.`email`, cr.`id_course`, L0.`id` as id_lesson from ( select distinct h.`email`, L.`id_course` from `lesson` L join `history` h on L.`id` = h.`id_lesson` ) as cr join `lesson` L0 on cr.`id_course` = L0.`id_course` ) L1 left outer join `history` H1 on H1.`email` = L1.`email` and H1.`id_lesson` = L1.`id_lesson` where H1.`email` is null 
 email |  id_course
 : ------------ |  --------:
 300@gmail.com |  3
 200@gmail.com |  3
 300@gmail.com |  four

We get the dates of the last classes of courses that the student took

  select h.`email`, L.`id_course`, max(h.`date`) as `max_date` from `lesson` L join `history` h on L.`id` = h.`id_lesson` group by h.`email`, L.`id_course` 
 email |  id_course |  max_date  
 : ------------ |  --------: |  : ---------
 100@gmail.com |  3 |  2018-07-12
 110@gmail.com |  4 |  2018-07-14
 200@gmail.com |  3 |  2018-07-16
 300@gmail.com |  3 |  2018-07-17
 300@gmail.com |  4 |  2018-07-18

RESULT: users past the course

 -- РЕЗУЛЬТАТ: пользователи прошедшие курс select L3.`email`, L3.`id_course`, T07.`max_date` from ( select distinct h.`email`, l.`id_course` from `lesson` l join `history` h on l.`id` = h.`id_lesson` ) L3 left outer join ( select distinct L1.`email`, L1.`id_course` from ( select cr.`email`, cr.`id_course`, L0.`id` as id_lesson from ( select distinct h.`email`, L.`id_course` from `lesson` L join `history` h on L.`id` = h.`id_lesson` ) as cr join `lesson` L0 on cr.`id_course` = L0.`id_course` ) L1 left outer join `history` H1 on H1.`email` = L1.`email` and H1.`id_lesson` = L1.`id_lesson` where H1.`email` is null ) L2 on L2.`email` = L3.`email` and L2.`id_course` = L3.`id_course` join ( select h.`email`, L.`id_course`, max(h.`date`) as `max_date` from `lesson` L join `history` h on L.`id` = h.`id_lesson` group by h.`email`, L.`id_course` ) as T07 on T07.`email` = L3.`email` and T07.`id_course` = L3.`id_course` where L2.`email` is null and L3.`id_course` = 4 -- Ограничиваем одним курсом ; 
 email |  id_course |  max_date  
 : ------------ |  --------: |  : ---------
 110@gmail.com |  4 |  2018-07-14

db <> fiddle here

  • Wow! Thank you very much. Only there are a couple of questions: 1. In the last query, you need to output also the "date" of the last lesson passed, accordingly this will be the time of the entire course. 2. I do not understand how the course id is determined, since in my function, I passed it as $ id and used it, but how? 3. And could you write a couple of sources with information about such requests so that you can understand them and disassemble yourself, otherwise it’s too cool for me for now. Thank you in advance. - Retarded Developer
  • The standard ansi sql, join joins were used, only there are more than two of them, so everything seems confusing. Each step in dbfiddle.uk ... uses the previous query and brings the query closer to the target. You can look at the results of the queries, and check how the join occurs over the data. - 2SRTVF
  • Thank you for spending time with me. Everything works as it should. I will understand what's what. - Retarded Developer

For all courses - somewhere like this:

 SELECT l.id_course, h.email, MAX(h.date) FROM lesson l LEFT JOIN history h ON h.id_lesson = l.id GROUP BY l.id_course, h.email HAVING SUM(h.id_lesson IS NULL) = 0

For one given course, add a WHERE.

  • Thank you, now I will test - Retarded Developer
  • Corrected, but now displays the user, if he passed at least one lesson, and not all three, as in this example. - Retarded Developer