MODx User with custom MySQL database - how to I interact ?

Hi

I have a custom database table checkPerson in a database named check.
I like to add a field in this table that ties with the modx user so that when he/she logins in modx,
my sql could query for rows in this table based on his/her login.

1- How do I achieve that ?
2- How do I find out from modx who the user is so I could use this info to query MySQL table ?
3- Are there modx documentation or simple examples to show querying/retrieving of tables by the user using his own login as the identifiable key field in the sql query ?

Thanks

I did this some years ago using extJs to make an AJAX-driven searchable sortable data grid, with the data filtered by the username. I can’t say it’s exactly simple, but I did a writeup of it here. I’ve gotten very fond of JQuery since then, and would do it differently now, but the principle is the same. Whether you are using AJAX or not, use the SESSION value $_SESSION[’webShortname’] or $_SESSION[’webInternalKey’] if you prefer to use the user’s numeric ID in the WHERE clause of your query.

Accessing a database separate from MODx’s is not difficult either http://wiki.modxcms.com/index.php/API:DBAPI:connect

If you need to check this table on login, perhaps to add data to the SESSION, or to validate the user against the remote data before completing the MODx login process, use a plugin using OnBeforeWebLogin, OnWebAuthentication or OnWebLogin events. http://www.sottwell.com/how-plugins-work.html

If you can wait until after the user is logged in, the function getLoginUserName( ) will get the user’s name. The function getLoginUserID() will return the user’s ID. Be sure to use userLoggedIn() first to make sure that there is a logged in user to check on.


You might consider moving the table into the MODx db so you can use the MODx DB methods on it.

You can use the MODx db API on external databases. You just need to make sure to connect and then disconnect properly when you are finished. See the wiki page I linked to.

Hi

<?php
global $modx;
//connect to seperate mysql db
$modx->db->connect(localhost, ’learningMODx’, ’admin’, ’admin’, true);

$output = ’’;
$table = $modx->getFullTableName( ’modx_active_users’ );

$result = $modx->db->select( ’username’, $table);

if( $modx->db->getRecordCount( $result ) >= 1 ) {
while( $row = $modx->db->getRow( $result ) ) {
echo "while loop";
}
}
else {
echo "There are no records to show.";
}
//disconnect
$modx->db->disconnect()

//reconnect to modx db
$modx->db->connect();
?>

I don’t get any printout on the web page.

What am i doing wrong here ?

Why are you connecting to an external database, then using a MODx database table?

If "learningmodx" is your MODx table, you do not need to connect to it. Any snippet, TV, module or plugin code already has access to it. MODx connects automatically.

Hi Susan

Thanks for your reply.
Even with my other database and a table from this database, I still get the same result. No printout on the web page.

Are you trying to get information on the current user, then use that information to get data from your non-MODx database?

Hi Susan
Yes

Depending on what information you need on the current user, you can get it from the SESSION (see this for more information; log in at the bottom of the page for a view of web user’s SESSION) or use these API functions.

After that, use the $modx->db->connect() method to connect to your external database, make your query, close the external connection and reopen the MODx connection, then process your query results.