General developer forum

 
 
Picture of Dietmar Malli
Using moodle functions to execute SQL Query?
 
Hy, is there a function, to execute a SQL-Query inside moodle? I want to add a field into my own table. I read about insert_record(), and get_records_sql(), but get_records_sql is only able read, and insert_record is not able to execute raw SQL-Query-Code. Greetings Didi
 
Average of ratings: -
Picture of Justin Wyllie
Re: Using moodle functions to execute SQL Query?
 

Hi Dietmar

If you are talking about designing your tables as part of your development rather than something that happens at runtime you can use the XMLDB Editor. See here: http://docs.moodle.org/en/XMLDB_editor

It is under Site Administration.... Development.

It generates PHP code which you place in a file called install.php or upgrade.php in a module /db directory and when you hit the Notifications tab it will automatically update the tahble.

This *may* all be Moodle 2.0 though. I'm new to Moodle so not sure what the case is with previous versions.

Justin Wyllie

 

 
Average of ratings: -
Picture of Mark Johnson
Re: Using moodle functions to execute SQL Query?
Group Developers

Just to add to that, this is the same in Moodle 1.9.

 
Average of ratings: -
Picture of Dietmar Malli
Re: Using moodle functions to execute SQL Query?
 
No sry, I want to execute an SQL-Query like:
"ALTER TABLE `mdl_peertut` ADD `Angewandte Informatik` BOOLEAN NULL DEFAULT NULL"

And I'd like to do this with moodle-functions/with moodles database-connection ( if possible).
Further, I'd need to execute commands like this pretty often, so anything like hitting any tabs in Browser isn't an option for me.

Greetings Didi smile And thx for the help smile
 
Average of ratings: -
Picture of Hubert Chathi
Re: Using moodle functions to execute SQL Query?
 

The XMLDB editor can emit the PHP code needed to make table modifications.

But why would you need to do things like this pretty often?  You should generally avoid making changes to the database structure.

 
Average of ratings: -
Picture of sam marshall
Re: Using moodle functions to execute SQL Query?
Group DevelopersGroup Particularly helpful MoodlersGroup Testers

Yes this is the correct answer. smile To reiterate:

1. Good practice suggests you should not make database structure changes except during a system update.

2. If you do make database structure changes (during update), you should use the PHP code generated by xmldb (or make your own code that follows that pattern), because this will work on all databases - the SQL code is different for each database.

However if you want to execute arbitrary SQL, although it is not really a good idea to use it for this particular purpose, it IS possible. The command is $DB->execute($sql, $params); E.g.

$DB->execute('UPDATE {course} SET shortname = id WHERE fullname LIKE ?',
array('%silly%'));

(Code for Moodle 2.)

--sam

 
Average of ratings: -
Picture of Dietmar Malli
Re: Using moodle functions to execute SQL Query?
 
Thank you very much for this information.
I need this, to save information in my own Table. Where should I save this information instead? If you've got a better idea where I should store information for a table with about 14 fields and 1 to 1000 entries better than in the mysql-database, please share it with me smile
Im open for any good ideas smile Greetings Didi
 
Average of ratings: -
Picture of Hubert Chathi
Re: Using moodle functions to execute SQL Query?
 

Well, you definitely want to store it in the database.  What we're wondering, though, is why you need to alter the structure so often?  There is probably a way to design your database better.  But without more information about exactly what type of information you are storing, we can't help much.

 
Average of ratings: -
Picture of Dietmar Malli
Re: Using moodle functions to execute SQL Query?
 
Oh, sry. My fault. I want to make it possible for teachers or admins to dynamically add or delete subjects. Let's say i've got a field with usernames (students), and one for each subject. Now it should be possible to add subjects and delete them.
Each record of the table is one user. Like:
username|subject1|subject2|......
user1|boolean for true or false|boolean|......

Greetings Didi smile
 
Average of ratings: -
Picture of Justin Wyllie
Re: Using moodle functions to execute SQL Query?
 

Hi Didi

I can't see anything in that requirement that needs you to add new fields at runtime.

If you want teachers or admins to be able to add new subjects just have a table called subjects with a column for the unique id, one for the subject name and perhaps one for the admin owner. Then you are adding data rows to the table not fields to the structure. (Then maybe another table subject_details with all the details for the subject and a foreign key to the subject table id column).

If you want admins to then be able to add students to subjects you need a table perhaps with a column for subject id and one for student id. Again; it all comes down to designing what you want up front so you can use ordinary inserts, updates, selects and deletes to manipulate the data at runtime.

Broadly speaking the idea with database design would be to design the structure up front in such a way that at runtime, in use, everything could be handled by normal updates, inserts etc.

All the normal Moodle database calls will work with tables you put into the Moodle database.

This is a really helpful tool for visualising database design: http://www.dbschemaeditor.com/ It doesn't work directly with Moodle but may help you visualise your tables.

Justin

 
Average of ratings: -
Picture of Dietmar Malli
Re: Using moodle functions to execute SQL Query?
 
Hey,
Thank your for the information, and the graeat tool. smile I think this will help me very much. smile

Greetings Didi
 
Average of ratings: -
Picture of Swati Sra
Re: Using moodle functions to execute SQL Query?
 

Hey Guys,

I am very new to moodle, I want to run follwoing sql querys

SELECT count(timecompleted) FROM mdl_course_completions WHERE userid=$uid AND course=$courseid

SELECT count(timecompleted) from mdl_course_completions WHERE userid=$uid AND course=$courseid AND timecompleted <>NULL

Here I want to display the count of course activities(query1) and count of activities where time is not null. CAn any one help me to write these queries and get the output. I tried using get_records_sql but no luck.

Please help.

 
Average of ratings: -
Picture of Hubert Chathi
Re: Using moodle functions to execute SQL Query?
 

What do you mean by "I tried using get_records_sql but no luck."?  What happened when you used get_records_sql?  Which version of Moodle are you using? (there are slight differences between Moodle 1.x and 2.x)  Since your queries are both counts, you may want to look at the count_records and count_records_select functions.

 
Average of ratings: -
Picture of Swati Sra
Re: Using moodle functions to execute SQL Query?
 

Hey count_records is working for me, thank you very much for your replay.

 
Average of ratings: -
Picture of Justin Wyllie
Re: Using moodle functions to execute SQL Query?
 

Hi Dietmar

I think I saw a method execute() in the moodle database file. Ah - and by it I see a comment saying use a class method database_manager::execute_sql() to make changes to the database instead.

However database_manager execute_sql (in the class database_manager obviouslywink)  is declared as protected and not static.  So that confuses me. I guess you could inherit from it , make a public method in your class and use it that way.

It may also be worth looking at the database files in /lib/dml to see what is available generally

regards

Justin

 

 

 

 
Average of ratings: -
Picture of Justin Wyllie
Re: Using moodle functions to execute SQL Query?
 

Hi again Dietmar

Ok. I've twigged.

What it is saying is if you have to do this  then use the same code you can see in any of the module upgrade.php files e.g. for your adding a field case something like:

$dbman = $DB->get_manager();

$table = new xmldb_table('tablename');
$field = new xmldb_field('name', XMLDB_TYPE_CHAR, '255', null,
XMLDB_NOTNULL, null, null, 'id');

// Conditionally launch add field name
if (!$dbman->field_exists($table, $field)) {
$dbman->add_field($table, $field);
}

 

But I guess one might want to ask you why you are adding fields to tables at runtime in response to user input.

Regards

Justin

 

 

 

 
Average of ratings: -