以php / moodle表示的Mysql查询分组结果

Question

我有一个mysql查询：

SELECT
mdl_user_info_data.data,
mdl_user.firstname, 
mdl_user.lastname, 
mdl_grade_grades.itemid, 
mdl_grade_items.itemname,
mdl_course.cpd,
mdl_grade_grades.timecreated AS DATE
FROM mdl_user
INNER JOIN mdl_grade_grades ON mdl_user.id = mdl_grade_grades.userid
INNER JOIN mdl_grade_items ON mdl_grade_grades.itemid = mdl_grade_items.courseid
INNER JOIN mdl_course ON mdl_grade_items.courseid = mdl_course.id
INNER JOIN mdl_user_info_data ON mdl_user.id = mdl_user_info_data.userid
WHERE mdl_grade_grades.timecreated >= '1370091991'
AND mdl_grade_grades.timecreated <= '1370524243'
AND mdl_user_info_data.fieldid = 1

查询正确执行，并且在phpmyadmin中正确显示。有4条记录。其中一些记录具有相同的用户名，例如：

09118514    Elazabi Gillyman    108 Career Management1  1370508988
09118514    Elzaabi Gillyman    108 Career Management2  1370508988
03228812    Magria  Martinia    171 NULL    4           1370294859
03228812    Magria  Martinia    171 Making Budgeting    1370294859

但是当我尝试在php中使用它时，只有2条记录：

09118514    Elazabi Gillyman    108 Career Management2  1370508988
03228812    Magria  Martinia    171 Making Budgeting    1370294859

我假设以某种方式将具有相同id的记录分组，但我需要查看所有结果。我做错了什么？

下面是确切的查询：

$sql = "SELECT
        mdl_user_info_data.data,
        mdl_user.firstname, 
        mdl_user.lastname, 
        mdl_grade_grades.itemid, 
        mdl_grade_items.itemname,
        mdl_course.cpd,
        mdl_grade_grades.timecreated AS DATE
        FROM mdl_user
        INNER JOIN mdl_grade_grades ON mdl_user.id = mdl_grade_grades.userid
        INNER JOIN mdl_grade_items ON mdl_grade_grades.itemid = mdl_grade_items.courseid
        INNER JOIN mdl_course ON mdl_grade_items.courseid = mdl_course.id
        INNER JOIN mdl_user_info_data ON mdl_user.id = mdl_user_info_data.userid
        WHERE mdl_grade_grades.timecreated >= '1370091991'
        AND mdl_grade_grades.timecreated <= '1370524243'
        AND mdl_user_info_data.fieldid = 1";

$users = $DB->get_records_sql($sql);
var_dump($users);

var_dump将此结果设置为5(请注意，为了提高可读性，我已经删除了一些字段)：

array(5) { 
  [20038617]=> object(stdClass)#387 (7) { ["data"]=> string(8) "20038617" ["firstname"]=> string(9) "Bhekumuzi" ["lastname"]=> string(7) "Shongwe" ["itemid"]=> string(2) "72" ["itemname"]=> string(21) "Delegating Assessment" ["cpd"]=> string(4) "2.00" ["date"]=> string(10) "1370518594" } 

["03429262"]=> object(stdClass)#388 (7) { ["data"]=> string(8) "03429262" ["firstname"]=> string(7) "Shirlee" ["lastname"]=> string(5) "Levey" ["itemid"]=> string(3) "100" ["itemname"]=> string(27) "The IFRS Agenda part 2 quiz" ["cpd"]=> string(4) "0.25" ["date"]=> string(10) "1370431311" } 

[10001873]=> object(stdClass)#386 (7) { ["data"]=> string(8) "10001873" ["firstname"]=> string(6) "Bertha" ["lastname"]=> string(11) "Muchineripi" ["itemid"]=> string(3) "103" ["itemname"]=> string(45) "State of the nation analysis 2012 part 3 quiz" ["cpd"]=> string(4) "0.25" ["date"]=> string(10) "1370255012" } 

["09118514"]=> object(stdClass)#385 (7) { ["data"]=> string(8) "09118514" ["firstname"]=> string(6) "Elzabi" ["lastname"]=> string(7) "Gillman" ["itemid"]=> string(3) "108" ["itemname"]=> string(17) "Career Management" ["cpd"]=> string(4) "2.00" ["date"]=> string(10) "1370508988" } 

["03228812"]=> object(stdClass)#383 (7) { ["data"]=> string(8) "03228812" ["firstname"]=> string(5) "Maria" ["lastname"]=> string(6) "Martin" ["itemid"]=> string(3) "172" ["itemname"]=> string(90) "Managerial Megatrends and Financial Control Quiz - pass the quiz for automatic CPD logging" ["cpd"]=> string(1) "4" ["date"]=> string(10) "1370297204" } }

Answer 1

http://docs.moodle.org/dev/Data_manipulation_API#Getting_an_hashed_array_of_records的moodle文档表明，您正在使用的API调用(get_records_sql)检索按查询的第一列索引的记录的散列数组。文档上写着

该数组按查询返回的字段的第一列编制索引。因此，为了确保数据的一致性，最佳实践似乎是确保查询包含"id列“作为第一个字段。

“似乎是最佳实践？”嘿嘿。这是一个轻描淡写的模型。

因此，对于查询结果中mdl_user_info_data.data的每个不同值，您将在散列数组中获得一项。moodle get_records_sql方法的功能与设计时一样。

Moodle还提供了一个记录集API。这就是你想要的。http://docs.moodle.org/dev/Data_manipulation_API#Using_Recordsets

试试这样的东西。

$recordset = $DB->get_recordset_sql($sql);
foreach ($recordset as $user) {
    // Do whatever you want with this user's record
}
$recordset ->close(); // Don't forget to close the recordset!