display data from get_recordset_sql

display data from get_recordset_sql

by Frederic Nevers -
Number of replies: 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 smile Is there a more appropriate function to achieve what I want to do?

Cheers,
Fred

Average of ratings: Useful (1)
In reply to Frederic Nevers

Re: display data from get_recordset_sql

by 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?
Average of ratings: -
In reply to Frederic Nevers

Re: display data from get_recordset_sql

by 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


Average of ratings: -
In reply to Barry Oosthuizen

Re: display data from get_recordset_sql

by 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 smile

Cheers,
Fred

Average of ratings: -
In reply to Barry Oosthuizen

Re: display data from get_recordset_sql

by Eloy Lafuente (stronk7) -
Picture of Core developers Picture of Documentation writers Picture of Moodle HQ Picture of Peer reviewers Picture of Plugin developers Picture of 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 smile

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.
Average of ratings: Useful (1)
In reply to Eloy Lafuente (stronk7)

Re: display data from get_recordset_sql

by 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 smile

Cheers,
Fred
Average of ratings: -
In reply to Barry Oosthuizen

Re: display data from get_recordset_sql

by 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

Average of ratings: -