problem with LEFT JOIN

You have a syntax error in your query, you are using 2 where , why?

should be like this:

     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,
     lessons_completed.lesson_number,
     lessons_completed.completed
     FROM lessons
     LEFT JOIN lessons_completed 
     ON lessons.lesson_number = lessons_completed.lesson_number 
     WHERE lessons.course_id=$course_id 
     AND lessons.user_id=$user_id

Can you test this query first on your database engine and see if it gives you expected results?

greetings

First: to mention that error 500 is related to your server (infrastructure).

Second: You have a syntax error, fix by putting a single WHERE and add an alias to your "lessons_completed" table:

Try first replacing your variables with static values:

   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 = 5 --Prueba con un valor estatico 
    AND lessons.user_id = 'guest' --Prueba con un valor estatico

If that works for you then something may be wrong with your php code.

You could also try your BD IDE first.

Thank you very much !! works perfectly with this code:

    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 = $course_id
   AND lessons.user_id = $user_id"

                )
                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;
    }

Accommodate the query as follows:

SELECT l.lesson_id, 
    l.course_id, 
    l.lesson_number, 
    l.lesson_intro, 
    l.lesson_text, 
    l.lesson_date, 
    l.lesson_prev, 
    l.lesson_top, 
    lc.lesson_number, 
    lc.completed FROM lessons l WHERE l.course_id=$course_id LEFT JOIN lessons_completed lc ON l.lesson_number = lc.lesson_number WHERE l.user_id=$user_id";

I decided to make another inquiry right here since it is the same issue, and I think that my head is already warming, because I'm sure I'm missing something.

With the code yesterday the 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 table lessons , 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.