get_records_sql returns only one result using inner join in moodle

Question

I have used following code for getting category and corresponding course detail in MOODLE

global $DB;

$catlist = $DB->get_records_sql(
'SELECT ca.id,ca.name,ca.coursecount, c.id as course_id , c.category,
c.fullname, c.shortname , c.summary , c.format, c.startdate , c.timecreated 
FROM {course_categories} as ca inner join {course} as c on ca.id = c.category 
WHERE ca.parent > ? and ca.visible = ? and c.visible = ? ', array('0','1','1'));

echo "<pre>";print_r($catlist); echo "</pre>";

When i execute this query I am getting a result array with only one of the result rows, whereas executing the same sql in the mysql database directly returns many rows.

Table course_categories have 2 category 'account' and 'business' have active condition using visible =1 and also contain parent category. Table course have 4 course 2 of each related to category 'account' and 'business'

result like this:

Array
(
    [1] => stdClass Object
        (
            [id] => 1
            [name] => Accounts
            [coursecount] => 2
            [course_id] => 4
            [category] => 1
            [fullname] => Finance
            [shortname] => Finance
            [summary] => 

Lorem Ipsum is simply dummy text of the printing and typesetting industry. Lorem Ipsum has been the industry's standard dummy text ever since the 1500s, when an unknown printer took a galley of type and scrambled it to make a type specimen book. It has survived not only five centuries, but also the leap into electronic typesetting, remaining essentially unchanged. It was popularised in the 1960s with the release of Letraset sheets containing Lorem Ipsum passages, and more recently with desktop publishing software like Aldus PageMaker including versions of Lorem Ipsum.

            [format] => weeks
            [startdate] => 1461695400
            [timecreated] => 1461653620
        )

    [2] => stdClass Object
        (
            [id] => 2
            [name] => Business
            [coursecount] => 2
            [course_id] => 5
            [category] => 2
            [fullname] => Animal Health Honours (BSc(Hons))
            [shortname] => Animal Health Honours
            [summary] => 

Sl/NO, Course Name, Duration. HARDWARE & NETWORKING. 1, Advacnce Diploma in Computer Hardware Maintanance & Networking(ADCHMN), 12 Months.

            [format] => weeks
            [startdate] => 1461781800
            [timecreated] => 1461760598
        )

)

Can any one help to resolved this problem.

davosmith · Accepted Answer · 2016-05-05 10:21:21Z

4

The results of calling any of the get_records* functions in Moodle are returned as an array indexed by the first field in the results (which is very helpful if, for example, you get an array of user records, then want to jump straight to one record, based on the userid).

As your query returns the categoryid as the first field, only 1 result will be returned for each category (if you have debugging set to developer, you will get a warning about this).

To fix, either use a different field as the first value returned (in this case, c.id would be a good candidate), or use one of the get_recordset* functions instead and loop through the results with foreach.

answered May 5, 2016 at 10:21

davosmith

6,4572 gold badges16 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Khushboo Over a year ago

Its works.thank you so much. I have used 1st method use a different field as the first value and I get the expected result.

Collectives™ on Stack Overflow

get_records_sql returns only one result using inner join in moodle

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related