Hi All,
Banging my head against a wall here with an SQL query which works sometimes.
SELECT ue.id as id,
u.id AS UserID,
c.id AS CourseID,
cat.id AS CatID,
c.fullname, u.firstname, u.lastname, u.email, u.department, c.visible as coursevis,
cp.timeenrolled, cp.timestarted, cp.timecompleted,
cat.name AS catname,
cat.visible AS catvis,
e.roleid AS role,
uid.fieldid, uid.data
FROM {user_enrolments} AS ue
JOIN {user} AS u ON ue.userid = u.id
JOIN {user_info_data} AS uid ON uid.userid = u.id
JOIN {enrol} AS e ON e.id = ue.enrolid JOIN {course} AS c ON e.courseid = c.id
JOIN {course_categories} AS cat ON c.category = cat.id
LEFT JOIN {course_completions} AS cp ON (c.id = cp.course AND u.id=cp.userid)
WHERE e.roleid > 3 AND c.visible = 1 AND cat.visible = 1 AND uid.fieldid = :wibble
Params is...Array ( [wibble] => 1 )
This fails in Moodle with no error messages (full debugging and SQL error reporting are on).
However it works directly in PHPMyAdmin.
It also works if I add "AND c.id = 4" to the end of the SQL statement.
I'm stumped here - any advice and thoughts appreciated.
Cheers
Dave