Uploaded image for project: 'Moodle'
  1. Moodle
  2. MDL-27986

User Course graph fails to render on activity logs page

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Minor
    • Resolution: Duplicate
    • Affects Version/s: 2.0.3
    • Fix Version/s: None
    • Component/s: Other
    • Labels:
      None
    • Environment:
      Ubuntu 10.04
      PHP 5.3.2
    • Workaround:
      Hide

      Looks like it has to do with how named params passed to a sql query are handled.

      If you do something like...

      $params['id'] = 1;
      $params['date'] = 7;
      $sql = "select * from mdl_table where (id = :id and date > :date) or (id = :id and date < :date)";

      get_records_sql($sql,$params);

      In "/lib/dml/moodle_database.php" when it hits "public function fix_sql_params" it will find that the number of params passed is less then the number of named params in the query.
      ---663
      $named_count = preg_match_all('/(?<!:[a-z][a-z0-9_]*/', $sql, $named_matches); // :: used in pgsql casts
      $dollar_count = preg_match_all('/\$[1-9][0-9]*/', $sql, $dollar_matches);
      $q_count = substr_count($sql, '?');

      $count = 0;

      if ($named_count)

      { $type = SQL_PARAMS_NAMED; $count = $named_count; }
      if ($dollar_count) {
      if ($count) { throw new dml_exception('mixedtypesqlparam'); }
      $type = SQL_PARAMS_DOLLAR;
      $count = $dollar_count;

      }
      if ($q_count) {
      if ($count) { throw new dml_exception('mixedtypesqlparam'); }
      $type = SQL_PARAMS_QM;
      $count = $q_count;

      }

      if (!$count) {
      // ignore params
      if ($allowed_types & SQL_PARAMS_NAMED) { return array($sql, array(), SQL_PARAMS_NAMED); } else if ($allowed_types & SQL_PARAMS_QM) { return array($sql, array(), SQL_PARAMS_QM); } else { return array($sql, array(), SQL_PARAMS_DOLLAR); }
      }

      if ($count > count($params)) { $a = new stdClass; $a->expected = $count; $a->actual = count($params); throw new dml_exception('invalidqueryparam', $a); }


      If you changed this to what is below it should fix it...
      ----
      $named_count = preg_match_all('/(?<!:[a-z][a-z0-9_]*/', $sql, $named_matches); // :: used in pgsql casts
      $dollar_count = preg_match_all('/\$[1-9][0-9]*/', $sql, $dollar_matches);
      $q_count = substr_count($sql, '?');

      $count = 0;

      if ($named_count) { $type = SQL_PARAMS_NAMED; $count = $named_count; }

      if ($dollar_count) {
      if ($count)

      { throw new dml_exception('mixedtypesqlparam'); }
      $type = SQL_PARAMS_DOLLAR;
      $count = $dollar_count;

      }
      if ($q_count) {
      if ($count) { throw new dml_exception('mixedtypesqlparam'); }

      $type = SQL_PARAMS_QM;
      $count = $q_count;

      }

      if (!$count) {
      // ignore params
      if ($allowed_types & SQL_PARAMS_NAMED)

      { return array($sql, array(), SQL_PARAMS_NAMED); }

      else if ($allowed_types & SQL_PARAMS_QM)

      { return array($sql, array(), SQL_PARAMS_QM); }

      else

      { return array($sql, array(), SQL_PARAMS_DOLLAR); }

      }

      if ($count > count($params) ) {
      if($named_count) {
      if($named_count > count($named_matches[0]))

      { $a = new stdClass; $a->expected = $count; $a->actual = count($params); throw new dml_exception('invalidqueryparam', $a); }

      } else

      { $a = new stdClass; $a->expected = $count; $a->actual = count($params); throw new dml_exception('invalidqueryparam', $a); }

      }

      or you can use what was done in '/mod/workshop/locallib.php' to fix it for this instance.
      ---1332
      $params['workshopid1'] = $this->id;
      $params['workshopid2'] = $this->id;
      $sqlsort = $sortby . ' ' . $sorthow . ',u.lastname,u.firstname,u.id';
      $sql = "SELECT u.id AS userid,u.firstname,u.lastname,u.picture,u.imagealt,u.email,
      s.title AS submissiontitle, s.grade AS submissiongrade, ag.gradinggrade
      FROM

      {user}

      u
      LEFT JOIN

      {workshop_submissions}

      s ON (s.authorid = u.id AND s.workshopid = :workshopid1 AND s.example = 0)
      LEFT JOIN

      {workshop_aggregations}

      ag ON (ag.userid = u.id AND ag.workshopid = :workshopid2)
      WHERE u.id $participantids
      ORDER BY $sqlsort";
      $participants = $DB->get_records_sql($sql, $params, $page * $perpage, $perpage);

      if we use the same method in 'lib/datalib.php'
      ---1846
      $params['userid'] = $userid;
      $params['coursestart'] = $coursestart;

      return $DB->get_records_sql("SELECT FLOOR((time - :coursestart)/". DAYSECS .") AS day, COUNT AS num
      FROM

      {log}
      WHERE userid = :userid
      AND time > :coursestart $courseselect
      GROUP BY FLOOR((time - :coursestart)/". DAYSECS .")", $params);


      would become...


      $params['userid'] = $userid;
      $params['coursestart1'] = $coursestart;
      $params['coursestart2'] = $coursestart;
      $params['coursestart3'] = $coursestart;

      return $DB->get_records_sql("SELECT FLOOR((time - :coursestart1)/". DAYSECS .") AS day, COUNT AS num
      FROM {log}

      WHERE userid = :userid
      AND time > :coursestart2 $courseselect
      GROUP BY FLOOR((time - :coursestart3)/". DAYSECS .")", $params);

      and it is fixed. Either way should work, but I think it would be a good idea to 'fix' the 'fix_sql_params' function.

      Show
      Looks like it has to do with how named params passed to a sql query are handled. If you do something like... $params ['id'] = 1; $params ['date'] = 7; $sql = "select * from mdl_table where (id = :id and date > :date) or (id = :id and date < :date)"; get_records_sql($sql,$params); In "/lib/dml/moodle_database.php" when it hits "public function fix_sql_params" it will find that the number of params passed is less then the number of named params in the query. ---663 $named_count = preg_match_all('/(?<! : [a-z] [a-z0-9_] */', $sql, $named_matches); // :: used in pgsql casts $dollar_count = preg_match_all('/\$ [1-9] [0-9] */', $sql, $dollar_matches); $q_count = substr_count($sql, '?'); $count = 0; if ($named_count) { $type = SQL_PARAMS_NAMED; $count = $named_count; } if ($dollar_count) { if ($count) { throw new dml_exception('mixedtypesqlparam'); } $type = SQL_PARAMS_DOLLAR; $count = $dollar_count; } if ($q_count) { if ($count) { throw new dml_exception('mixedtypesqlparam'); } $type = SQL_PARAMS_QM; $count = $q_count; } if (!$count) { // ignore params if ($allowed_types & SQL_PARAMS_NAMED) { return array($sql, array(), SQL_PARAMS_NAMED); } else if ($allowed_types & SQL_PARAMS_QM) { return array($sql, array(), SQL_PARAMS_QM); } else { return array($sql, array(), SQL_PARAMS_DOLLAR); } } if ($count > count($params)) { $a = new stdClass; $a->expected = $count; $a->actual = count($params); throw new dml_exception('invalidqueryparam', $a); } — If you changed this to what is below it should fix it... ---- $named_count = preg_match_all('/(?<! : [a-z] [a-z0-9_] */', $sql, $named_matches); // :: used in pgsql casts $dollar_count = preg_match_all('/\$ [1-9] [0-9] */', $sql, $dollar_matches); $q_count = substr_count($sql, '?'); $count = 0; if ($named_count) { $type = SQL_PARAMS_NAMED; $count = $named_count; } if ($dollar_count) { if ($count) { throw new dml_exception('mixedtypesqlparam'); } $type = SQL_PARAMS_DOLLAR; $count = $dollar_count; } if ($q_count) { if ($count) { throw new dml_exception('mixedtypesqlparam'); } $type = SQL_PARAMS_QM; $count = $q_count; } if (!$count) { // ignore params if ($allowed_types & SQL_PARAMS_NAMED) { return array($sql, array(), SQL_PARAMS_NAMED); } else if ($allowed_types & SQL_PARAMS_QM) { return array($sql, array(), SQL_PARAMS_QM); } else { return array($sql, array(), SQL_PARAMS_DOLLAR); } } if ($count > count($params) ) { if($named_count) { if($named_count > count($named_matches [0] )) { $a = new stdClass; $a->expected = $count; $a->actual = count($params); throw new dml_exception('invalidqueryparam', $a); } } else { $a = new stdClass; $a->expected = $count; $a->actual = count($params); throw new dml_exception('invalidqueryparam', $a); } } — or you can use what was done in '/mod/workshop/locallib.php' to fix it for this instance. ---1332 $params ['workshopid1'] = $this->id; $params ['workshopid2'] = $this->id; $sqlsort = $sortby . ' ' . $sorthow . ',u.lastname,u.firstname,u.id'; $sql = "SELECT u.id AS userid,u.firstname,u.lastname,u.picture,u.imagealt,u.email, s.title AS submissiontitle, s.grade AS submissiongrade, ag.gradinggrade FROM {user} u LEFT JOIN {workshop_submissions} s ON (s.authorid = u.id AND s.workshopid = :workshopid1 AND s.example = 0) LEFT JOIN {workshop_aggregations} ag ON (ag.userid = u.id AND ag.workshopid = :workshopid2) WHERE u.id $participantids ORDER BY $sqlsort"; $participants = $DB->get_records_sql($sql, $params, $page * $perpage, $perpage); — if we use the same method in 'lib/datalib.php' ---1846 $params ['userid'] = $userid; $params ['coursestart'] = $coursestart; return $DB->get_records_sql("SELECT FLOOR((time - :coursestart)/". DAYSECS .") AS day, COUNT AS num FROM {log} WHERE userid = :userid AND time > :coursestart $courseselect GROUP BY FLOOR((time - :coursestart)/". DAYSECS .")", $params); — would become... — $params ['userid'] = $userid; $params ['coursestart1'] = $coursestart; $params ['coursestart2'] = $coursestart; $params ['coursestart3'] = $coursestart; return $DB->get_records_sql("SELECT FLOOR((time - :coursestart1)/". DAYSECS .") AS day, COUNT AS num FROM {log} WHERE userid = :userid AND time > :coursestart2 $courseselect GROUP BY FLOOR((time - :coursestart3)/". DAYSECS .")", $params); — and it is fixed. Either way should work, but I think it would be a good idea to 'fix' the 'fix_sql_params' function.
    • Affected Branches:
      MOODLE_20_STABLE

      Description

      If you go to a user's activity report (/course/user.php?user=1&id=1&mode=alllogs) the graph fails to render, returns a 404.

      If you go to the url that generates the graph (/course/report/log/graph.php?id=1&user=1&type=usercourse.png&date=0) you get "ERROR: Incorrect number of query parameters."

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              dougiamas Martin Dougiamas
              Reporter:
              mulroony Patrick Mulrooney
              Participants:
              Component watchers:
              Adrian Greeve, Jake Dallimore, Mathew May, Mihail Geshoski, Peter Dias, Sujith Haridasan
              Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: