Installing a database table editor as a MODx module

[Guest]

Welcome, Guest. Please login or register.
Did you miss your activation email?

1 Hour 1 Day 1 Week 1 Month Forever
Login with username, password and session length

[ganeshXL]

There are situations when using MODx documents and custom TVs to store your data is not the preferred or ideal way:

• Too much processing power is used for aggregating (Ditto) and the site slows down.
• You have to import existing data from an old site or external application.
• You will most likely end up with several hundred or thousands of datasets.
• You need to do something with that data which is not possible using only the default MODx tools or the snippets/plugins available in the repository.

In such cases, it's wise to just store your data in your own mySQL table(s).

Problem: MODx doesn't have an in-built DB table-editor, but your client expects to handle everything inside MODx, i.e. using just one login, one admin-URL, one app.
Solution: Install a database table editor script as a module.


Here's how:

a) Download phpMyEdit: http://www.phpmyedit.org/
Make sure you also download the documentation package.


b) Upload it to your folder (e.g. in the manager folder: manager/phpMyEdit/)
Hint: If you're also using mySQL SETS, it's wise to fix a small typo in the phpMyEdit.class.php file:
see this forum thread for infos: http://platon.sk/forum/projects/viewtopic.php?t=7892&highlight=set+bug


c) Go to http://www.domain.tld/manager/phpMyEdit/phpMyEditSetup.php to create a new editor-page for your DB-table.
Enter your DB connection data (hostname, username, password, DB-name, table-name) and hit "submit".
On the next page, select the identifier field. Usually this is "id" and will be preselected already.
On the third page, you can add a few options (title, default CSS etc.)
The last page presents you with the generated PHP code. The app will try to create the file for you. In case that isn't possible for some reason, just create your own file and paste the code you see on screen.


d) Now you can see the page in action at: http://www.domain.tld/manager/phpMyEdit/filename.php
Change the CSS to your liking and/or add the manager CSS, e.g.

Code:

<link rel="stylesheet" type="text/css" href="media/style/MODxLight/style.css" />

e) Create a new module for your DB-table. Go to Modules > Manage Modules and click "New Module".
All you really need to do is add one line in the module code:

Code:

include_once $modx->config['base_path'] . "manager/phpMyEdit/filename.php";

Save it, and then reload the upper tabs-frame to see the newly created modules tab.

Hover over the new tab to see the URL: We want to see which module id we got.
e.g. http://localhost/modx-0961p2/manager/index.php?a=112&id=4
We need this for step f.


f) Add the following lines to your editor-page:

Code:

$opts['page_name'] = 'index.php';
$opts['cgi']['persist'] = array(
'id' => 4,
'a' => 112
);

Make sure the id value is the same as your module id you just created.
The var a should stay 112 = manager action "run module".
This will make sure the form-actions will always go to the correct URL.


g) Secure the page: Add this at the top of the editor-file:

Code:

<?php
if (realpath(__FILE__) == realpath($_SERVER['SCRIPT_FILENAME'])) {
exit('This file can not be accessed directly.');
}
if(IN_MANAGER_MODE!="true") die("Please use the MODx Manager.");
?>

This will make sure you can only access it via the manager module, not directly (remember: just "hiding" a doc has nothing to do with security).


h) Everything should work nicely now. There's a lot of configuration options to choose from. Change permissions (view, delete, create, add), default number of records per page and much much more.

Code:

e.g. $opts['inc'] = 25; // show 25 records per page (default = 15)

The documentation is really good and there's plenty of examples.



I know phpMyEdit is not the only script you could use. I've been using TableEditor for some projects in the past: http://www.phpguru.org/static/TableEditor.html
Setup requires a bit more work, but see for yourself what tool suits you best. Here's a feature comparison: http://www.phpguru.org/static/TableEditorComparison.html
None of these scripts can replace phpMyAdmin imho, but they're also not really meant to.


I hope this little "how-to" helps someone. Adding a new module as described here takes only 5 minutes. Of course, designing the DB-table and writing queries to output data for your specific application is outside the scope of this little write-up.

[dev_cw]

This looks great. I will definitely give this tutorial a run and this is a good example of the possibilities. Thanks 

[bunk58]

Thanks for the insight.
I've been using an external database table editor because I couldn't see how to get it to work from inside the manager.
Hopefully I can now integrate into the MODx Manager.

[smashingred]

Would you be interested in placing this in the Wiki? I think it would be a great addition. Thanks for the great tutorial.

Cheers,

Jay

[ganeshXL]

Yes, I thought about the Wiki first. But I'm not familar with the wiki-specific formatting tags, so I just posted it here instead.

Is there an overview of wiki formatting options? I honestly can't stand all that pseudo-tags stuff. What's wrong with plain vanilla HTML?

[smashingred]

Unfortunately there is formatting MediaWiki's proprietary formatting is fairly easy. Here is the guide: http://wiki.modxcms.com/index.php/Help:Editing

Update: Just found this http://en.wikipedia.org/wiki/Wikipedia:Cheatsheet

If you want to just post it someone else can format it for you. (Me or someone else.)

Cheers,

Jay

[dev_cw]

What's wrong with plain vanilla HTML?

I have the same question. Why do wikis need to impose their own obscure formating methods. There should always be the option to use HTML.

[smashingred]

I can't explain why but only that for convention many wikis are following the MediaWiki formatting. It is obscure and I am not sure the rationale but it is what it is. Maybe someone should add version control to MODx and build a wiki on it.

[ganeshXL]

If you want to just post it someone else can format it for you. (Me or someone else.)

That would be great. Maybe add a link to this thread for discussions and suggestions.

[ganeshXL]

Just slighly modified step f). I found out if you try to sort by field-name (clicking on the table-headers) without these "cgi persist" rules, you'll just get to the main manager/index.php file and modx will produce an error.
It basically adds those two vars to every request.

[Chuck]

Very nice writeup ganeshXL!  You made that simpler than I thought it would be.  I've been using phpMyEdit with MODx on the front end of my client's projects.  My approach requires a separate login facility, additional logins, creating a management page, using MODx secure pages, and I completely missed securing the external phpMyEdit pages with the extra code at the top.  I'm going to press this into action this morning on my own site to get familiar with the process.  Thank you!

[EDIT]

Instead of using an include I was able to pull the PHP code from the phpMyEditSetup.php output into the module itself.  First I moved the embedded CSS into a chunk:

Code:

<style type="text/css">
hr.pme-hr      { border: 0px solid; padding: 0px; margin: 0px; border-top-width: 1px; height: 1px; }
table.pme-main      { border: #004d9c 1px solid; border-collapse: collapse; border-spacing: 0px; width: 100%; }
table.pme-navigation { border: #004d9c 0px solid; border-collapse: collapse; border-spacing: 0px; width: 100%; }
td.pme-navigation-0, td.pme-navigation-1 { white-space: nowrap; }
th.pme-header      { border: #004d9c 1px solid; padding: 4px; background: #add8e6; }
td.pme-key-0, td.pme-value-0, td.pme-help-0, td.pme-navigation-0, td.pme-cell-0,
td.pme-key-1, td.pme-value-1, td.pme-help-0, td.pme-navigation-1, td.pme-cell-1,
td.pme-sortinfo, td.pme-filter { border: #004d9c 1px solid; padding: 3px; }
td.pme-buttons { text-align: left;   }
td.pme-message { text-align: center; }
td.pme-stats   { text-align: right;  }
</style>

Then I added an API call to the top of the module code to call the chunk:

Code:

$chunkOutput = $modx->getChunk('chunk_name');
echo $chunkOutput;

I also had to add reference to the module page which matches the page per the above description.  Without this the add, change, delete functions don't work:

Code:

$opts['page_name'] = 'index.php?a=112&id=4';

Then near the bottom I altered the path to the class file:

Code:

// Now important call to phpMyEdit
require_once 'location/of/phpMyEdit.class.php';

Just another solution to the same challenge!

[/EDIT}

[yoomai]

Thread bookmarked. Thank you, ganeshXL!

[ChuckTrukk]

man you rock. i was just thinking ohw this could be done. and here it is.

thanks

[malissa]

Hey this is great!

I have been trying to work out how to search for a field and then get the row data from a table in the database and then edit or delete it for months now. Have to say that while i can succesfully add and edit searching and displaying the list with pagination that works just isnt working.
I have put this in the manager but would still like to be able to have it working from the from end for a particular group of users.
Would this be possible?

Thanks for any help.

[sottwell]

http://www.sottwell.com/article-extjs1.html

It's easy enough to make the grid editable; see the extjs documentation for the parameters to do that. Of course, that means more code in the back-end processor, but again it's fairly easy, just validate and sanitize the data and insert/replace it into the database.