'MOODLE: retrieve database query

$condgrade = $DB->get_recordset_sql('SELECT c.fullname, gi.itemtype, gi.itemname, 
                                     gg.userid, gg.finalgrade FROM 
                                     '.$CFG->prefix.'grade_items gi 
                                     JOIN '.$CFG->prefix.'grade_grades gg ON gi.id = 
                                     gg.itemid JOIN '.$CFG->prefix.'course c ON c.id = 
                                     gi.courseid WHERE 
                                     gi.itemtype IN ("course") AND  
                                     gg.userid = '.$USER->id.'');                                                        

foreach($condgrade as $cgg)
{
   $this->content->text .= html_writer::div($cgg->fullname.' : '.round($cgg->finalgrade).'%', array('class' => 'cours')); 
}

Is there any other way of retrieving data directly from the query, rather using foreach loop everytime something like in mysql : mysql_fetch_object($users);

moodle


Solution 1:[1]

$records = $DB->get_records_sql('SELECT c.fullname, gi.itemtype, gi.itemname, 
                                     gg.userid, gg.finalgrade FROM 
                                     {grade_items} gi 
                                     JOIN {grade_grades} gg ON gi.id = 
                                     gg.itemid JOIN {course} c ON c.id = 
                                     gi.courseid WHERE 
                                     gi.itemtype IN ("course") AND  
                                     gg.userid = :userid', array('userid' => $USER->id));

This will return an array of records.

Note also, the use of {tablename} notation, rather than manually inserting the $CFG->prefix and the use of ':userid' bound parameter + parameter list passed as "array('userid' => $USER->id)", which helps to protect you from SQL injection attacks.

Solution 2:[2]

Get a single record?

$user = $DB->get_record('user', array('id' => 1));

http://docs.moodle.org/dev/Data_manipulation_API#Getting_a_single_record

EDIT : As well as the recommendations from davosmith, you also need to have a unique id in the first column. Also you can just use $DB->get_records_sql() - use $DB->get_recordset_sql() if you expect a large amount of data. If you do use get_recordset_sql() then you will also need to check the query is valid with $condgrade->valid() and close it after $condgrade->close();

$sql = "SELECT gg.id,
               c.fullname,
               gi.itemtype,
               gi.itemname, 
               gg.userid,
               gg.finalgrade
        FROM {grade_items} gi 
        JOIN {grade_grades} gg ON gi.id = gg.itemid
        JOIN {course} c ON c.id = gi.courseid
        WHERE gi.itemtype = :itemtype
        AND gg.userid = :userid";
$params = array('itemtype' => 'course', 'userid' => $USER->id);
$condgrade = $DB->get_records_sql($sql, $params);

This will return an array of record objects. The array key will be whatever is in the first column. eg:

$condgrade[1] = $recordobject;

So you can access a record object directly eg:

with $condgrade[1]->fullname

But you won't know what the id is unless you retrieve it from the database. So I'm not clear why you want to access the object directly? If you can give me an example of how you would use this directly then I can try to give an answer.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 davosmith
Solution 2

Related Questions