将值存储到循环中的每个用户

Question

In the app I have a select users and select lesson id. What I want to do is for each user selected I want to create new records in the DB (MySQL) with each lesson id selected. To better explain this is what it should look like:

DB:

In this scenario, 2 Users and 2 lessons have been selected so 4 records have been created:

Date | User_ID | Lesson_ID
-----|---------|-----------
NOW()|67A62    |12
-----|---------|-----------
NOW()|220A0    |12
-----|---------|-----------
NOW()|67A62    |8
-----|---------|-----------
NOW()|220A0    |8

My Code:

In my code it goes through the list side by side. So the first user_id in the list will be assigned the first lesson_id in the list. Instead of what I want which is assign all the users from the user_id list each item (lesson_id) in the lesson_id list

My current php code for storing values in the MySQL DB:

public function storeClass($attendees, $lesson_iDs) {
    $stmt = $this->conn->prepare("INSERT INTO watch (date, user_id, lesson_id) VALUES(NOW(), ?, ?)");

    $i = 0;

    foreach ($attendees as $index => $attendee) {

        $stmt->bind_param("ss", $attendees[$i], $lesson_iDs[$i]);
        $i++;
        $result = $stmt->execute();
        // var_dump($i);

        if(!$result){
            echo 'Error: ' .$stmt->error;
        }
    }

    $stmt->close();

    // result
    return $result;
}

Is it because I have the line $stmt->bind_param("ss", attendees[$i], lesson_iDs[$i]);

How can I change this code to do what I require it to do? I have very little experience in php so that is why I ask such a bad question

Answer 1

If I understand what you are trying to do correctly, the problem is not your query, but the loop. If you want to have a Cartesian product of user_id x lesson_id, you need another loop in your loop. For example:

foreach ($users as $user_id)
{
    foreach ($lessons as $lesson_id)
    {
        $stmt->bind_param("ss", $user_id, $lesson_id);
        $stmt->execute();
    }
}