Need suggestions for working with mysql data through MODx interface

Hi everyone,

I could really use some advice from some of you MODx power users regarding working with a database table through MODx. Here goes:

I’m building a course catalog system for a major university using MODx (when all is said and done, this will be an awesome project for showcasing MODx and I’ll be sure to share it with the community). I need to pull the course information in from another system, but all they can provide is a CSV (worth mentioning: every course has a unique course ID). No problem there -- I’ve already figured out how to get the information into my MODx database (in its own table) using the csv2db module.

My problem is, I now need this data to be managed through MODx by other less technical people. I thought about making a new MODx resource for each course and then using a TV with a @select binding to pull from the table (thinking I could manually set each MODx resource pagetitle as the course ID and then match it that way). The problem is, this won’t be editable -- it would just pull from the DB and mirror what’s already there.

What I think I need to do instead is actually create the new MODx resource at the same time I create the mysql table for the courses via straight PHP/MySQL (or a module) and tie it to the other table with either a TV or a foreign key, but I have no idea how to go about this. Can anyone walk me through the process of adding MODx resources directly to the database, with or without using the API? Or, if I’m going about this all wrong, can anyone show me a better method overall? I should mention that I’m average in PHP/MySQL but can usually figure it out if you give me the basics.

Thanks!

What I would do is use one doc to as the course viewer and create a snippet to retrieve the data (rather than TVs) and return the retrieved data via placeholders.

As far as managing the data there are several ways that can be done from a building a DB Editor Module to writing another snippet to allow updates to the table data that is only available to manager users. The snippet would check to see if the user was a logged in manager, display the course data and allow changes and saves.

If the data import is going to be a one time thing and there aren’t too many courses you could use the CSV to MODx Addon that can be found in the Extras Section and map the tables to document variables.

I am sure someone more dev’y than I can better explain my two options.

That’s a great idea -- I think a single "viewer" page is definitely a better option than trying to manipulate the MODx document structure from straight PHP/MySQL.

When you suggested writing a snippet, do you mean something like this: http://wiki.modxcms.com/index.php/DB_query_and_Placeholders? What advantage would a snippet have over TVs?

Also, are you aware of any pre-built modules or snippets that would handle the DB editing? I’ve looked around but haven’t seen much. I did notice MakeForm (http://modxcms.com/forums/index.php/topic,2553.0.html), which seems like it could simplify the process.

maybe this could be helpful "Module Maker". download it here: http://modx.cmsthingies.com/module_maker.html

documentation included

The basic idea behind this module is to allow a manager user to add data to a table in a friendly manner, for later use in, for example, a snippet. The basic module allows a user to input data in a friendly manner into a single table, although over time, the module has been extended to allow automatic links to be made to different tables, allowing, for example, multi-language support, etc.

support thread: http://modxcms.com/forums/index.php?topic=39174.0

Thanks! Module Maker is exactly what I was looking for. I am experiencing an error getting it to run (Fatal error: Call to undefined method module_maker::module_maker). Any ideas? I don’t want to tangent this thread and will gladly ask in the Module Maker support thread if it’s not something obvious.

I am experiencing an error getting it to run (Fatal error: Call to undefined method module_maker::module_maker). Any ideas?

just tested it myself and got the same error. hopefully there will be an easy fix.

this could be interesting for your project, too:

[Snippet] loopDbChunk (with pagination) http://modxcms.com/forums/index.php/topic,14654.0.html

The good news is, I have the basics of the site working using a combination of loopDbChunk and some template variables on each main section to tie the course to the DB table, using @INHERIT to prevent having to manually add it to sub-pages. When I’m completely finished, I’ll be sure to post a link to the finished site!

This still doesn’t solve my problem of being able to edit the DB table through MODx. Module Maker is exactly what I need to pull it off, but I can’t get it working. Anyone have any other suggestions for this type of module, or know what’s wrong with Module Maker?

Thanks!

There’s the SQLBuddy - Ajax powered database editor module or the dbEdit module.
Either might be worth a look.