foreach and for loop not getting all data from sql query

**Edited

I split the sql queries into 3 and now the queries look like this.

$sql_2 = "SELECT DISTINCT cr.id AS courseid,
        cr.fullname AS coursename,
        cr.idnumber AS idnumber,
        COUNT(DISTINCT ra.id) AS enrols, 
        COUNT(DISTINCT cc.timecompleted) AS completed 
        FROM {course} cr JOIN {context} ct ON ( ct.instanceid = cr.id ) 
        LEFT JOIN {role_assignments} ra ON ( ra.contextid = ct.id ) and ra.roleid = 5 
        LEFT JOIN {course_completions} cc ON (cc.course = cr.id) 
        WHERE cr.id = :course_id GROUP BY cr.fullname, cr.id";

$sql_3 = "SELECT DISTINCT 
        u.id AS userid,
        u.email AS email,
        u.lastaccess,
        c.id AS courseid
        FROM {user} u
        JOIN {role_assignments} ra ON ra.userid = u.id
        LEFT JOIN {context} ct ON ct.id = ra.contextid AND ra.roleid = 5
        LEFT JOIN {course} c ON c.id = ct.instanceid AND ct.contextlevel = 50
        WHERE c.id = :course_id";

$sql_4 = "SELECT DISTINCT 
        u.id AS userid,
        sc.name AS item,
        m.name AS module,
        cm.id AS cmid,
        cm.idnumber AS itemidnumber,
        sc.launch AS launch,
        cmc.completionstate AS completed,
        cmc.timemodified AS timecompleted
        FROM {user} u
        JOIN {role_assignments} ra ON ra.userid = u.id
        LEFT JOIN {context} ct ON ct.id = ra.contextid AND ra.roleid = 5
        LEFT JOIN {course} c ON c.id = ct.instanceid AND ct.contextlevel = 50
        LEFT JOIN {course_modules} cm ON cm.course = c.id
        LEFT JOIN {course_modules_completion} cmc ON cmc.userid = u.id AND cmc.coursemoduleid = cm.id
        LEFT JOIN {modules} m ON m.id = cm.module
        LEFT JOIN {scorm} sc ON sc.id = cm.instance
        WHERE c.id = :course_id and m.id = 18";

$courses = $DB->get_records_sql($sql_2, ["course_id" => $requestedcourseids]);
$users = $DB->get_records_sql($sql_3, ["course_id" => $requestedcourseids]);
$modules = $DB->get_records_sql($sql_4, ["course_id" => $requestedcourseids]);

I added foreach and for loop under the each result to get the multidimensional array result and got the module results like this

   "course": [
{
"courseid": 4,
"coursename": "Activities",
"idnumber": "",
"enrols": 6,
"completed": 1,
"users": [
    {
        "userid": 3,
        "email": "[e-mail]",
        "lastaccess": 1599379804,
        "modules": [
            {
                "userid": 3,
                "module": "scorm",
                "item": "Vitamin",
                "itemidnumber": "",
                "cmid": 24,
                "launch": 28,
                "completed": 1,
                "timecompleted": 1595575023
            },
            {
                "userid": 2,
                "module": "scorm",
                "item": "Vitamin",
                "itemidnumber": "",
                "cmid": 24,
                "launch": 28,
                "completed": 0,
                "timecompleted": 1593135761
            },
            {
                "userid": 4,
                "module": "scorm",
                "item": "Vitamin",
                "itemidnumber": "",
                "cmid": 24,
                "launch": 28,
                "completed": 1,
                "timecompleted": 1589359945
            },
            {
                "userid": 8,
                "module": "scorm",
                "item": "Vitamin",
                "itemidnumber": "",
                "cmid": 24,
                "launch": 28,
                "completed": null,
                "timecompleted": null
            },
            {
                "userid": 7,
                "module": "scorm",
                "item": "Vitamin",
                "itemidnumber": "",
                "cmid": 24,
                "launch": 28,
                "completed": null,
                "timecompleted": null
            }
        ]
    },
    {
        "userid": 2,
        "email": "[e-mail]",
        "lastaccess": 1599635370,
        "modules": [
            {
                "userid": 9,
                "module": "scorm",
                "item": "Vitamin",
                "itemidnumber": "",
                "cmid": 24,
                "launch": 28,
                "completed": 1,
                "timecompleted": 1595576987
            },
            {
                "userid": 3,
                "module": "scorm",
                "item": "Vitamin",
                "itemidnumber": "",
                "cmid": 24,
                "launch": 28,
                "completed": 1,
                "timecompleted": 1595575023
            },,,,

As you can see the module list only displays one module with each userid.

It should list 8 different modules per user but it loops only 6 times that is only the number of user count. I’d like to get users with all modules the user attempted.

Here is the code

foreach($courses as $completion) {

if(!is_null($course)) {
    $coursesdata[] = $course;
}
$course = array();
$course['courseid'] = $completion->courseid;
$course['coursename'] = $completion->coursename;
$course['idnumber'] = $completion->idnumber;
$course['enrols'] = $completion->enrols;
$course['completed'] = $completion->completed;
$course['users'] = [];

if(!empty($users)) {
    foreach($users as $userinfo) {
        for($i=0; $i<count($users); $i++) {
            $user = array();
            $user['userid'] = $userinfo->userid;
            $user['email'] = $userinfo->email;
            $user['lastaccess'] = $userinfo->lastaccess;
            $user['modules'] = array();

            if(!empty($modules)) {
                foreach($modules as $details) {
                    for($i=0; $i<count($modules); $i++) {
                        $module = array();
                        $module['module'] = $details->module;
                        $module['item'] = $details->item;
                        $module['itemidnumber'] = $details->itemidnumber;
                        $module['cmid'] = $details->cmid;
                        $module['launch'] = $details->launch;
                        $module['completed'] = $details->completed;
                        $module['timecompleted'] = $details->timecompleted;
                    }
                    if(!is_null($module)) {
                        $user['modules'][] = $module;
                    }
                }
            }
        }
        if(!is_null($user)) {
            $course['users'][] = $user;
        }
    }
}
}

Question: How can I put all data from $modules under $users array? At the moment it only loops the first element in $modules query.

I’ve tried methods suggested in the comments and answer but this is the closest one that I got so far.

Any help or tips would be appreciated.

Thanks in advance!

Answer

I found the issue in Moodle database calling get_records_sql

$modules = $DB->get_records_sql($sql_4, ["course_id" => $requestedcourseids]);

This returns an array indexed by the first field in the results. Which means in my case, only returns one set of an array from each userid. After I changed the sql query for moduleid(unique value) to come first it is now working as expected.

$sql_4 = "SELECT DISTINCT   cm.id AS moduleid,
                            u.id AS userid,
                            sc.name AS item,
                            m.name AS module,
                            cm.idnumber AS itemidnumber,
                            sc.launch AS launch,
                            cmc.completionstate AS completed,
                            cmc.timemodified AS timecompleted
                            FROM {user} u
                            JOIN {role_assignments} ra ON ra.userid = u.id
                            LEFT JOIN {context} ct ON ct.id = ra.contextid AND ra.roleid = 5
                            LEFT JOIN {course} c ON c.id = ct.instanceid AND ct.contextlevel = 50
                            LEFT JOIN {course_modules} cm ON cm.course = c.id
                            LEFT JOIN {course_modules_completion} cmc ON cmc.userid = u.id AND cmc.coursemoduleid = cm.id
                            LEFT JOIN {modules} m ON m.id = cm.module
                            LEFT JOIN {scorm} sc ON sc.id = cm.instance
                            WHERE c.id = :course_id and m.id = 18 and u.id = :userid";

Before it was like,

users:
    userid: 2,
    module:
      moduleid: 12,
    userid: 8,
    module:
      moduleid: 12
    userid: 9,
    module:
      moduleid: 12,

Now I get,

users:
    userid: 2,
    module:
      moduleid: 12,
      moduleid: 14,
      moduleid: 17,
    userid: 8,
    module:
      moduleid: 12,
      moduleid: 14,
      moduleid: 17,
    userid: 9,
    module:
      moduleid: 12,
      moduleid: 14,
      moduleid: 17,

This post helped me to sort it out. get_records_sql returns only one result using inner join in moodle