Databases: get_records_sql returns only one result

Databases: get_records_sql returns only one result

de către Olli Savolainen-
Număr de răspunsuri: 23
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().
Media aprecierilor:Useful (1)
Ca răspuns la Olli Savolainen

Re: Databases: get_records_sql returns only one result

de către Eloy Lafuente (stronk7)-
Fotografia lui Core developers Fotografia lui Documentation writers Fotografia lui Moodle HQ Fotografia lui Peer reviewers Fotografia lui Plugin developers Fotografia lui Testers
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 surâs

Media aprecierilor:Useful (6)
Ca răspuns la Eloy Lafuente (stronk7)

Re: Databases: get_records_sql returns only one result

de către Olli Savolainen-
Wow. It actually worked. Thanks a bunch! surâs
Media aprecierilor: -
Ca răspuns la Olli Savolainen

Re: Databases: get_records_sql returns only one result

de către Utilizator șters-
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
Media aprecierilor: -
Ca răspuns la Utilizator șters

Re: Databases: get_records_sql returns only one result

de către Tim Hunt-
Fotografia lui Core developers Fotografia lui Documentation writers Fotografia lui Particularly helpful Moodlers Fotografia lui Peer reviewers Fotografia lui Plugin developers
Yes, your problem is that it is too simple face cu ochiul

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;
}
Media aprecierilor:Useful (2)
Ca răspuns la Tim Hunt

Re: Databases: get_records_sql returns only one result

de către Utilizator șters-
Great Tim... that did it!
Thanks
Media aprecierilor: -
Ca răspuns la Utilizator șters

Re: Databases: get_records_sql returns only one result

de către Utilizator șters-
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
Media aprecierilor: -
Ca răspuns la Utilizator șters

Re: Databases: get_records_sql returns only one result

de către Tim Hunt-
Fotografia lui Core developers Fotografia lui Documentation writers Fotografia lui Particularly helpful Moodlers Fotografia lui Peer reviewers Fotografia lui Plugin developers
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.
Media aprecierilor: -
Ca răspuns la Tim Hunt

Re: Databases: get_records_sql returns only one result

de către Utilizator șters-
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!!!
Media aprecierilor: -
Ca răspuns la Utilizator șters

Re: Databases: get_records_sql returns only one result

de către Howard Miller-
Fotografia lui Core developers Fotografia lui Documentation writers Fotografia lui Particularly helpful Moodlers Fotografia lui Peer reviewers Fotografia lui Plugin developers
Errr.... the information in phpdocs *IS* just the text from the comments isn't it??
Media aprecierilor: -
Ca răspuns la Howard Miller

Re: Databases: get_records_sql returns only one result

de către Utilizator șters-
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!
Media aprecierilor: -
Ca răspuns la Utilizator șters

Re: Databases: get_records_sql returns only one result

de către Tim Hunt-
Fotografia lui Core developers Fotografia lui Documentation writers Fotografia lui Particularly helpful Moodlers Fotografia lui Peer reviewers Fotografia lui Plugin developers
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.
Media aprecierilor: -
Ca răspuns la Eloy Lafuente (stronk7)

Re: Databases: get_records_sql returns only one result

de către Dale Davies-
Eloy you just saved my sanity! I did not realise get_records_sql would group the results.
Media aprecierilor: -
Ca răspuns la Eloy Lafuente (stronk7)

Re: Databases: get_records_sql returns only one result

de către 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.



Media aprecierilor: -
Ca răspuns la Paolo D'Arbitrio

Re: Databases: get_records_sql returns only one result

de către Tim Hunt-
Fotografia lui Core developers Fotografia lui Documentation writers Fotografia lui Particularly helpful Moodlers Fotografia lui Peer reviewers Fotografia lui Plugin developers

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.

Media aprecierilor: -
Ca răspuns la Olli Savolainen

Re: Databases: get_records_sql returns only one result

de către 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.

Media aprecierilor: -
Ca răspuns la Gabriel Rocha

Re: Databases: get_records_sql returns only one result

de către Tim Hunt-
Fotografia lui Core developers Fotografia lui Documentation writers Fotografia lui Particularly helpful Moodlers Fotografia lui Peer reviewers Fotografia lui Plugin developers
Can you post the exact code you are using to do the query.
Media aprecierilor: -
Ca răspuns la Tim Hunt

Re: Databases: get_records_sql returns only one result

de către Gabriel Rocha-

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);

Media aprecierilor: -
Ca răspuns la Gabriel Rocha

Re: Databases: get_records_sql returns only one result

de către Eloy Lafuente (stronk7)-
Fotografia lui Core developers Fotografia lui Documentation writers Fotografia lui Moodle HQ Fotografia lui Peer reviewers Fotografia lui Plugin developers Fotografia lui Testers
if this is a condition:
 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. face cu ochiul

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 surâs
Media aprecierilor: -
Ca răspuns la Eloy Lafuente (stronk7)

Re: Databases: get_records_sql returns only one result

de către 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

Media aprecierilor: -
Ca răspuns la Gabriel Rocha

Re: Databases: get_records_sql returns only one result

de către Gabriel Rocha-

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.

Media aprecierilor: -
Ca răspuns la Gabriel Rocha

Re: Databases: get_records_sql returns only one result

de către 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

Media aprecierilor: -