Get role types 1,2,3,4

Get role types 1,2,3,4

by Monika Gujar -
Number of replies: 4

What is wrong in this query?

I am writing below code in locallib.php

function tgwhiteboard_getteacherdetail() {

    global $DB;

$sql = "SELECT DISTINCT u.username FROM {user} u";

$sql .= "JOIN {role_assignments} ra ON ra.userid = u.id";

$sql .= "WHERE ra.roleid in (1,2,3,4)";

$teacherlist = $DB->get_records_sql($sql);

    return $teacherlist;

}

And if I am writing the same in phpMyAdmin then it works,

SELECT DISTINCT u.username FROM mdl_user u JOIN mdl_role_assignments ra ON ra.userid = u.id WHERE ra.roleid in (1,2,3,4)


Average of ratings: -
In reply to Monika Gujar

Re: Get role types 1,2,3,4

by Mark Johnson -
Picture of Core developers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers

If you output $sql before you run it (or view it in a debugger) you'll probably see the error. It reads something like this:

SELECT DISTINCT u.username FROM {user} uJOIN {role_assignments} ra ON ra.userid = u.idWHERE ra.roleid in (1,2,3,4)

When concatenating strings like this, remember to add a space at the end of each chunk. The Moodle SQL coding style actually suggests multi-line strings for SQL.

Do you have developer debugging turned on? I feel like it should have given you an error showing this.

Average of ratings: Useful (1)
In reply to Mark Johnson

Re: Get role types 1,2,3,4

by Dominique Palumbo -
Picture of Particularly helpful Moodlers Picture of Plugin developers
Hi,

you're right Mark with the space.

The more easy it's do it like that no need to concatenate. And More easy to copy paste it to an sql editor.

$sql = "SELECT DISTINCT u.username FROM {user} u
                  JOIN {role_assignments} ra ON ra.userid = u.id
              WHERE ra.roleid in (1,2,3,4)";

Your sql string is like that :

SELECT DISTINCT u.username FROM {user} uJOIN {role_assignments} ra ON ra.userid = u.idWHERE ra.roleid in (1,2,3,4)


Average of ratings: -
In reply to Monika Gujar

Re: Get role types 1,2,3,4

by Andreas Grabs -
Picture of Core developers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers Picture of Translators

Hi, you have to select a unique id column too.

SELECT DISTINCT u.id, u.username ...

Best regards Andreas

Average of ratings: -
In reply to Monika Gujar

Re: Get role types 1,2,3,4

by Monika Gujar -
Thanks to all of you... smile
Issue has been resolved

function tgwhiteboard_getteacherdetail() {
    global $DB;
     $sql  = "SELECT DISTINCT u.id, CONCAT(u.firstname, ' ', u.lastname) AS username FROM {user} u ";
     $sql .= "JOIN {role_assignments} ra ON ra.userid = u.id ";
     $sql .= "WHERE ra.roleid in (1,2,3,4)";
     $teacherlist = $DB->get_records_sql($sql);
    return $teacherlist;
}

id was compulsory Yes
Average of ratings: Useful (1)