Inherited Code - Cannot figure out how to sort?

Beginners - Learning PHP
#1

I inherited this code and have very little php coding experience. I want to be able to sort by fullname or lastname. Can anyone point me in the right direction? I’ve spent the past couple hours trying things and only coming up with errors.

[php]
function get_content() {
global $CFG, $USER, $DB;

    if ($this->content !== NULL) {
        return $this->content;
    }

    $this->content = new stdClass();

    // get all the mentees, i.e. users you have a direct assignment to
    $allusernames = get_all_user_name_fields(true, 'u');
	
    if ($usercontexts = $DB->get_records_sql("SELECT c.instanceid, c.instanceid, $allusernames
                                                FROM {role_assignments} ra, {context} c, {user} u
                                                WHERE ra.userid = ?
                                                     AND ra.contextid = c.id
                                                     AND c.instanceid = u.id
                                                     AND c.contextlevel = ".CONTEXT_USER, array($USER->id))) {
        
       
        
		$this->content->text = '<ul>';         
        foreach ($usercontexts as $usercontext) {
            $this->content->text .= '<li><a href="'.$CFG->wwwroot.'/blocks/mentees_report/view.php?id='.$usercontext->instanceid.'">'.fullname($usercontext).'</a></li>';
        }
        $this->content->text .= '</ul>';
             
    }

    $this->content->footer = '';

    return $this->content;
}

[/php]

#2

We will need to know the value of $allusernames

[php]$allusernames = get_all_user_name_fields(true, ‘u’);[/php]

You could try changing this line to what I have below, reload the page, and copy & paste the output here.

Note: If this is a live website, you will break it :slight_smile:

[php]
$allusernames = get_all_user_name_fields(true, ‘u’);
var_dump($allusernames);
exit;
[/php]

#3

Thanks for your help.

string(90) “u.firstnamephonetic,u.lastnamephonetic,u.middlename,u.alternatename,u.firstname,u.lastname”

#4

First, why do this?
[php]$allusernames = get_all_user_name_fields(true, ‘u’);[/php]

If you are going to grab all those values anyway, you should just code the query that way. No need to make it dynamic.

You need to add an ORDER BY clause to do what you want.

#5

I didn’t write the code, nor can I get ahold of who did.

I did try the ORDER BY clause, but kept getting an error.

#6

What error?

Try this as well:

[php]$sql = "SELECT c.instanceid, c.instanceid, $allusernames
FROM {role_assignments} ra, {context} c, {user} u
WHERE ra.userid = ?
AND ra.contextid = c.id
AND c.instanceid = u.id
AND c.contextlevel = “.CONTEXT_USER . " – {$USER->id}”:

exit( $sql );[/php]

#7

Parse error: syntax error, unexpected ‘exit’ (T_EXIT)

#8

My keyboard didn’t catch the shift key. Change the colon to a semi colon.

#9

Parse error: syntax error, unexpected ‘$this’ (T_VARIABLE), expecting function (T_FUNCTION)

#10

Read what I put to temporarily modify…

#11

I’m not following you. I replaced the code and also the ;.

#12

There was not ‘$this’ in what I posted, so the error makes it look like you added extra to the sql string.

#13

It was in my original code.

#14

Please post your current code. Fixing syntax error without seeing the syntax is pretty hard.

#15

[php] function get_content() {
global $CFG, $USER, $DB;

    if ($this->content !== NULL) {
        return $this->content;
    }

    $this->content = new stdClass();

    // get all the mentees, i.e. users you have a direct assignment to
     $allusernames = get_all_user_name_fields(true, 'u');

	
    if ($usercontexts = $DB->get_records_sql("SELECT c.instanceid, c.instanceid, $allusernames
                                                FROM {role_assignments} ra, {context} c, {user} u
                                                WHERE ra.userid = ?
                                                     AND ra.contextid = c.id
                                                     AND c.instanceid = u.id
                                                     AND c.contextlevel = ".CONTEXT_USER, array($USER->id))) {
        
       
        
		$this->content->text = '<ul>';         
        foreach ($usercontexts as $usercontext) {
            $this->content->text .= '<li><a href="'.$CFG->wwwroot.'/blocks/mentees_report/view.php?id='.$usercontext->instanceid.'">'.fullname($usercontext).'</a></li>';
        }
        $this->content->text .= '</ul>';
             
    }

    $this->content->footer = '';

    return $this->content;
}[/php]
#16

Whats with the parenthesis around the table names in the query? Thats not valid SQL.

#17

I didn’t write the code. It functions and works. I just can’t get the right syntax to sort by last name. Spent around 8 hours yesterday trying everything under the sun.

#18

If you can provide a zip of everything I need to run it, I will take a look at it.

#19

Here is my guess, since you were not able to post the string that is passed to the function.
[php]
SELECT c.instanceid, c.instanceid, $allusernames
FROM {role_assignments} ra, {context} c, {user} u
WHERE ra.userid = ?
AND ra.contextid = c.id
AND c.instanceid = u.id
AND c.contextlevel = “.CONTEXT_USER . "
ORDER BY u.lastname DESC”[/php]

That is the string value passed to the function for the query. The second part being sent as an array, is the second parameter.

Look from this perspective.

[php]$sql = "SELECT c.instanceid, c.instanceid, $allusernames
FROM {role_assignments} ra, {context} c, {user} u
WHERE ra.userid = ?
AND ra.contextid = c.id
AND c.instanceid = u.id
AND c.contextlevel = “.CONTEXT_USER . "
ORDER BY u.lastname DESC”;
$usercontexts = $DB->get_records_sql( $sql, array($USER->id));[/php]

Sponsor our Newsletter | Privacy Policy | Terms of Service