Print exact sql statement executed - for debugging & documentation

Re: Print exact sql statement executed - for debugging & documentation

by baraa abd el-hady -
Number of replies: 1

i use it to see this query 

SELECT COUNT(s.userid) FROM mdl_assign_submission s LEFT JOIN mdl_assign_grades g ON s.assignment = g.assignment AND s.userid = g.userid AND g.attemptnumber = s.attemptnumber JOIN(SELECT DISTINCT eu10_u.id FROM mdl_user eu10_u JOIN mdl_role_assignments eu10_ra3 ON (eu10_ra3.userid = eu10_u.id AND eu10_ra3.roleid IN (5) AND eu10_ra3.contextid IN (1,34,4825,5033)) JOIN mdl_user_enrolments eu10_ue ON eu10_ue.userid = eu10_u.id JOIN mdl_enrol eu10_e ON (eu10_e.id = eu10_ue.enrolid AND eu10_e.courseid = ?) WHERE eu10_u.deleted = 0 AND eu10_u.id <> ? AND eu10_ue.status = ? AND eu10_e.status = ? AND eu10_ue.timestart < ? AND (eu10_ue.timeend = 0 OR eu10_ue.timeend > ?)) e ON e.id = s.userid WHERE s.latest = 1 AND s.assignment = ? AND s.timemodified IS NOT NULL AND s.status = ? AND (s.timemodified >= g.timemodified OR g.timemodified IS NULL OR g.grade IS NULL) [array ( 0 => '32', 1 => '1', 2 => 0, 3 => 0, 4 => 1480943800, 5 => 1480943800, 6 => '269', 7 => 'submitted', )]


but when i test it on the phpmyadmin 

it show me errors why that and how to see the same query  ( i want to get all assign that need to be graded as a report for all courses ) 


Error

There seems to be an error in your SQL query. The MySQL server error output below, if there is any, may also help you in diagnosing the problem.

ERROR: Unknown Punctuation String @ 877
STR: =>
SQL: SELECT COUNT(s.userid) FROM mdl_assign_submission s LEFT JOIN mdl_assign_grades g ON s.assignment = g.assignment AND s.userid = g.userid AND g.attemptnumber = s.attemptnumber JOIN(SELECT DISTINCT eu10_u.id FROM mdl_user eu10_u JOIN mdl_role_assignments eu10_ra3 ON (eu10_ra3.userid = eu10_u.id AND eu10_ra3.roleid IN (5) AND eu10_ra3.contextid IN (1,34,4825,5033)) JOIN mdl_user_enrolments eu10_ue ON eu10_ue.userid = eu10_u.id JOIN mdl_enrol eu10_e ON (eu10_e.id = eu10_ue.enrolid AND eu10_e.courseid = ?) WHERE eu10_u.deleted = 0 AND eu10_u.id <> ? AND eu10_ue.status = ? AND eu10_e.status = ? AND eu10_ue.timestart < ? AND (eu10_ue.timeend = 0 OR eu10_ue.timeend > ?)) e ON e.id = s.userid WHERE s.latest = 1 AND s.assignment = ? AND s.timemodified IS NOT NULL AND s.status = ? AND (s.timemodified >= g.timemodified OR g.timemodified IS NULL OR g.grade IS NULL) [array ( 0 => '32', 1 => '1', 2 => 0, 3 => 0, 4 => 1480943800, 5 => 1480943800, 6 => '269', 7 => 'submitted', )]

SQL query: Documentation

SELECT COUNT(s.userid) FROM mdl_assign_submission s LEFT JOIN mdl_assign_grades g ON s.assignment = g.assignment AND s.userid = g.userid AND g.attemptnumber = s.attemptnumber JOIN(SELECT DISTINCT eu10_u.id FROM mdl_user eu10_u JOIN mdl_role_assignments eu10_ra3 ON (eu10_ra3.userid = eu10_u.id AND eu10_ra3.roleid IN (5) AND eu10_ra3.contextid IN (1,34,4825,5033)) JOIN mdl_user_enrolments eu10_ue ON eu10_ue.userid = eu10_u.id JOIN mdl_enrol eu10_e ON (eu10_e.id = eu10_ue.enrolid AND eu10_e.courseid = ?) WHERE eu10_u.deleted = 0 AND eu10_u.id <> ? AND eu10_ue.status = ? AND eu10_e.status = ? AND eu10_ue.timestart < ? AND (eu10_ue.timeend = 0 OR eu10_ue.timeend > ?)) e ON e.id = s.userid WHERE s.latest = 1 AND s.assignment = ? AND s.timemodified IS NOT NULL AND s.status = ? AND (s.timemodified >= g.timemodified OR g.timemodified IS NULL OR g.grade IS NULL) [array ( 0 => '32', 1 => '1', 2 => 0, 3 => 0, 4 => 1480943800, 5 => 1480943800, 6 => '269', 7 => 'submitted', )]

MySQL said: Documentation

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '?) WHERE eu10_u.deleted = 0 AND eu10_u.id <> ? AND eu10_ue.status = ? AND eu10_e' at line 1 
In reply to baraa abd el-hady

Re: Print exact sql statement executed - for debugging & documentation

by baraa abd el-hady -

i figure  it now 

the ? marks replaced with the values in the array at the end and then remove the array the query works fine