sync Drupal users with Moodle users (with HOWTO on course enrollments via Ubercart)

1. If my user clicks on the link to the course will he be automatically logged into Moodle?

No, this module does not support Single Sign On. They will be taken to the moodle login page, but after authenticating will go directly to the course page.

2. Also can I automatically create the Moodle user and password for each Drupal user without requiring that they do this manually themselves?

Not with this module. However, moodle supports bulk import / export of users. Or you can authenticate both drupal and moodle to LDAP.

3. Also how do I register them for the Moodle course? Would and admin log into Moodle and manually do each course registration or would the student?

Moodle is set up by default to allow students to enroll in courses themselves.

4. Finally do you know of handy way that I could sell the courses with Ubercart. I think Ubercart will let me sell access to a link. Could I use that to see the link to the course registration?

I do not know a way with ubercart. If they find out the URL to your moodle site they can bypass drupal. However, moodle does support paid access to courses. However, most of these are moodle questions and should be taken up with the folks at http://moodle.org/

This post is a few months old so I won't do an in-depth reply here... I just happened to run across this as I was searching for Moodle/Ubercart related topics. Here's how I did it...

1. NO special Drupal modules related to talking to Moodle.
2. Moodle courses set up as "products" in a course catalog on the Ubercart side of things.
3. Moodle user registration external database set to point at Drupal's user table (use the MD5 encryption setting for passwords).
4. Moodle enrollment also via external database... Moodle expects it's data in ONE table... in this case, I build a view (MySQL 5) similar to this (in the Drupal database) (in this case, I'm equating the Ubercart product's "model" to a Moodle course's "short name"...

CREATE VIEW enrollments(username,course) AS 
SELECT users.name, uc_order_products.model
FROM users, uc_orders,uc_order_products
WHERE users.uid = uc_orders.uid
AND uc_orders.order_id = uc_order_products.order_id
AND uc_orders.order_status = 'completed'

Anyone reading this feel free to get in touch if you want help setting it up.

Further Details (as requested)...

1. Log into your Moodle online classroom site using your administrative account.
2. Click on Users then Authentication then Manage authentication
3. Look for External database in the list. The "eye" icon next to it will probably be closed. Click on it so that the eye is open. Scroll down to the bottom and click the Save Changes button.
4. On the same page, on the row containing External database, click the Settings link.
5. Fill in the log-in information for your Drupal database. On most servers, the host field will likely be localhost. Enter the name, user and password to access your Drupal database. If it's been a while since you've set up Drupal (or someone else did it for you) you can find this info in the settings.php file located in your sites/default folder.
6. For the Table, enter users. For the Username field enter name. For Password field, enter pass. For Password format, select MD5 from the drop-down list.
7. As you scroll down, you'll see a section called Data Mapping—all of this is optional. For now, let's enter just one - find the Email address field, and type in mail.
8. Scroll down to the bottom of the page and click the Save changes button. If all you want to do is synch up your users, you're done. However, if you want to use Drupal/Ubercart to have your students pay their tuitions and enroll in classes...
9. On the same page, on the menu to your left, click on Courses then click on Enrolments
10. The checkbox next to External Database in the list of enrollment methods is probably not checked. If it isn't, then click it now to check it, then click the Save changes> button.
11. Click the Edit link in the Settings column next to External Database
12. Fill in the data for your Drupal database and server. This should be identical to the database settings we used above, until you get down to the enrol_dbtable field. Enter enrollments here (note that I'm using the American English spelling with two L's.
13. For enrol_localcoursefield enter shortname. For enrol_localuserfiel enter username. Leave enrol_db_localrolefield blank. For enrol_remotecoursefield enter course. For enrol_remoteuserfield enter username. Leave enrol_db_remoterolefield blank.
14. Leave everything else at the default settings. Scroll down to the bottom and click the Save changes button.
15. Now comes the tricky technical part. We need to create the enrollments "table" (it's a view, really). Most users don't have a tool giving them direct access to their database tables. If you're on a hosting service, you might have access to a program called phpMyAdmin in your hosting account's cPanel. That's the most commonly available route to the database, so I'll use that for the following steps...
16. Log into your web hosting account's control panel (usually some form of cPanel) and select phpMyAdmin from the database section.
17. You'll see a list of the databases in your hosting account's server on your left. Click the name of your Drupal database. You should now see a list of all the tables in your database. Confirm that you're in the right one and that you have Ubercart installed by noticing quite a few tables that have names starting with uc_
18. Click the SQL tab near the top of the page (it's between two tabs called Structure and Search).
19. You'll now have a large text edit area where you can type SQL commands. Cut and paste the code from the "short version" of these steps, way up above starting with CREATE VIEW and ending with AND uc_orders.order_status = 'completed'
20. Click the Go button.
21. If everything went according to plan and there were no errors, you will now have a new table in the list on your left, called enrollments. If you click on it, you'll see an empty table with two fields called username and course. This is the info that we'll be accessing with Moodle via the external database info that we've already entered.
22. We're done with cPanel and phpMyAdmin. You can log out of these if you wish.
23. Go back to your Moodle, and create the course for which you want external registration. Pay special attention to the course's short name, because we'll be entering that as Ubercart's Product SKU/Model.
24. On your Drupal site, create an Ubercart product for your course. Set the price as the enrollment fee, and set the SKU (aka Model) as the short name for the course created in the step above.
25. To test the system, use an email address that is currently unknown on both of your server. Create a new one using a free webmail like GMail or Yahoo if you need to. Go through the purchase process in Ubercart, and it will automatically create a new Drupal account for that email as part of the transaction completion process (there are various ways to test this, with PayPal's developer sandbox for example, or using Ubercarts built-in Credit Cart testing features). Once the Ubercart transaction is completed, you'll be emailed your new Drupal account username and password. Use this to log into your Moodle website. The very first time you attempt to log in, Moodle will automatically prompt you for information that it requires and doesn't have yet, such as First name, Surname, and City/town.

That's it! I'm thinking of making this whole thing into a Moodle Lesson if there's enough interest. I'll include the details I left out here, for example...

• How to synchronize fields with Drupal's profile module with the extra info that exists in a Moodle profile
• How to make the Enrollments view smarter, for example, to only consider a student enrolled in a course if they paid the tuition within the last 6 months.
• How to manage roles in a course using Drupal roles - for example, using Ubercart to give people the ability to purchase the teacher role and build their own courses (and using Ubercart's affiliate add-on so teachers can earn something from the incoming tuition fees).

If you're going to get in touch, please use my contact form instead of posting here if possible. I'll notice your communique much faster that way.

Happy teaching!

Hi kbrune, I'm working on a Similar project and need pretty much the same setup. I'd love it if you could give me some specifics on how you set this up.

regards,

Richard

Thank you kbrune for your comprehensive guide. That's what I was looking for! (specially after realizing that moodle's integration seems to be abandoned or not well documented)

Unfortunately I followed steps 1-8 very carefully not to forget anything but it seems I did something wrong or the behaviour is different from what I expected. After following your instructions 1-8 I created a new user in drupal and I went to moodle's user list but I didn't see the recently created user in drupal. I manually ran cron in moodle and drupal, just in case although this last step doesn't seem to be required, (I haven't configured it on my local server) and refreshed the user list with no success.

I also tried to do it reversely (created a moodle's user) but I got the same bad results. No syncronization at all :(

I'm sure to have provided correct database's name, user and password, as well as the correct name for the table (users), and fields (name, pass and mail), what am I missing? what am I doing wrong? Did I misunderstood its behaviour?

Thank you

EDIT: I don't know if that matters, but I am using both drupal and moodle in a local server using ubuntu and LAMP server.

A few ideas/clarifications...

• I'm thinking perhaps synchronization might not be the most accurate way to describe the link between Drupal and Moodle...
• This is one-way communication only. Moodle looks to the Drupal database when it cannot find information for the attempted log-in. At that point, if it finds the username and password of the user on the Drupal side and that username does not yet exist on the Moodle side, it will immediately take the user to their profile screen to add the extra info that Moodle requires, like first name, surname, and home city. Therefore, the username is not automatically created during a cron run... it's looking for a log-in attempt on the Moodle side.
• Log-ins, likewise, remain separate. Even though the Drupal username/password/email are used on both sites, each will require the user to log in. Also, password recovery needs to be done on the Drupal side only.

Thank you! I didn't understand how it works. I have tryed to log in with a Drupal username and it works (it asks me to add more fields -name, surname, city and country- but I think that could easily be solved by adding those fields to drupal profile and mapping the fields). It's a pitty, however, not to have a single login.

As far as I know (and as little as I know), other solutions could be to use LDAP integration (don't know exactly what this means) or OpenID (which I am not sure neither -I thought drupal had it on core, but I have found other modules as wel... )

EDIT: i added some profile fields, but since they are stored in different table and my sql knowledge is very poor, I do not know how to map the custom fields.

I just contacted kbrune to see if the documentation from #3 got posted anywhere on Moodle.org or Drupal.org. If it didn't, I will post it somewhere, since it looks great (haven't tried it yet).

I just added some tags & title to make this more visible to Google till then.

Here's an example query for doing the denormalization to pull the fields in that Moodle requires for a user:

select u.uid,
       u.name,
       u.pass,
       u.mail,
       MAX(CASE WHEN pf.name = 'profile_first_name' THEN pv.value ELSE NULL END) AS first_name,
       MAX(CASE WHEN pf.name = 'profile_last_name' THEN pv.value ELSE NULL END) AS last_name,
       MAX(CASE WHEN pf.name = 'profile_street' THEN pv.value ELSE NULL END) AS street,
       MAX(CASE WHEN pf.name = 'profile_city' THEN pv.value ELSE NULL END) AS city,
       MAX(CASE WHEN pf.name = 'profile_state_province' THEN pv.value ELSE NULL END) AS state_province,
       MAX(CASE WHEN pf.name = 'profile_postal_code' THEN pv.value ELSE NULL END) AS postal_code,
       MAX(CASE WHEN pf.name = 'profile_country' THEN pv.value ELSE NULL END) AS country,
       MAX(CASE WHEN pf.name = 'profile_phone_number' THEN pv.value ELSE NULL END) AS phone_number
from users as u
inner join profile_values as pv on pv.uid = u.uid
inner join profile_fields as pf on pf.fid = pv.fid
group by pv.uid

Note that this uses Profile module for the user fields. If you use Content Profile, then you'll be using a different query, but analogous technique.

(Based on a query from http://www.transio.com/content/how-denormalize-normalized-table-using-sql.)

I've just created a Drupal module that integrates Drupal Commerce with Moodle: http://drupal.org/node/1833212