display data from get_recordset_sql

display data from get_recordset_sql

- Frederic Nevers の投稿
返信数: 7
Hello,

I have managed to fetch data from my database using get_recordset_sql but I'm now struggling to display it the way I want.

Just so you know I have had a look at the documentation, read the X-Ref, and checked the dmblib.php file for comments and also looked in the CVS to check examples, but I still cannot work out how to get it done.

Okay, here is my problem. My query results look like this: id,userid,giver,text 3599,401,423,Interesting presentation in English

So far, it is normal. All I want to do is be able to use 'extract' data from that array so that I can turn it into something more meaningful such as 'Mary received a credit from John Smith for 'Interesting presentation in English'

I cannot for the life or me work out how to do it.

I would appreciate any pointers 笑顔 Is there a more appropriate function to achieve what I want to do?

Cheers,
Fred

評点平均:Useful (1)
Frederic Nevers への返信

Re: display data from get_recordset_sql

- Hubert Chathi の投稿
What part of the process are you having problems with? Figuring out the format of the return value for get_recordset_sql? Mapping userids to names? Something else?
評点平均: -
Frederic Nevers への返信

Re: display data from get_recordset_sql

- Barry Oosthuizen の投稿
Any reason you need to use get_recordset_sql instead of get_records_sql?

Here is what I think it would be with get_records_sql

$records = get_records_sql('insert your query in here');

foreach ($records as $record) {
$student = $record->userid;
$giver = $record->giver;
$text = $record->text;
echo '<p>'.fullname($student).' received a credit from '.fullname($giver).' for '.$text.'</p>';
}

Or if you have to use get_recordset_sql (sorry, I've not used this before but I guess this is how you could use it):

$recordset = get_recordset_sql('your query in here');

$records = recordset_to_array($recordset);

foreach ($records as $record) {
$student = $record->userid;
$giver = $record->giver;
$text = $record->text;
echo '<p>'.fullname($student).' received a credit from '.fullname($giver).' for '.$text.'</p>';
}

Cheers,

Barry


評点平均: -
Barry Oosthuizen への返信

Re: display data from get_recordset_sql

- Frederic Nevers の投稿
Hi Barry,

thanks a lot for your very detailed response. This is exactly what I needed. I was having trouble with working out how to 'break down' the results (array?) so that I could use each part of it individually later in my script.

I will give this a try today and get back to you 笑顔

Cheers,
Fred

評点平均: -
Barry Oosthuizen への返信

Re: display data from get_recordset_sql

- Eloy Lafuente (stronk7) の投稿
画像 Core developers 画像 Documentation writers 画像 Moodle HQ 画像 Peer reviewers 画像 Plugin developers 画像 Testers
Hi,

sorry Barry but your recordset proposed use is totally wrong (although works, I know).

get_recordset_xxx() functions were created to avoid loading all the data into one big array (that is the way get_record_xxx() functions use to work) in order to save memory (at the cost of being slightly slower).

So, in your proposal, you are suggesting to use get_recordset_xxx() and then use the (theoretically private) recordset_to_array() function. So you lose the memory benefits of the recordsets: save memory. To do so, just use directly the get_records_xxx() functions.

Instead, if you really want to use recordsets, because the number of records can become large, then you must do that in this way:


$rs = get_recordset_sql('SELECT .....');
while ($rec = rs_fetch_next_record($rs)) {
// Perform actions with the $rec record here
}
rs_close($rs); /// Close the recordset if not used anymore. Saves memory (optional but recommended).

First you get the recordset and then iterate over it, so only one record will be processed on each loop (memory won't be the sum of all records). Finally, you close the recordset.

That's all. Ciao 笑顔

Note: The syntax described above is valid only for Moodle 1.x versions. From Moodle 2.0 we'll be using one new (improved) syntax to handle recordsets. You can find more info in these Moodle Docs pages: Development:DML functions and Development:DB layer 2.0 migration docs.
評点平均:Useful (1)
Eloy Lafuente (stronk7) への返信

Re: display data from get_recordset_sql

- Frederic Nevers の投稿
I have used get_records_sql as my query will only return very few records and it works a treat.

Thanks again for your help.

I will try and use get_recordset_sql for a larger number of records and see if I can get it to work 笑顔

Cheers,
Fred
評点平均: -
Barry Oosthuizen への返信

Re: display data from get_recordset_sql

- Muhammad Hasnain の投稿

get_recordset_sql will return all the rows and get_records_sql may not do the same case as when it turns the records to object the primary key (Identifier) it uses may be same for more then one records.

Especially in case of joins you will be skipping records if you use get_records_sql

評点平均: -