sql select command doesnt work using get_records_sql, but works in phpmyadmin

sql select command doesnt work using get_records_sql, but works in phpmyadmin

by Yew Hong Ng -
Number of replies: 8

I thought this rather peculiar. I have the following sql command:

SELECT element FROM mdl_scorm_scoes_track WHERE scoid =4 ORDER BY element ASC LIMIT 0 , 100

which returns a column of values when run from phpmyadmin. But this code doesnt work:

$elements = get_records_sql("SELECT element FROM mdl_scorm_scoes_track WHERE scoid =4 ORDER BY element ASC LIMIT 0 , 100");

I tested by printing $elements in a table later in my code. However, if I change it to

$elements = get_records_sql("SELECT id, element FROM mdl_scorm_scoes_track WHERE scoid =4 ORDER BY element ASC LIMIT 0 , 100");

Then printing $elements gives me an Array. Adding a foreach loop i can see all the results and I know that it works fine. If I change the id to another field, for eg. scoid it returns something but not all records (in fact, for my db, only 1 record). Any idea????

Average of ratings: -
In reply to Yew Hong Ng

Re: sql select command doesnt work using get_records_sql, but works in phpmyadmin

by Samuli Karevaara -
Selecting only one column doesn't work in Moodle, as it always wants to return a so-called "associative array", where the first column is the key of the array, and the rest of the fields are values for that key, like:

select id, name...
gives
[1] => "Samuli" and so on...

In phpMyAdmin the query "select name..."  works as it just returns a table with the results in it, one column or more.

Your last query only returns one row as the first column that is returned is used as a "key", so it has to have distinct values in it. As your query seys "where scoid=4" you only have one value for the scoid (4) and only one row is returned.
Average of ratings: -
In reply to Samuli Karevaara

Re: sql select command doesnt work using get_records_sql, but works in phpmyadmin

by Yew Hong Ng -

Yes, I was suspecting as much ... thanks for the reply. Actually what i want to do is to get a list of all possible values of the element column, so something like this would solve my problem:

SELECT element,'0' from ...

Not a beautiful solution though, adding that dummy field.

Can you also enlighten me as to whether there are neat ways to add forms in moodle? It's not very nice having to code it manually. I'm new to php (did some asp.net developing before, but I'm not a pro, doing it juz for fun and educational purposes), and the lack of a datagrid seems daunting. Even simple controls like radio and listbox seem tedious compared to developing .net in visual studio.

Average of ratings: -
In reply to Yew Hong Ng

Re: sql select command doesnt work using get_records_sql, but works in phpmyadmin

by Martin Dougiamas -
Picture of Core developers Picture of Documentation writers Picture of Moodle HQ Picture of Particularly helpful Moodlers Picture of Plugin developers Picture of Testers
There are a bunch of functions related to forms in datalib.php, but one of the things targeted for Moodle 2.0 is a comprehensive way to do forms consistently throughout Moodle.
Average of ratings: -
In reply to Martin Dougiamas

Do forms consistently throughout Moodle (2.0)

by Jan Dierckx -

That would be great. Thanks!

I like the way you can create a table using the methods in tablelib. I was hoping you developers would also add something similar for writing forms. Are you still planning on using HTML QuickForm?

Average of ratings: -
In reply to Jan Dierckx

Re: Do forms consistently throughout Moodle (2.0)

by Martin Dougiamas -
Picture of Core developers Picture of Documentation writers Picture of Moodle HQ Picture of Particularly helpful Moodlers Picture of Plugin developers Picture of Testers
Yep that's the one. smile  Unless something better turns up.
Average of ratings: -
In reply to Martin Dougiamas

Re: sql select command doesnt work using get_records_sql, but works in phpmyadmin

by Yew Hong Ng -
Thanks, I was thinking more of functions to build forms using web controls, probably with some javascript to help. Is that coming in Moodle 2.0? Shan't ask when that'd be =)
Average of ratings: -
In reply to Yew Hong Ng

Re: sql select command doesnt work using get_records_sql, but works in phpmyadmin

by Samuli Karevaara -
About the SELECT: you could do "SELECT element, element". This way you can loop through the array values (a more natural way) instead of the keys (possible of course too).

About the form building: can't help you there. Myself, I'm from the school of handcoding everything, I like the full control it gives over the form elements. In Visual Studio it's very easy to quickly put together a form that has elements matching the database table cells. But things like liquid layout, many-to-many-relationships, non-trivial logic between the data and UI... It all requires to get your hands dirty.

Using ready-made functions to have some building blocks for HTML forms is a nice compromise.
Average of ratings: -
In reply to Samuli Karevaara

Re: sql select command doesnt work using get_records_sql, but works in phpmyadmin

by Yew Hong Ng -
I think the main advantage of visual studio is the way forms are built very easily through the dataset, databinding and the datagrid control. But i'm figuring a way to do somewhat the same stuff in php now. My virgin effort in moodle (more to learn than to create anything actually, but i needed this functionality) development is to create some kind of custom reporting functionality in the SCORM module. I'd like to see a table of course students with corresponding columns of SCORM tracks that I want to see. Am finishing it up.

I think Martin was wrong when he said there're some functions in datalib to help in building forms. Should be weblib.
Average of ratings: -