Issue with finding a student with role id not equal to 5 and PSQL issue with concat

[tlock]

Fixed bug with finding a student with a different role id than 5.

/mod/bim/coordinator/find_student.php

• $students = get_role_users( 5, $context );
• $students = get_users_by_capability( $context, 'mod/bim:student',
• 'u.id,u.username,u.firstname,u.lastname,u.email',

•                  'u.lastname', '', '', '', '', false, true );

•   "concat(firstname,lastname) like '%$search%' )";

• "firstname || lastname like '%$search%' )";

[tlock]

This has shown another issue to fix :-

posts mirrored 1

actual answers / # required answers 0 / 1

Released / # Marked 0 / 0

Progress result 0 of 5

ERROR: syntax error at or near ")" at character 60

SELECT * FROM mdl_bim_marking WHERE bim=3 and userid in ( ) line 686 of lib/dmllib.php: call to debugging() line 609 of lib/dmllib.php: call to get_recordset_sql() line 930 of lib/dmllib.php: call to get_recordset_select() line 119 of mod/bim/lib/locallib.php: call to get_records_select() line 179 of mod/bim/lib/bim_rss.php: call to bim_get_marking_details() line 78 of mod/bim/student/view.php: call to bim_process_unallocated() line 120 of mod/bim/coordinator/find_student.php: call to show_student_details() line 55 of mod/bim/coordinator/find_student.php: call to bim_process_find_student() line 59 of mod/bim/coordinator/view.php: call to bim_find_student() line 97 of mod/bim/view.php: call to show_coordinator()

[djplaner]

Thanks Tim. Have made the changes will commit them later on tonights. Owe you a drink or two. Maybe at Moodlemoot'AU 2010

[djplaner]

Tim,

Just looking at the 2nd SQL error - locallib.php 119.

My assumption is that this is happening when there's no userid, hence it's empty and creates bad SQL.

I've added in a test for this, however, I'm trying to understand how it's happening. Seems to indicate a broader problem. I believe/hope that there should be a valid student id whenever it is called.

Any insights you can share are more than welcome.

[tlock]

If there is somewhere that calls the find student which requires the userid passed via the browser which may not be passed at that point could explain it.

[tlock]

Your change missed the $search where "%search% " below :-

• "concat(firstname,lastname) like '%$search%' )";
• "firstname || lastname like '%search%' )";

[djplaner]

Having to look at this again as I didn't factor in the division in databases around concat (I've been on MySQL for a long time). I've add some code to make it work nicer on different dbases and fixed this problem (hopefully)