0

I have a question on how I can extract data from Moodle based on a parameter thats "greater than" or "less than" a given value. For instance, I'd like to do something like:

**$record = $DB->get_record_sql('SELECT * FROM {question_attempts} WHERE questionid > ?', array(1));**

How can I achieve this, cause each time that I try this, I get a single record in return, instead of all the rows that meet this certain criteria.

Also, how can I get a query like this to work perfectly?

**$sql = ('SELECT * FROM {question_attempts} qa join {question_attempt_steps} qas on qas.questionattemptid = qa.id');**

In the end, I want to get all the quiz question marks for each user on the system, in each quiz.

Raymond Mlambo
  • 73
  • 6

2 Answers2

2

Use $DB->get_records_sql() instead of $DB->get_record_sql, if you want more than one record to be returned.

davosmith
  • 6,037
  • 2
  • 14
  • 23
  • Thanks for prompt response @Davosmith. How can I do a join query though, using the same method? I want to get data from more than one table in Moodle – Raymond Mlambo Jul 02 '16 at 19:34
  • 1
    You write join queries just like you do in ordinary SQL. SELECT fieldname1, fieldname2, ... FROM table1 JOIN table2 ON ... – davosmith Jul 03 '16 at 07:41
1

Thanks Davo for the response back then (2016, wow!). I did manage to learn this over time.

Well, here is an example of a proper query for getting results from Moodle DB, using the > or < operators:

$quizid = 100; // just an example param here
$cutoffmark = 40 // anyone above 40% gets a Moodle badge!!
$sql = "SELECT q.name, qg.userid, qg.grade FROM {quiz} q JOIN {quiz_grades} qg ON qg.quiz = q.id WHERE q.id = ? AND qg.grade > ?";

$records = $DB->get_records_sql($sql, [$quizid, $cutoffmark]);

The query will return a record of quiz results with all student IDs and grades, who have a grade of over 40.

Raymond Mlambo
  • 73
  • 6