XF 2.0 Creating an addon to connect to a remote DB

[runelynx]

Hiya -
Creating my first addon so I can display data from a remote MySQL DB. I've pieced together the details so far from various threads. I assume I'm missing an include (or a "use" in XF-land)... but for the life of me I cannot find where that documentation is. Can anyone help?

An exception occurred: [Error] Class 'Runic\Connect\Zend_Db' not found in src/addons/Runic/Connect/Execute.php on line 18

Code:

<?php

namespace Runic\Connect;

class Execute {

    /**
     * [USER=103277]@Param[/USER] \XF\Pub\Controller\AbstractController $controller
     * [USER=103277]@Param[/USER] \XF\Mvc\Reply\AbstractReply           $reply
     */
    public static function testQuery(
        \XF\Pub\Controller\AbstractController $controller,
        \XF\Mvc\Reply\AbstractReply &$reply)
        {

            //$mydb = XenForo_Application::get('db');

            $db = Zend_Db::factory('mysqli',
                array(
                    '---' => $config['db']['host'],
                    '3306' => $config['db']['port'],
                    '---' => $config['db']['username'],
                    '---' => $config['db']['password'],
                    'rpgame' => $config['db']['dbname'],
                    'latin1' => $config['db']['charset']
                )
            );

            $testQuery = $db->fetchAll("
            SELECT PlayerName FROM rp_PlayerInfo");
            $response->params['testQuery'] = $testQuery;
        }
}

 

[Robust]

There's quite a few problems here. For the more critical ones, $config is undefined here. Also, it looks like you're looking at XF1 docs and trying to use them in XF2. Instead take a look at \XF\App lines 578-594 for sample code. $response is also undefined.

I'd recommend taking a look at the XF developer docs first.

 

[runelynx]

Unfortunately the samples in the doc cover areas like setup (not needed here), entities (not needed here), uninstallation (not needed here).

Where would I go to look at the XF/App namespace? I've tried searching the documentation and the folders/files, I do not see it.

Do you have any thoughts on resolving the ZenDB class issue - how do I make use of that? I can't find it anywhere in the docs and it's stopping me from methodically stepping through any other errors I might have.

 

[runelynx]

Ok I found the zend framework in the vendors folder of addons... and noted that zend-db was missing. Found it here https://github.com/zendframework/zend-db/ ... so I've added it to my structure. Stay tuned...

 

[runelynx]

Change of plans... that went nowhere quickly and it looks like the XF folks provide Composer to help with this. I found this how-to article and am following it.

Using Composer Packages in XenForo 2.0 Addons Tutorial

Composer is a tool for dependency management in PHP. It allows you to declare the libraries your project depends on and it will manage (install/update) them for you. XenForo 2.0 uses Composer behind the scenes to include certain packages it uses...

xenforo.com

 

[Jeremy P]

XF2+ doesn't use the Zend framework for making database connections. You can reference src/XF/App.php lines 579-594 (as pointed out above) to see how the core sets up its own database adapter in the container. The gist of it should be something like:

$config = \XF::config();
$adapterClass = $config['db']['adapterClass'];

$dbConfig = [
    'host'     => 'localhost',
    'port'     => '3306',
    'username' => '',
    'password' => '',
    'dbname'   => '',
    'socket'   => null
];
$fullUnicode = true;

/** @var \XF\Db\AbstractAdapter $db */
$db = new $adapterClass($dbConfig, $fullUnicode);
if (\XF::$debugMode) {
    $db->logQueries(true);
}

 

[runelynx]

Wow... you have fast-forwarded my troubleshooting !! I now need to work out some MySQL connecting problems on the remote side, but it's actually connecting now. Thank you so much!!

 

[runelynx]

Hrrrm...
If I need advanced configs in the adapter, do I run standard mysqli php commands to set those options? I checked the XF Adapter file and it has no room for advanced options / security settings. Going to try mysqli commands.

 

[Jeremy P]

Yeah, it looks as though the adapter doesn't support secure remote connections. You would have to subclass it and implement support for those flags yourself.

 

[runelynx]

Ok I've found my way going back to the basics with mysqli. I'm down to an error on my last line of code so I'm almost done!! It won't let me output my data into the $reply variable because its protected... how do I unprotect it

error:
An exception occurred: [Error] Cannot access protected property XF\Mvc\Reply\View::$params in src/addons/Runic/Connect/Execute.php on line 32

<?php

namespace Runic\Connect;

class Execute {

    /**
     * [USER=103277]@Param[/USER] \XF\Pub\Controller\AbstractController $controller
     * [USER=103277]@Param[/USER] \XF\Mvc\Reply\AbstractReply           $reply
     */
    public static function testQuery(
        \XF\Pub\Controller\AbstractController $controller,
        \XF\Mvc\Reply\AbstractReply &$reply)
        {
            
            
            error_reporting(E_ALL);
            ini_set("display_errors", "1");
                    
            $mysqliGS=mysqli_init();
            if (!$mysqliGS)
              {
                die("mysqli_init failed");
              }

            mysqli_ssl_set ( $mysqliGS , "/home/runicp5/public_html/sslkeys/client-key.pem" , "/home/runicp5/public_html/sslkeys/client-cert.pem" , "/home/runicp5/public_html/sslkeys/ca.pem" , "/home/runicp5/public_html/sslkeys/" , NULL );
              
            $mysqliGS->real_connect('play.XXX.com', 'XXX', 'XXX', 'rpgame', 3306, NULL, MYSQLI_CLIENT_SSL_DONT_VERIFY_SERVER_CERT);

            $result = $mysqliGS->query("SELECT PlayerName FROM rp_PlayerInfo");

            $reply->params['testQuery'] = $result;
        }
}

 

[runelynx]

Happpppppppppy day!! Did some searching and found it on the forums. I can now retrieve data! I haaave the pooooowweeeerrr!!

Thanks all for your help!!

            $result = $mysqliGS->query("SELECT PlayerName FROM rp_PlayerInfo");

            $reply->setParams(['testQuery' => $result]);           

        }
}