Fork me on GitHub

Watch

GitHub Core

Show your support for Zikula! Sign up at Github account and watch the Core project!




GitHub Modules

Forum Activity

Forum feed

» Visit forum | » View latest posts

Connect to external database  Bottom

  • Zikula version: 1.3.2
    In config.php is provided to configure second (external) database.
    Please advice (sample code or idea) how to make queries against this database.
    Tank you in advance.
  • funny, I was looking for the same answer :)
    I would like to share the same tables of itsNews on another site...
  • It seems that you need to invoke the event: doctrine.init_connection
    and you will get the connection as the data, like the core does:

    Code

    $args = array('name' => 'yourConfigName', 'lazy' => true/false);
    $dbEvent = new Zikula_Event('doctrine.init_connection', null, $args);
    $myConnection = $this->eventManager->notify($dbEvent)->getData();

    My guess is that the connection is available from there for the Doctrine_Records and DBUtil.

    When you finish it, you may need to close the connection or restore the current one to the default:

    Code

    $doctrineManager = Doctrine_Manager::getInstance();
    $doctrineManager->closeConnection($doctrineManager->getConnection('yourConfigName'));
    // if you have more than two opened, it's good to be sure to restore the default
    $doctrineManager->setCurrentConnection('default');

    This is what I conclude by reading the code, I haven't used this feature.
    Greetz

    --
    - Mateo T. -
    Mis principios... son mis fines
  • Thank you Mateo, using your suggestions and exploring the code, managed to write some code that works.

    Code

    // Init new connection to external database (do once)
    $ConfigConnectionName = 'test'; # connection name as specified in config.php
    $dbEvent = new Zikula_Event('doctrine.init_connection', null, array('lazy' => false, 'name' => $ConfigConnectionName));
    $NewConnection = EventUtil::getManager()->notify($dbEvent)->getData();

    // Make queries to external database
    // Set current connection to external db
    $doctrineManager = Doctrine_Manager::getInstance();
    $doctrineManager->setCurrentConnection($ConfigConnectionName);
    // now can query external database
    $query = "SHOW TABLES";
    $result = DBUtil::executeSQL($query);
    if ($result) {
        $resitems = $result->fetchAll();
        print_r($resitems);
    }
    // restore default connection (important)
    $doctrineManager->setCurrentConnection('default');

    Maybe there is better solution, but this works for now.
  • Nice
    FYI I guess that the Manager sets the latest opened connection as the current one, so, you don't need to make it explicit, but restore the default or close your connection if you won't use it again ;)

    --
    - Mateo T. -
    Mis principios... son mis fines
  • It is true that latest opened connection become current one. I put setCurrentConnection in the example, just in case, if this piece of code use as function to call many times, and between is possible to call zikula queries against default database.

    BTW first piece of code (opening connection) I expected to already done by Zikula, when opening default connection (in init Zikula code).
  • You mean you want Zikula open your 'test' connection everytime?

    You can do it with EventHandlers too, check the docs inside the package, in the /docs folder icon_wink

    --
    - Mateo T. -
    Mis principios... son mis fines
  • nikp, where would you put this code above? I am new to the Zikula platform, and just trying to display certain tables in an external DB based on user credentials. Do I need to create an entirely new module to do this or is there something out there that will aid in developing this kind of procedure?
  • If you like to make something usable to website visitors, you have to make new module, or additions to existing one.
    But if you like to test only, you can create a PHP file, for example test_zikula.php, put the above code in it, and upload in main Zikula directory, and then point with browser www.zikulasite.xyz/test_zikula.php

    Content of the file can be something like this:

    Code

    // Zikula init
    include 'lib/bootstrap.php';
    $core->init();

    // Init new connection to external database (do once)
    $ConfigConnectionName = 'test'; # connection name as specified in config.php
    $dbEvent = new Zikula_Event('doctrine.init_connection', null, array('lazy' => false, 'name' => $ConfigConnectionName));
    $NewConnection = EventUtil::getManager()->notify($dbEvent)->getData();

    // Make queries to external database
    // Set current connection to external db
    $doctrineManager = Doctrine_Manager::getInstance();
    $doctrineManager->setCurrentConnection($ConfigConnectionName);
    // now can query external database
    $query = "SHOW TABLES";
    $result = DBUtil::executeSQL($query);
    if ($result) {
        $resitems = $result->fetchAll();
        print_r($resitems);
    }
    // restore default connection (important)
    $doctrineManager->setCurrentConnection('default');

    // Zikula footer and shutdown (uncomment if you like to see header and footer)
    //Zikula_View_Theme::getInstance()->themefooter();
    system::shutdown();

    In this example parameters for connection 'test' you have to enter in your config/config.php file, as second (not 'default').



    Edited by nikp on Mar 13, 2012 - 11:06 AM.
  • Hi David! Welcome to the forums :)

    You can also code a Block, cloning an existing one and putting in inside anothe module in the meanwhile.
    Blocks are simpler, but currently I don't remember what's the standard to put those pieces of code on /config/Blocks

    /config is the difectory where all your custom stuff should be
    so if you update the system, or a module, you have your code and your overrides there with no problem

    Greetings icon_wink

    --
    - Mateo T. -
    Mis principios... son mis fines
  • Solution with block is nice, particularry if the task is quite simple and don't need complicated admin and user interface.
    Even Zikula provides very useful native block: "Simple file include". Place above code in a PHP file (without Zikula init/shutdown), create such a block, enter path to the file, and all is done icon_smile



    Edited by nikp on Mar 13, 2012 - 10:32 PM.
  • Hey that are great ideas for some testing and including external parts indeed. icon_biggrin And all within Zikula itself.

    --
    campertoday.nl, Module development, Dutch Zikula Community

This list is based on users active over the last 60 minutes.