moodle database

Question

I am trying to fetch data from database in Moodle I do this:

$sql = " SELECT bbb.name, ue.name AS UeName, bbb.timecreated, bbb.timemodified FROM mdl_bigbluebuttonbn AS bbb JOIN mdl_block_report_bbb_user_events AS ue ON ue.meeting_id = bbb.id ";

$result = $DB->get_record_sql($sql);

var_dump($result);die();

but with the get_record_sql method I only get the first record in the database what should I do get all records in the database?

score 1 · Answer 1 · answered Sep 08 '21 at 09:25

First of all, you should not hard-code the 'mdl_' prefix into your SQL queries, as that could be different on different sites (and is always different if you run PHPUnit or Behat tests against your site) - as per the documentation at https://docs.moodle.org/dev/Data_manipulation_API#Table_prefix please use the {tablename} format instead:

$sql = "SELECT bbb.name, ue.name AS UeName, bbb.timecreated, bbb.timemodified 
          FROM {bigbluebuttonbn} bbb
          JOIN {block_report_bbb_user_events} ue ON ue.meeting_id = bbb.id";

Once that is fixed, you can then use:

$DB->get_records_sql($sql);

(note the 's' on records) to get the results.

Be aware, that get_records_sql() will return an array indexed by the first field returned, so if your query produces more than one result with the same 'bbb.name', then you will not see those results (and get a debugging warning, if you have debugging on, which you should when doing development).

You will probably need to add something unique as the first field returned (e.g. "SELECT ue.id, bbb.name, ...")

moodle database

1 Answers1