Dynamic Drop Down List for looking at a non-MODx table

Hi all,

I’m still a noob at all of this stuff — at some point I may get better .

I’m working on a site that looks at data from several different locations (most of which are in the main MODx database as non-MODx tables). I’ve got some data that I’m trying to use to populate a selectable drop down list to pull up coinciding data from another table (ie - a user can select from the list an item and then the data from the other table is populated below via a MySQL query in a nice & neat table - kinda like viewing a list of cities if you select a certain state/country, but not javascript.) Also, I’m basing this on having a registered user logged in who can only see the items from the list that they are allowed to see.

I’ve looked around and it seems like I should use a TV, but when I place the TV in my template it doesn’t create a drop down list, it only creates a list of the items. I’m hoping that I don’t have to do anything too crazy in PHP, but it may be necessary.

Also, is it possible to take the selection from the drop down and push it to the MySQL query in my snippet that pulls the rows from the table?

My demo site is http://www.burningtreedesigns.com/mcni/ and the page I’m working on is http://www.burningtreedesigns.com/mcni/index.php?id=112.

Any help is greatly appreciated.

Thanks,
Ashton

---

Additional info about my project
Here’s the TV that I’ve created:
Variable Name: [*qlinkAccts*]
Caption: The list of Qlink Accts
Description: A way to pull up the Qlink Acct users
Input Type: DropDown List Menu
Input Option Values: <empty>
Default Value: @SELECT DISTINCT qlink_acct FROM tmtrxfil
Widget: Delimited List
Widget Properties: Delimiter -


Other info:
Table 1: dmtotm
rows - id, user_id (same as MODx user_id from registration), qlink_acct

Table 2: tmtrxfil
rows - id, qlink_acct, sync_time, no_tickets, total_sales

Table 1 will need to look at Table 2 in order to select the appropriate qlink_acct.


Snippet - QlinkAcctViewer

Try using a comma as the delimiter instead of
. Also, I think you need global $modx at the start of that snippet.

Ok. I’ve switched to the comma instead of the line-break for the delimiter in the TV, but now it’s just a one-lined list and still not a drop down. I ended up switching it back.  I’ve added the global $modx call to the beginning of the snippet and, for now, is not being used.

Any other ideas?

You’ve probably already tried calling the snippet cached [snippet] and uncached [!snippet!].

The only other thing I can think of is scrapping the TV and doing it the old fashioned way -- appending the html for each item in the drop-down list with $output .= in your foreach() statement.

Instead of this: @SELECT DISTINCT qlink_acct FROM tmtrxfil, try using a snippet that runs the sql query and returns a string in the right format which is:
"Option1==Value1||Option2==Value2||Option3==Value3"

You’ll need to do this anyway as you need a join to grab the user name from the modx user table.

And use like this as the input option value: @EVAL return $modx->runSnippet(’MySnippet’);

Quote from: therebechips at Dec 04, 2008, 04:25 AM

Instead of this: @SELECT DISTINCT qlink_acct FROM tmtrxfil, try using a snippet that runs the sql query and returns a string in the right format which is:
"Option1==Value1||Option2==Value2||Option3==Value3"

You’ll need to do this anyway as you need a join to grab the user name from the modx user table.

And use like this as the input option value: @EVAL return $modx->runSnippet(’MySnippet’);

I’m not sure what you mean about putting it into a snippet. Like I stated previously, I’m a noob at both PHP and MODx, but I’m willing to learn. Do you have any insight as to how the code might look?

Create a snippet called ’MySnippet’ (or whatever) with this code. I’m assuming the qlink_acct is a foreign key for the modx web user id :


Then go back to your TV and add this as your input option value, and set the delimiter to comma , :


Naturally I haven’t tested this so it may not work.

EDIT: <slaps head> I’ve just realised you want this on the front end not in the Manager - right?

Quote from: therebechips at Dec 04, 2008, 12:37 PM

Create a snippet called ’MySnippet’ (or whatever) with this code. I’m assuming the qlink_acct is a foreign key for the modx web user id :

Naturally I haven’t tested this so it may not work.

EDIT: <slaps head> I’ve just realised you want this on the front end not in the Manager - right?

It is on the front end. I’ll give it a shot right now! Thanks for the help! I’ll let everyone know how it turns out.

Quote from: therebechips at Dec 04, 2008, 12:37 PM

Create a snippet called ’MySnippet’ (or whatever) with this code. I’m assuming the qlink_acct is a foreign key for the modx web user id :

<?php
global $modx;
$output ='';

$sql = 'SELECT DISTINCT a.qlink_acct as user_id, b.username as user_name
           FROM tmtrxfil as a 
           LEFT JOIN modx_web_users AS b 
           ON a.id = b.id';

$result = $modx->db->query($sql);
if ($result && mysql_num_rows($result)>0) {
	for ($i=0; $row=mysql_fetch_array($result); $i++){	
	    if ($i>0) $output .= '||';
	    $output .=stripslashes($row['user_id']) . '=='. stripslashes($row['user_name']);
	}
}

return $output;
?>

Then go back to your TV and add this as your input option value, and set the delimiter to comma , :

@EVAL return $modx->runSnippet('MySnippet');

Ok. So I’ve tried it several different ways, but can’t seem to get any output from the snippet (called QlinkAcctSelect). I’ve run the SQL in phpMyAdmin and it works fine, so it’s not the call. I’ve even run the snippet by itself in a document just to see if it loads any data and still nothing. I’m going to keep trying and see if I can pinpoint what may be causing the hangup.

This would output the select menu on the front end instead of within a Manager page. Obviously you want to do something more with it, like use it as a filter for what’s displayed on the same page.