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
Just to add to that, this is the same in Moodle 1.9.
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
And thx for the help
"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
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.
Yes this is the correct answer. 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
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
Im open for any good ideas
Greetings
Didi
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
Im open for any good ideas
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.
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
Each record of the table is one user. Like:
username|subject1|subject2|......
user1|boolean for true or false|boolean|......
Greetings Didi
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
Hey,
Thank your for the information, and the graeat tool. I think this will help me very much.
Greetings Didi
Thank your for the information, and the graeat tool.
Greetings Didi
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.
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.
Hey count_records is working for me, thank you very much for your replay.
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 obviously) 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
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