Database Project

Maybe this won’t work, but consider the possibility that you may not need a MODx page for every row. You might be able to have a single MODx page that handles multiple rows from a given database table, for example.

One example is how Expression Engine handles stuff like this.... the URL structure is usually /class/function/id -- e.g. table_name/read/123. I was working on some CRUD stuff where I brought this up in another thread:

http://modxcms.com/forums/index.php/topic,31477.msg190866.html

The idea being that I could have one MODx document act as a launching point to read from multiple other tables by interpreting the URL segments. e.g. http://mydomain.com/some/modx/document/custom_id could use the "custom_id" bit to feed a Snippet which would look up the appropriate row in your custom database table.

It’s rare that you’d need 5,000 pages that were unique in format. More likely would be that you had fewer than 10 formats, and hundreds (possibly thousands) of pages that that fell into that format. That’s where you could get some mileage by having the URL supply the id of the related row. Maybe you’ve already seen stuff like /my/page?id=345. You can have the same functionality with "straight" urls.

Hope this helps.

Quote from: Everettg_99 at Jan 01, 2009, 03:12 AM

Maybe this won’t work, but consider the possibility that you may not need a MODx page for every row. You might be able to have a single MODx page that handles multiple rows from a given database table, for example.

That is certainly doable in MODx, and it does effectively avoid the cache issue that can cause problems with thousands of documents. The negative of doing something this way is that you lose all of the MODx tools for using this content, so you can’t use Ditto or AjaxSearch or any other resource and have this content included in your results. But the positives may outweigh the negatives, and you can use the MODx dbAPI to access the data and display it with custom snippets.

Adding a custom rewrite rule to your .htaccess file would allow you to use URLs like Everettg_99 mentions and make the data available as $_GET variables, and it’s easy to write a MODx plugin that could create these URLs for you within MODx (if necessary).

Everettg’s suggestion is exactly what I had in mind! I just don’t know how to make the master pages to control the external DB. The only form<>DB interaction I got working was a site with eform2db, but as noted earlier that seamed a bit buggy/unreliable. Plus it also seamed to lack any content modifications to prevent the input from causing problems with the DB.

I can create the DB Query’s to get the data I need, but I don’t know how to create the snippet(s) to interact with the DB and create the addresses as noted.

AMDbuilder

You would do this exactly the same way that you would outside of MODx, except that you could use the MODx database API as a wrapper for your database commands. You’ll need to be familiar with MySQL use from PHP and some PHP coding in general in order to write your custom snippets. You can probably learn how to do this if you don’t know already, and you could post your code here for help if you’re having trouble. If you feel that this may be a bit over your head or require more time and energy than you can devote to it, then you should consider either hiring someone to help you out or not doing your site this way.

This wouldn’t be unstable or unreliable in any way, but it would be custom and the content in these tables wouldn’t be handled by MODx.

Quote from: ZAP at Jan 01, 2009, 11:13 AM

That is certainly doable in MODx, and it does effectively avoid the cache issue that can cause problems with thousands of documents. The negative of doing something this way is that you lose all of the MODx tools for using this content, so you can’t use Ditto or AjaxSearch or any other resource and have this content included in your results. But the positives may outweigh the negatives, and you can use the MODx dbAPI to access the data and display it with custom snippets.

Just need to correct you there ZAP. Certainly in the case of AjaxSearch you can use your own tables. I’ve done this successfully on a site I’m working on with a table with over 12000 records (products in my case). See http://www.modx.wangba.fr/index.php?id=261 and http://wiki.modxcms.com/index.php/AjaxSearch. As for Ditto I’m sure you could do something with a custom extender. See http://ditto.modxcms.com/ for documentation.

Quote from: TobyL at Jan 01, 2009, 07:25 PM

Just need to correct you there ZAP.

Thanks for doing so. I didn’t realize both these resources were so easily extended to custom tables. Just goes to show how flexible MODx can be...

If you need to write some PHP to interact with the database (e.g. in a Snippet), have a look at the wiki:
http://wiki.modxcms.com/index.php/Access_another_database_from_MODx

I wrote an article for doing stealth submits with PHP... you don’t need that kind of functionality, but if you want to get an idea for how the PHP code might look for interacting with a database using good security practices (e.g. bind variables, regular expressions for filtering input data, etc), have a look at this article:
http://www.tipsfor.us/2008/11/21/stealth-submit-using-ajax-to-save-form-data-without-submitting-the-form/

Thanks for the links! You would happen to have a link to part two of the wiki article (how to run commands such as INSERT, SELECT, UPDATE, DELETE)? I can find the connection code without much trouble, but when it comes to the code to interact with the DB it seams to be missing...

AMDbuilder

The MODx database API reference is here.

I gotta stop looking at pages on iphone... (that or get some sleep...)

$table = $modx->getFullTableName("table_name");
$sql = "TRUNCATE TABLE $table";
$modx->db->query($sql);

Update line two with SQL query! DUA!

Going to sleep before I ask another stupid question...
AMDbuilder