How do I query the Moodle database for outside Moodle?

How do I query the Moodle database for outside Moodle?

by Deleted user -
Number of replies: 10

Okay, I know this are really basic questions for some of you, but I'm new to Moodle and to Databases, and can't find this info in the documentation (honestly, I really looked)...

First of all, where is the database with usernames, passwords and emails usually stored (what directory), and what is the file normally called?

Secondly, how do I "query" this database from outside Moodle? For example, users will pay and signup external to Moodle (we will be setting up external database authentication or LDAP authentication), and I want to be able to check the database (right away) when someone signs up to see if the username & email they choose, is valid.

Thirdly, I have MS-Access, but do I need any other programs in order to "query" the database?

Thanks

Average of ratings: -
In reply to Deleted user

Re: How do I query the Moodle database for outside Moodle?

by uday ramakrishna -

Hi Jeff,

I am not a pro with this but i will try to fill you in with what best I can. If you have installed moodle on windows using php, apache and mysql then you can use phpmyadmin a web based tool available in the modules and plugins section to view the database and also run sql queries. If you only wish to view the user login then as an admin user under the administration panel you can view the user list which should display the users enrolled and also their email id's.

Hope this gives you a start.

Best Regards

Uday

Average of ratings: -
In reply to uday ramakrishna

Re: How do I query the Moodle database for outside Moodle?

by Deleted user -

"...you can use phpmyadmin a web based tool available in the Modules and Plugins section to view the database and also run sql queries."

Yes, I actually installed this yesterday, and have been "browsing" thru the database to find the particular tables I need.

But what I actually want to do is access the database from outside Moodle, not from within it. Do you know if this can be done using MS-Access or something else?

Thanks

Average of ratings: -
In reply to Deleted user

Re: How do I query the Moodle database for outside Moodle?

by Mark Hilliard -

Jeff,

I have couple of suggestions though I may be shy on the specifics since I did this almost two years ago...

I access MySQL databases through MS Access quite a bit. Here is what we did.

First, get the MySQL ODBC client for your computer (I use have used the 3.51 MySQL client on Windows 2000 and Windows XP on my laptop, not sure about other ODBC clients) I got this from the MySQL site in the downloads area when I looked many months ago.

Second, after you install this on your Windows computer, you will need the rights and specific info to setup the the ODBC connections. If you don't have an account to the DB Moodle server you will need your Admin to set one up. If you have one the do this in Windows: Go to Programs, Admin Tools, Data Sources

Third create/ADD a new MySQL ODBC connection by clicking ADD new DSN account, then filling in DB server, DB name, description, Server IP #, DB Access account, password, etc.

If you are in the right screen you should see a TEST connection option that is helpful in trouble shooting.

Once this connection works, open MS Access and select the new ODBC source you created and connect to it with ACCESS.

Some tips to remember: one you are LIVE on your data, so if you change table data here you change in live on the server, BE CAREFUL. Remember your MS Access ODBC knows nothing about data integrity rules Moodle may use!!!   ODBC can be slow to respond especially if you are going between different systems (in my case Windows to Linux Moodle server) and over a Wide area Network so be patient to let tables refresh or populate.  There are some bugs in the ODBC link, so often I use the MS access REFRESS LINKS to be sure i have the latest data for reports, exports, etc.

Hope this helps you some. It has worked for me with several MySQL based open source apps (Moodle, SugarCRM, etc) so I hope it works for you too!

Mark H.

MPLS, MN  

Average of ratings: Useful (1)
In reply to Mark Hilliard

Re: How do I query the Moodle database for outside Moodle?

by Deleted user -

Mark Hilliard wrote "get the MySQL ODBC client for your computer (I use have used the 3.51 MySQL)"

Thanks so much for the info, it was very helpful. After much playing around though, I found out our host blocks the port on their firewall to the database (and doesn't open it for anybody), so for now I will have to stick with PHPMyAdmin!

I'll probably end up setting some kind of automated weekly export of the info I want, and use that file outside of Moodle instead of the actual DB file (which will be safer anyway).

3.51 MySQL is available for download (for anybody else who may need it)

http://dev.mysql.com/downloads 

Then scroll down to DRIVERS & CONNECTORS,

then click on CONNECTOR/ODBC 3.51

Average of ratings: -
In reply to Deleted user

Re: How do I query the Moodle database for outside Moodle?

by John Mc Hale -

If you are using MySQL as your Moodle Database providor, there is an excellent tool 'MySQL Query Browser', which will allow you manipulate and query any database schema mounted by your database server. Using Access the way Mark describes is also an excellent option for querying the Moodle database.

Average of ratings: -
In reply to John Mc Hale

Re: How do I query the Moodle database for outside Moodle?

by Deleted user -

John Mc Hale wrote "there is an excellent tool 'MySQL Query Browser'"

Thanks so much for the info, it was very helpful. And as I noted above in my reply to Mark Hilliard... After much playing around, I found out our host blocks the port on their firewall to the database (and doesn't open it for anybody), so for now I will have to stick with PHPMyAdmin!

I'll probably end up setting some kind of automated weekly export of the info I want, and use that file outside of Moodle instead of the actual DB file (which will be safer anyway).

MySQL Query Browser is available for download (if anybody else needs it) as part of the MySQL GUI TOOLS package, at:

http://dev.mysql.com/downloads

Then scroll down to MySQL TOOLS,

Then click on MySQL GUI TOOLS.

I'm not sure, because I was not able to test it, but the way I read it, these tools will only work with MySQL 5.0+, so if your using an older version of MySQL you have to see the earlier post about using MS-Access and the Connector to access your DB.

Average of ratings: -
In reply to Deleted user

Re: How do I query the Moodle database for outside Moodle?

by Wouter Maenhout -

Jeff,

If you have little php/html knowledge you can easily get all the things from the moodle data base that you want.

If needed i can help you out with that.

greetz

Average of ratings: -
In reply to Wouter Maenhout

How to connect moodle with crystal reports?

by Suhni Abbasi -
I am bit new user of moodle and have not so much knowledge of Mysql:
I want to know how to connect moodle database with crystal reports. Because i have to make some extra reports from the Moodle database.

I have already download and used the Mysql ODBC driver 3.51 and 5.1. The ODBC Connection is successfully built, but in the crystal reports it is not showing the hierarchy of tables. Please Help me how can I resolve this problem.


Suhni Abbasi
Average of ratings: -
In reply to Deleted user

Re: How do I query the Moodle database for outside Moodle?

by George Abraham -

Hi Jeff,

I have set up the lates Moodle Windows 23 on my laptop. I would like to know how to access the database. I mean you did it through phpmyadmin....how to do that?

Thanks

Average of ratings: -