Can I access an external database to populate a template variable drop-down?

☆ A M B ☆
326 Posts

aladage Reply #1, 15 years, 6 months ago

I have an external database table (non-MODx) with two columns: name and value. In my Evolution back-end, I want to connect to the DB, retrieve the name/value pairs and use them as the names/values for a drop-down template variable.

My initial thought was to use @bindings, like @SELECT. However, I don’t know how you’d use @SELECT to hit an external DB; it seems meant for the current MODx DB:

http://rtfm.modx.com/display/MODx096/%28at%29SELECT

@EVAL to a PHP script with the new connection looks like a possibility, but I’m unsure of what values my script should be returning to work with the TV.

How would I go about this? Thanks!

654 Posts

mconsidine Reply #2, 15 years, 6 months ago

In Evo 1.0.5, if you set up a TV as a drop-down and put this for the default input value

@EVAL return include_once('actual/path/to/mytestcode.php');

where ’mytestcode.php’ is this

<?php
$output ='test1==testa||test2==testb';
return $output;
?>

then you’ll get a drop down. So if you make $output a function of info from your database, I’d guess you’d get what you want. Hope this helps,
Matt

☆ A M B ☆
326 Posts

aladage Reply #3, 15 years, 6 months ago

Thanks! I figured it out right as I saw this message, and luckily, our methods match up

What was confusing me was understanding how the @EVAL binding was expecting the returned data. But it makes perfect sense! It wants exactly the same format as if you just manually entered the content into Default Values:

Name1==value1||Name2==value2||etc...

As long as my snippet returns that format, it works beautifully. Thanks!

☆ A M B ☆
326 Posts

aladage Reply #4, 15 years, 6 months ago

Hmm...okay, not working as beautifully as I thought. Although my TV values are showing up fine, at the bottom of the page I’m editing (in the manager), I get the following errors:

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /installpath/manager/actions/mutate_content.dynamic.php on line 1056

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /installpath/manager/actions/mutate_content.dynamic.php on line 1076

I’ve traced the problem to the snippet I’m calling in my TV’s @EVAL statement, which looks sort of like this (I’m okay but not great at php; please let me know if there’s a better way):

<?php
$output = '';

$host = 'host';
$database = 'database';
$uid = 'username';
$pwd = 'password';


//connect to seperate mysql db
$db = new DBAPI($host,$database, $uid,$pwd);
$sql = $db->select('DISTINCT name,value','tablename','','name ASC');
$resultArray = $modx->db->makeArray($sql);

if (!empty($resultArray)) {
    $formattedArray = array();
    $formattedArray[] = '';
    foreach ($resultArray as $item) {
        $formattedArray[] = $item['name'].'=='.$item['value'];
    }
    $output = implode('||', $formattedArray);
}
return $output;
?>

Does anyone see a problem here? I’m stumped.

☆ A M B ☆
326 Posts

aladage Reply #5, 15 years, 6 months ago

One more detail: my @EVAL statement is slightly different than what was recommended above by mconsidine, but I think does the same thing:

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

654 Posts

mconsidine Reply #6, 15 years, 6 months ago

FWIW, I’m getting some mysql-related errors when trying this in Evo 1.0.5 (using straight mysql_connect, mysql_query, etc. PHP commands). If I figure out a solution, I’ll post it.
Matt

654 Posts

mconsidine Reply #7, 15 years, 6 months ago

I got this code to work in Evo 1.0.5:

<?php
$output = '';

$host = 'localhost';
$database = 'testdb';
$uid = 'testuser';
$pwd = 'testuserpwd';

//connect to seperate mysql db
//$db = new DBAPI($host,$database, $uid,$pwd);
$link = mysql_connect($host,$uid,$pwd) OR DIE ('Unable to connect to database! Please try again later.'); //MattC
$dbselected = mysql_select_db($database, $link) or die( "Unable to select database");  //MattC
//$sql = $db->select('DISTINCT name,value','tablename','','name ASC');
$query = "SELECT * from `testtable`";
$resultArray = mysql_query($query, $link);

//mysql_close($link);  //uncommenting this caused errors

//$resultArray = $modx->db->makeArray($sql);

if (!empty($resultArray)) {
    $formattedArray = array();
    $formattedArray[] = '';
    while ($row = mysql_fetch_assoc($resultArray)) {
        $formattedArray[] = $row['field1'].'=='.$row['field2'];
    }
    $output = implode('||', $formattedArray);
}
return $output;
?>

For reasons I don’t understand if I uncomment the mysql_close line, I get some errors thrown in addition to seeing the dropdown. And as written there seem to be a few extra empty "slots" generated in the dropdown. Anyway, hope this helps a bit.
Matt

249 Posts

cyclissmo Reply #8, 15 years, 6 months ago

Unless you’re using a persistent connection (which in this case you are not), you can safely leave [tt]mysql_close($link)[/tt] commented out. The connection will automatically close when the script terminates.

For argument’s sake, what error messages are you receiving?

lo9on.com

MODx Evolution/Revolution | Remote Desktop Training | Development

☆ A M B ☆
326 Posts

aladage Reply #9, 15 years, 6 months ago

Sigh...no dice. I copied your code exactly, but I continue to get the same two errors:

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /installpath/manager/actions/mutate_content.dynamic.php on line 1056

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /installpath/manager/actions/mutate_content.dynamic.php on line 1076

Just for kicks, I tried uncommenting mysql_close($link). That just made things worse, and produced these errors:

Warning: mysql_query() [function.mysql-query]: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) in /installpath/manager/actions/mutate_content.dynamic.php on line 1055

Warning: mysql_query() [function.mysql-query]: A link to the server could not be established in /installpath/manager/actions/mutate_content.dynamic.php on line 1055

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /installpath/manager/actions/mutate_content.dynamic.php on line 1056

Warning: mysql_query() [function.mysql-query]: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) in /installpath/manager/actions/mutate_content.dynamic.php on line 1075

Warning: mysql_query() [function.mysql-query]: A link to the server could not be established in /installpath/manager/actions/mutate_content.dynamic.php on line 1075

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /installpath/manager/actions/mutate_content.dynamic.php on line 1076

☆ A M B ☆
326 Posts

aladage Reply #10, 15 years, 6 months ago

I’m not sure if I ever listed my setup specs:

Evo 1.0.5
MySQL 4.1.13-standard