General developer forum

get_records_sql returns one user/course/activity/etc when there are many

 
Picture of A Guy
get_records_sql returns one user/course/activity/etc when there are many
 

I'm having a strange problem. I have been a PHP/Moodle developer for a while now. But this is unique, to me. Thought I would post here to see if you could help me out.

I have a  PHP script that taps into the Moodle code. I've done this many times. So no problems there. The problem is that database queries are coming back from the database truncated. For example, I use this bit of code:


 


If I query it directly in MySQL it returns all the students who are students in the given course. In one particular course I was using for testing there were about 25 students.

SELECT c.id AS id, c.fullname, u.id as uid, u.username, u.firstname, u.lastname, u.email
FROM mdl_role_assignments ra, md_user u, mdl_course c, mdl_context cxt
WHERE ra.userid = u.id
AND ra.contextid = cxt.id
AND cxt.contextlevel =50
AND cxt.instanceid = c.id
AND c.id =23251
AND roleid = (select id from mdl_role where shortname='student');

But when I have it in my program like the below it only has an array of one student--most always the last one in the database. So if I did a var_dump of $students below I would see only one of the 25 users that this query would return for one example test course.

$students = $DB->get_records_sql("SELECT c.id AS id, c.fullname, u.id as uid, u.username, u.firstname, u.lastname, u.email
FROM {role_assignments} ra, {user} u, {course} c, {context} cxt
WHERE ra.userid = u.id
AND ra.contextid = cxt.id
AND cxt.contextlevel =50
AND cxt.instanceid = c.id
AND c.id =?
AND roleid = (select id from mdl_role where shortname='student')",array($courseID));

I have even formatted the queries as recordsets and also as non-parametized queries. Same behavior. I've cleared the caches. Same behavior. I've tried other courses. Same behavior.

It is not just this query. It is all queries where I query the database for users, courses, etc. So what am I missing here? Why are they not all coming back from a program with this SQL in it when they do when I query the database directly with just SQL?

 
Average of ratings: -
Davo
Re: get_records_sql returns one user/course/activity/etc when there are many
Core developersParticularly helpful MoodlersPlugin developers

As stated in the docs https://docs.moodle.org/dev/Data_manipulation_API#Getting_an_hashed_array_of_records the array returned by all of the get_records* functions in Moodle is indexed by the first field in the SELECT.

So, if you set the first field to c.id, then you will only get a single record for each course, as the others will be overwritten.

If you have Debugging on (as you should always have for any development), then you will get a warning message about this.

Solutions:

1. Choose a different field at the start of the SELECT statement (ra.id would be a good choice in this case)

2. Use one of the get_recordset* functions and loop through the results with foreach (this also reduces memory usage, but has less flexibility as you can only loop through the results, you cannot use any of the usual array manipulation functions on them).


 
Average of ratings: -