Starting from two tables
lessons
lessons.lessons_id, lessons.lesson_number, lessons.course_id entre otras
and lessons_completed
lessons_completed.lessons_number, lessons_completed.user_id,
lessons_completed.completed
With the following code, LEFT JOIN works like a charm, however, delete all the records in the lessons_completed table and as it was obvious I stopped showing the exercises in the lessons table , and if I add user records in the lessons_completed table, the exercises appear with their respective check, that's fine, but it does not show me the exercises that have not yet completed the user.
to show the check I only use an if to the column:
if $lesson[completed]==1 {
muestro el check
} else {
NO OK
}
In the lessons_completed.completed column there are only values 1 the 0 does not exist because the record is created when the user clicks Completed with an insert
I will add the code as it was after your recommendations
public function get_lessons_completed($args = array()) {
global $db;
$user_id = !isset($args['user_id']) ? null : $args['user_id'];
$course_id = !isset($args['course_id']) ? null : $args['course_id'];
$lessons_completed = array();
$get_lessons_completed = $db->query
("SELECT
lessons.lesson_id,
lessons.course_id,
lessons.lesson_number,
lessons.lesson_intro,
lessons.lesson_text,
lessons.lesson_date,
lessons.lesson_prev,
lessons.lesson_top,
LC.lesson_number,
LC.completed
FROM lessons
LEFT JOIN lessons_completed LC
ON lessons.lesson_number = LC.lesson_number
WHERE lessons.course_id = 2 AND LC.user_id= 2 ORDER BY
lessons.lesson_number
") or _error(SQL_ERROR_THROWEN);
if($get_lessons_completed->num_rows > 0) {
while($lesson_completed = $get_lessons_completed->fetch_assoc()) {
$lessons_completed[] = $lesson_completed;
}
}
return $lessons_completed;
}
Up to here all excellent, Now the question is: how can I do or what I need to bring the exercises from the table lessons that the user has not yet completed? without duplicating the records with which the user already completed?
Here the results are shown with the code above, but there are for example in this COURSE ID in the table lessons other 6 exercises that I have tried to bring, but they are duplicated with those already complete the user if I make changes to the function
Try bringing the nulls with a:
OR NULL
and the result is that they are duplicated
I already tried changing INNER and RIGTH in my desperation but the result is the same, it brings me the same data in the same way as it is currently.
Maybe the ideal would be adding another JOIN but I'm not sure how to do it.
I hope friends can help me. I think I've never faced a situation I can not solve on my own, those JOINs bring me dizzy.