Hi,
I'm doing moodle customization and I need to get data from an external database. I'm doing this with get_records_sql() to avoid the moodle database prefix; my SQL is also more complex than I could do with get_records(). Anyway, the problem is fairly simple: get_records_sql() returns an array with only one of the result rows, whereas executing the same sql in the mysql database directly returns many rows, just like it's supposed to.
$sql="SELECT table1.course, table1.stime," .
"table1.etime,table1.info," .
"table2.value, table2.id " .
"FROM table1, table2 " .
"WHERE table1.id=table2.eid " .
"AND stime < $now_stamp " .
"AND etime > $now_stamp " .
"AND user = 'user' " .
"AND type = 'type'";
echo $sql;
$p=get_records_sql($sql);
print_r($p);
Any thoughts about how to troubleshoot/start debugging this?
p.s. it seems that also get_recordset() only returns an object with that one row to get_records_sql().
In reply to Olli Savolainen
Re: Databases: get_records_sql returns only one result
Hi Olli,
all the get_records_XX() family of functions inside Moodle always returns one associative array, i.e. one array of records where the key is the first field in the SELECT clause.
So, if you get two records with the same value in the 1st field the functions will "group" them, returning the last one in the real recordset.
I remember myself some years ago trying to imagine what/why was happening, but after sometime hacking Moodle since then, it has demonstrated to work pretty well.
More if all Moodle tables must have one field, named "id", autonumeric (sequence, identily...) as primary key, that guaranties the associative array to be returned without problems.
You'll find some more guidelines at http://docs.moodle.org/en/Coding#Database_structures
Ciao
all the get_records_XX() family of functions inside Moodle always returns one associative array, i.e. one array of records where the key is the first field in the SELECT clause.
So, if you get two records with the same value in the 1st field the functions will "group" them, returning the last one in the real recordset.
I remember myself some years ago trying to imagine what/why was happening, but after sometime hacking Moodle since then, it has demonstrated to work pretty well.
More if all Moodle tables must have one field, named "id", autonumeric (sequence, identily...) as primary key, that guaranties the associative array to be returned without problems.
You'll find some more guidelines at http://docs.moodle.org/en/Coding#Database_structures
Ciao
In reply to Eloy Lafuente (stronk7)
Re: Databases: get_records_sql returns only one result
by Olli Savolainen -
Wow. It actually worked. Thanks a bunch!
In reply to Olli Savolainen
Re: Databases: get_records_sql returns only one result
by Deleted user -
I've been banging my head on this one for hours...
I'm using get_records_sql to make a db call, and I get back the following array:
Array ( [3] => stdClass Object ( [prereq_course_id] => 11 [prereq_quiz_id] => 25 [prereq_grade] => 84.5 [id] => 3 ) )
But I cannot for the life of me figure out how get the values out... I have tried mysql_fetch_row (which works when I use mysql_query), but I keep getting warnings like this:
Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result resource in...
I'm sure it has something to do with the 'stdClass Object', and it's probably very simple, but I can't figure it out.
Any help would be appreciated... thanks
I'm using get_records_sql to make a db call, and I get back the following array:
Array ( [3] => stdClass Object ( [prereq_course_id] => 11 [prereq_quiz_id] => 25 [prereq_grade] => 84.5 [id] => 3 ) )
But I cannot for the life of me figure out how get the values out... I have tried mysql_fetch_row (which works when I use mysql_query), but I keep getting warnings like this:
Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result resource in...
I'm sure it has something to do with the 'stdClass Object', and it's probably very simple, but I can't figure it out.
Any help would be appreciated... thanks
Yes, your problem is that it is too simple 
What you have got is just a plain PHP array. The array keys are the first column of your results set, which seems to be id, and the values are plain objects with one field for each column.
So you can do
$records = get_records_sql(...);
foeach ($records as $id => $record) {
echo $record->id, record->prereq_course_id, record->prereq_quiz_id, record->prereq_grade;
}
What you have got is just a plain PHP array. The array keys are the first column of your results set, which seems to be id, and the values are plain objects with one field for each column.
So you can do
$records = get_records_sql(...);
foeach ($records as $id => $record) {
echo $record->id, record->prereq_course_id, record->prereq_quiz_id, record->prereq_grade;
}
Great Tim... that did it!
Thanks
Thanks
Okay, I have get_records_sql all figured out now, and also figured out how to use insert_record and update_record!
Is there any information on how to use these functions?
get_record() & get_records()
Specifically, how do I tell them which table and which fields? Do they require an sql statement like get_records_sql?
Thanks
Is there any information on how to use these functions?
get_record() & get_records()
Specifically, how do I tell them which table and which fields? Do they require an sql statement like get_records_sql?
Thanks
Have you looked at where those functions are defined? Isn't there a but PHP doc comment above them explaining how they should be used? (I suppose you could also look at the generated PHPdocumentor documentation at http://phpdocs.moodle.org/! Since I always have the code open in my IDE, I don't use that myself and tend to forget about it.)
Alternatively, have you looked at some examples of where those functions are called?
I am not just refusing to to answer your question, I just think it is better if you learn how to find the answers to these questions yourself.
If you are still having troubles, get back to us.
Alternatively, have you looked at some examples of where those functions are called?
I am not just refusing to to answer your question, I just think it is better if you learn how to find the answers to these questions yourself.
If you are still having troubles, get back to us.
Thanks Tim... I ended up doing just that... finding the functions, looking at them, and then trying to find examples elsewhere that explained how to use them.
I was really just asking if there was further documentation because I find the comments at the beginning of each function can be rather vague for somebody who is not really familiar with the inner workings of Moodle.
After browsing thru the phpdocs.moodle.org (which I did not know existed), I found this a little more helpful than some of the comments with some of the functions, so this is what I was looking for... the docs are a little more specific than the comments!
So now that I am armed with phpdocs.moodle.org and xref.moodle.org I should be more dangerous than ever!!!
I was really just asking if there was further documentation because I find the comments at the beginning of each function can be rather vague for somebody who is not really familiar with the inner workings of Moodle.
After browsing thru the phpdocs.moodle.org (which I did not know existed), I found this a little more helpful than some of the comments with some of the functions, so this is what I was looking for... the docs are a little more specific than the comments!
So now that I am armed with phpdocs.moodle.org and xref.moodle.org I should be more dangerous than ever!!!
Errr.... the information in phpdocs *IS* just the text from the comments isn't it??
Well Howard, it appears you are right!!!
I guess the way it is laid out in phpdocs just seemed like it had more information, when in reality it is just easier to read so, I perceived it has being more detailed!
I guess the way it is laid out in phpdocs just seemed like it had more information, when in reality it is just easier to read so, I perceived it has being more detailed!
I often improve the PHP doc comments when I find one that is particularly bad. However, I am 'really familiar with the inner workings of Moodle', so I am not always sure if what I am writing is comprehensible.
If you have just worked out a particular function; think you could write a better comment; and know How to create a patch; then, by all means, create a patch and attach it to a tracker issue assigned to me, and I will review it and commit it.
If you have just worked out a particular function; think you could write a better comment; and know How to create a patch; then, by all means, create a patch and attach it to a tracker issue assigned to me, and I will review it and commit it.
Okay Tim, I will keep this in mind and see what I can do!!
In reply to Eloy Lafuente (stronk7)
Re: Databases: get_records_sql returns only one result
by Dale Davies -
Eloy you just saved my sanity! I did not realise get_records_sql would group the results.
Almost 6 years since I made this comment and this still catches me out from time to time!
Tim Hunt's comment here about using Debugging set to DEVELOPER level is certainly very good advice...
In reply to Eloy Lafuente (stronk7)
Re: Databases: get_records_sql returns only one result
by Paolo D'Arbitrio -
That tip saved my day, because in my clause just needed to change the order of the fields in the select to get the full recordset!
Thanks Eloy.
Note that, if you have Debugging set to DEVELOPER level, you get a warning when you get this wrong.
This is one of many reasons why you should always turn on developer debug when doing development.
In reply to Olli Savolainen
Re: Databases: get_records_sql returns only one result
by Gabriel Rocha -
Hi everybody!
I know that the first field in my sql statement must be the primary key for my array. I did this, therefore, it doesn't return all records.
I have two fields named "id", one from mgl_log table and the other from mdl_user table.
I put as the first field the id from mdl_log table, but the array uses as the key the id field from mdl_user table.
This way it doesn't return all records.
Can you post the exact code you are using to do the query.
here is the code:
$op = ($userid == 0) ? '!=' : '=';
$query = "SELECT c.id, q.shortname, c.userid, c.ip, c.course, c.action, c.url, c.info, c.module, c.time, p.id, p.firstname, p.lastname, p.email, p.city
FROM {$CFG->prefix}log c
inner join {$CFG->prefix}user p on c.userid = p.id
inner join {$CFG->prefix}course q on c.course = q.id where c.userid $op '$userid'";
if($date && $date != 0){
$query .= " and c.time > $begindate and c.time < $enddate";
}
$objLogs = get_records_sql($query);
In reply to Gabriel Rocha
Re: Databases: get_records_sql returns only one result
if this is a condition:
Anyway, as a general solution, you can always use column aliases and name that column as you want. Just replace "p.id" in the SELECT clause by "p.id AS newnameforcolumn" and you'll get results properly in the "newnameforcolumn" field of the result records.
Duplicate columns names won't work in some DBs at all (mainly because Moodle uses "named" references to them both in "get_recordsXXX() and get_recordsetXXX() functions instead of "indexed" references), so avoid them completely (or use the aliases) in the SELECT clause if you want to avoid "misterious" problems like "id suplantation" or other undesired effects.
My 2 cents, ciao
inner join {$CFG->prefix}user p on c.userid = p.id then, why return both ("c.userid" and "p.id") in the SELECT clause? I think you can delete "p.id" safely from there, as you have that info also available in the "c.userid" column. Anyway, as a general solution, you can always use column aliases and name that column as you want. Just replace "p.id" in the SELECT clause by "p.id AS newnameforcolumn" and you'll get results properly in the "newnameforcolumn" field of the result records.
Duplicate columns names won't work in some DBs at all (mainly because Moodle uses "named" references to them both in "get_recordsXXX() and get_recordsetXXX() functions instead of "indexed" references), so avoid them completely (or use the aliases) in the SELECT clause if you want to avoid "misterious" problems like "id suplantation" or other undesired effects.
My 2 cents, ciao
In reply to Eloy Lafuente (stronk7)
Re: Databases: get_records_sql returns only one result
by Gabriel Rocha -
thanks for replying me.
well, I already tried these solutions.
if I delete 'p.id' column, as well as I replace 'p.id' by its aliases like 'p.id as uid' for example, it also doesn't work.
I can't understand it.
if I keep only 'c.id' and 'p.id as uid' it works fine.
and if I keep till a amount of 9 columns it also works fine with aliases even without 'p.id' column.
I realy can't understant this...
Thanks
I'm not using more than one field called "id" in the select statement, so, i just deleted them.
Instead of using the field mdl_user.id from the table, I'm using mdl_log.userid
thanks guys.
In reply to Gabriel Rocha
Re: Databases: get_records_sql returns only one result
by Miguel Barreto -
All you gotta do it to add a field on your query that fits an unique result. On my case, wich has to be some grades from all courses, I chose mdl_grade_grades.id.
Hope it helps