General developer forum

get_records_sql returns unique records

 
Picture of Loganathan Natarajan
get_records_sql returns unique records
 

I am trying to execute the below query in Moodle and it displays unique records where as the same query displays multiple records in phpMyAdmin - database?  How can I execute in Moodle to returns multiple records like phpMyAdmin database?


$get_assignment_pending_course = $DB->get_records_sql('SELECT u.id AS user_id,u.firstname,u.lastname,ma.name AS assignment_name,mc.fullname AS course_name,mc.id AS course_id,ma.duedate AS assignment_duedate,mcm.id as assignment_id 

  FROM TBL_course mc 

  JOIN TBL_course_categories mcc on mcc.id = mc.category 

  JOIN TBL_course_modules mcm on mc.id = mcm.course AND mcm.module = 1 

  JOIN TBL_modules mm on mm.id = mcm.module JOIN TBL_enrol e on e.courseid = mc.id JOIN TBL_user_enrolments ue on ue.enrolid = e.id JOIN TBL_user u on u.id = ue.userid JOIN TBL_assign ma on mcm.instance = ma.id WHERE mcm.id IN(559,1286,1298,1306,1310,1314)');

  

  

 
Average of ratings: -
Davo
Re: get_records_sql returns unique records
Core developersParticularly helpful MoodlersPlugin developers

As described in the docs ( https://docs.moodle.org/dev/Data_manipulation_API ), the get_records_* functions return an array of results indexed by the first field in the results. So, in your case the array is indexed by the user.id field, meaning that there will only be one record per user in the results.

If this is not what you wanted, the either choose a different field to be first in the SELECT part of your query or use the get_recordset_sql function instead and loop through the results using foreach.

Note also that you should write {user} for database table names, not TBL_user, as that way Moodle will automatically insert the configured prefix for your system.


 
Average of ratings: Useful (1)
Picture of Loganathan Natarajan
Re: get_records_sql returns unique records
 

I used rand() function and fixed it.

 
Average of ratings: -
Tim at Lone Pine Koala Sanctuary
Re: get_records_sql returns unique records
Core developersDocumentation writersParticularly helpful MoodlersPlugin developers

Note that, when doing development, you should have Debugging set to Developer level. If you do, then you get all sorts of helpful warnings about possible problems in your code, including this easy-to-make mistake.

 
Average of ratings: -