Use multiple database connections in Zencart


There are times when you may need to grab data from a database outside the Zencart database, it may be the case when two zencart installation need to share data or may be some other reasons. To achieve this, you need to open 2 database connections that wont overlap each others. People generally open the connection with raw mysql_connect and work with that. There are situations when people open numbers of connections whenever needed which a total mess. To solve all these issues here is my solution that is best suited for Zencart.

Zencart has its own query class “queryFactory“. The database initialization takes place in the following file each time a page loads CATALOG/includes/init_includes/init_database.php
After initialization is done, $db is the connection object that is used throughout the system.
So you need another connection object like $db, lets call it $new_db, that will use a different database to grab data. For creating the new connection follow the following steps:

  1. Open a new file and save it as CATALOG/includes/extra_configures/new_db_config.php. The file name can be anything. This config file will contain detail of the new connection. Add the following definitions in the file:
    <?php
    
    define('NEW_DB_SERVER', 'new_db_server'); //it may be anywhere, generally localhost
    define('NEW_DB_USERNAME', 'new_db_username');
    define('NEW_DB_PASSWORD', 'new_db_password');
    define('NEW_DB_DATABASE', 'new_database_name');
    
    ?>
    

    Save the file.

  2. Open the database init file CATALOG/includes/init_includes/init_database.php.
    Add the following lines at the bottom of the file, just before the ?> .

    if(defined('NEW_DB_SERVER')){
    
      $new_db = new queryFactory();
      if(!$new_db->connect(NEW_DB_SERVER, NEW_DB_USERNAME, NEW_DB_PASSWORD, NEW_DB_DATABASE, USE_PCONNECT, false)) {
        die('Unable to connect new database. Check config in extra_configures/new_db_config.php');
      }
    
    }
    

    Dont save it yet. Save the file as CATALOG/includes/init_includes/overrides/init_database.php. This way, the original files wont be overwritten.

So your new connection is ready for the action. Right now you have 2 different connections connecting to 2 different databases. $db is the default connection used by zencart through out the system. $new_db is your connection you can use whenever you need.
If at any point you need to grab some data from the other database, just use the new connection variable $new_db instead of $db.
For example, $new_db->Execute("select ANYTHING from ANYTABLE_IN_NEW_DB");

Just remember, $new_db cannot perform any operation on the default database. You can use the new connection object just like the original connection object and use all the functions the original connection uses.

Let me know if you have any questions.

, ,

  1. #1 by naveen on December 23, 2009 - 1:22 pm

    its is working for database related functions from the fronten , but its not working for admin .i have done the same procedure for admin as well but zencart fails to create categories , products after doing the above procedure.

    Do u have any suggestions for admin side?

  2. #2 by imran on December 24, 2009 - 6:19 am

    Hi Naveen,

    It should work for the admin as well. Are you saying regular products, categories cannot be created after adding the new database connection?

  3. #3 by Ali Starkley on February 16, 2011 - 3:09 am

    I have a web-site that I’d like to deploy multiple individual zen-carts on, each with their own individual merchant accounts but I’d like the username and password created by a customer on any of the individual carts to work on any of the other cart in this network. Can you tell me how I can do this? Thanx.

  4. #4 by saqib on November 30, 2011 - 3:27 pm

    i need help zen cart db connection on server its work fine on localhost this is my include/configure.php

    <?php
    /**
    * @package Configuration Settings circa 1.3.9
    * @copyright Copyright 2003-2010 Zen Cart Development Team
    * @copyright Portions Copyright 2003 osCommerce
    * @license http://www.zen-cart.com/license/2_0.txt GNU Public License V2.0
    * File Built by zc_install on 2011-11-25 08:56:01
    */

    /*************** NOTE: This file is similar, but DIFFERENT from the \"admin\" version of configure.php. ***********/
    /*************** The 2 files should be kept separate and not used to overwrite each other. ***********/

    // Define the webserver and path parameters
    // HTTP_SERVER is your Main webserver: eg-http://www.your_domain.com
    // HTTPS_SERVER is your Secure webserver: eg-https://www.your_domain.com
    define(\’HTTP_SERVER\’, \’http://staysports.com/\’);
    define(\’HTTPS_SERVER\’, \’http://staysports.com/\’);

    // Use secure webserver for checkout procedure?
    define(\’ENABLE_SSL\’, \’false\’);

    // NOTE: be sure to leave the trailing \’/\’ at the end of these lines if you make changes!
    // * DIR_WS_* = Webserver directories (virtual/URL)
    // these paths are relative to top of your webspace … (ie: under the public_html or httpdocs folder)
    define(\’DIR_WS_CATALOG\’, \’http://staysports.com/zencart-1-3-9/\’);
    define(\’DIR_WS_HTTPS_CATALOG\’, \’http://staysports.com/zencart-1-3-9/\’);

    define(\’DIR_WS_IMAGES\’, \’images/\’);
    define(\’DIR_WS_INCLUDES\’, \’includes/\’);
    define(\’DIR_WS_FUNCTIONS\’, DIR_WS_INCLUDES . \’functions/\’);
    define(\’DIR_WS_CLASSES\’, DIR_WS_INCLUDES . \’classes/\’);
    define(\’DIR_WS_MODULES\’, DIR_WS_INCLUDES . \’modules/\’);
    define(\’DIR_WS_LANGUAGES\’, DIR_WS_INCLUDES . \’languages/\’);
    define(\’DIR_WS_DOWNLOAD_PUBLIC\’, DIR_WS_CATALOG . \’pub/\’);
    define(\’DIR_WS_TEMPLATES\’, DIR_WS_INCLUDES . \’templates/\’);

    define(\’DIR_WS_PHPBB\’, \’/\’);

    // * DIR_FS_* = Filesystem directories (local/physical)
    //the following path is a COMPLETE path to your Zen Cart files. eg: /var/www/vhost/accountname/public_html/store/
    define(\’DIR_FS_CATALOG\’, \’C:/wamp/www/zencart-1-3-9/\’);

    define(\’DIR_FS_DOWNLOAD\’, DIR_FS_CATALOG . \’download/\’);
    define(\’DIR_FS_DOWNLOAD_PUBLIC\’, DIR_FS_CATALOG . \’pub/\’);
    define(\’DIR_WS_UPLOADS\’, DIR_WS_IMAGES . \’uploads/\’);
    define(\’DIR_FS_UPLOADS\’, DIR_FS_CATALOG . DIR_WS_UPLOADS);
    define(\’DIR_FS_EMAIL_TEMPLATES\’, DIR_FS_CATALOG . \’email/\’);

    // define our database connection
    define(\’DB_TYPE\’, \’mysql\’);
    define(\’DB_PREFIX\’, \’\’);
    define(\’DB_SERVER\’, \’Localhost\’);
    define(\’DB_SERVER_USERNAME\’, \’\’);
    define(\’DB_SERVER_PASSWORD\’, \’\’);
    define(\’DB_DATABASE\’, \’\’);
    define(\’USE_PCONNECT\’, \’false\’);
    define(\’STORE_SESSIONS\’, \’db\’);

    // for STORE_SESSIONS, use \’db\’ for best support, or \’\’ for file-based storage

    // The next 2 \"defines\" are for SQL cache support.
    // For SQL_CACHE_METHOD, you can select from: none, database, or file
    // If you choose \"file\", then you need to set the DIR_FS_SQL_CACHE to a directory where your apache
    // or webserver user has write privileges (chmod 666 or 777). We recommend using the \"cache\" folder inside the Zen Cart folder
    // ie: /path/to/your/webspace/public_html/zen/cache — leave no trailing slash
    define(\’SQL_CACHE_METHOD\’, \’none\’);
    define(\’DIR_FS_SQL_CACHE\’, \’C:/wamp/www/zencart-1-3-9/cache\’);

    // EOF

    this is my admin/include/confiugue.php
    <?php
    /**
    * @package Configuration Settings circa 1.3.9
    * @copyright Copyright 2003-2010 Zen Cart Development Team
    * @copyright Portions Copyright 2003 osCommerce
    * @license http://www.zen-cart.com/license/2_0.txt GNU Public License V2.0
    * File Built by zc_install on 2011-11-25 08:56:01
    */

    /*************** NOTE: This file is similar, but DIFFERENT from the \"store\" version of configure.php. ***********/
    /*************** The 2 files should be kept separate and not used to overwrite each other. ***********/

    // Define the webserver and path parameters
    // Main webserver: eg-http://www.your_domain.com –
    // HTTP_SERVER is your Main webserver: eg-http://www.your_domain.com
    // HTTPS_SERVER is your Secure webserver: eg-https://www.your_domain.com
    // HTTP_CATALOG_SERVER is your Main webserver: eg-http://www.your_domain.com
    // HTTPS_CATALOG_SERVER is your Secure webserver: eg-https://www.your_domain.com
    /*
    * URLs for your site will be built via:
    * HTTP_SERVER plus DIR_WS_ADMIN or
    * HTTPS_SERVER plus DIR_WS_HTTPS_ADMIN or
    * HTTP_SERVER plus DIR_WS_CATALOG or
    * HTTPS_SERVER plus DIR_WS_HTTPS_CATALOG
    * …depending on your system configuration settings
    *
    * If you desire your *entire* admin to be SSL-protected, make sure you use a \"https:\" URL for all 4 of the following:
    */
    define(\’HTTP_SERVER\’, \’http://staysports.com/\’);
    define(\’HTTPS_SERVER\’, \’http://staysports.com/\’);
    define(\’HTTP_CATALOG_SERVER\’, \’http://staysports.com/\’);
    define(\’HTTPS_CATALOG_SERVER\’, \’http://staysports.com/\’);

    // Use secure webserver for catalog module and/or admin areas?
    define(\’ENABLE_SSL_CATALOG\’, \’false\’);
    define(\’ENABLE_SSL_ADMIN\’, \’false\’);

    // NOTE: be sure to leave the trailing \’/\’ at the end of these lines if you make changes!
    // * DIR_WS_* = Webserver directories (virtual/URL)
    // these paths are relative to top of your webspace … (ie: under the public_html or httpdocs folder)
    //saqib area to change the admin root name
    define(\’DIR_WS_ADMIN\’, \’/zencart-1-3-9/admin-new/\’);
    define(\’DIR_WS_CATALOG\’, \’/zencart-1-3-9/\’);
    define(\’DIR_WS_HTTPS_ADMIN\’, \’/zencart-1-3-9/admin-new/\’);
    define(\’DIR_WS_HTTPS_CATALOG\’, \’/zencart-1-3-9/\’);

    define(\’DIR_WS_IMAGES\’, \’images/\’);
    define(\’DIR_WS_ICONS\’, DIR_WS_IMAGES . \’icons/\’);
    define(\’DIR_WS_CATALOG_IMAGES\’, HTTP_CATALOG_SERVER . DIR_WS_CATALOG . \’images/\’);
    define(\’DIR_WS_CATALOG_TEMPLATE\’, HTTP_CATALOG_SERVER . DIR_WS_CATALOG . \’includes/templates/\’);
    define(\’DIR_WS_INCLUDES\’, \’includes/\’);
    define(\’DIR_WS_BOXES\’, DIR_WS_INCLUDES . \’boxes/\’);
    define(\’DIR_WS_FUNCTIONS\’, DIR_WS_INCLUDES . \’functions/\’);
    define(\’DIR_WS_CLASSES\’, DIR_WS_INCLUDES . \’classes/\’);
    define(\’DIR_WS_MODULES\’, DIR_WS_INCLUDES . \’modules/\’);
    define(\’DIR_WS_LANGUAGES\’, DIR_WS_INCLUDES . \’languages/\’);
    define(\’DIR_WS_CATALOG_LANGUAGES\’, HTTP_CATALOG_SERVER . DIR_WS_CATALOG . \’includes/languages/\’);

    // * DIR_FS_* = Filesystem directories (local/physical)
    //the following path is a COMPLETE path to your Zen Cart files. eg: /var/www/vhost/accountname/public_html/store/
    define(\’DIR_FS_ADMIN\’, \’C:/wamp/www/zencart-1-3-9/admin-new/\’);
    define(\’DIR_FS_CATALOG\’, \’C:/wamp/www/zencart-1-3-9/\’);

    define(\’DIR_FS_CATALOG_LANGUAGES\’, DIR_FS_CATALOG . \’includes/languages/\’);
    define(\’DIR_FS_CATALOG_IMAGES\’, DIR_FS_CATALOG . \’images/\’);
    define(\’DIR_FS_CATALOG_MODULES\’, DIR_FS_CATALOG . \’includes/modules/\’);
    define(\’DIR_FS_CATALOG_TEMPLATES\’, DIR_FS_CATALOG . \’includes/templates/\’);
    define(\’DIR_FS_BACKUP\’, DIR_FS_ADMIN . \’backups/\’);
    define(\’DIR_FS_EMAIL_TEMPLATES\’, DIR_FS_CATALOG . \’email/\’);
    define(\’DIR_FS_DOWNLOAD\’, DIR_FS_CATALOG . \’download/\’);

    // define our database connection
    define(\’DB_TYPE\’, \’mysql\’);
    define(\’DB_PREFIX\’, \’\’);
    define(\’DB_SERVER\’, \’Localhost\’);
    define(\’DB_SERVER_USERNAME\’, \’\’);
    define(\’DB_SERVER_PASSWORD\’, \’\’);
    define(\’DB_DATABASE\’, \’\’);
    define(\’USE_PCONNECT\’, \’false\’);
    define(\’STORE_SESSIONS\’, \’db\’);

    // for STORE_SESSIONS, use \’db\’ for best support, or \’\’ for file-based storage

    // The next 2 \"defines\" are for SQL cache support.
    // For SQL_CACHE_METHOD, you can select from: none, database, or file
    // If you choose \"file\", then you need to set the DIR_FS_SQL_CACHE to a directory where your apache
    // or webserver user has write privileges (chmod 666 or 777). We recommend using the \"cache\" folder inside the Zen Cart folder
    // ie: /path/to/your/webspace/public_html/zen/cache — leave no trailing slash
    define(\’SQL_CACHE_METHOD\’, \’none\’);
    define(\’DIR_FS_SQL_CACHE\’, \’C:/wamp/www/zencart-1-3-9/cache\’);

    // EOF

  5. #5 by Equin on March 7, 2012 - 9:01 pm

    To make it working in admin, you have to repeat this procedure for corresponding files in admin directory.

  6. #6 by Delia on July 4, 2013 - 3:04 am

    Can this work in version 1.5.1?

  7. #7 by Imran on July 4, 2013 - 6:41 am

    Yes, it should work on 1.5. I have not tried yet, but do not think 1.5 has any change on this part of code.

  8. #8 by Thu Nguyen on August 21, 2014 - 7:23 am

    Hi Imran, thanks for the good work.

    I have a question regarding this post of yours, as it could be the answer to my quest for a solution. Do you have an actual example of why people want to connect to multiple databases in Zencart?

    I have implemented a zencart store for my workplace where members can order lab equipments, and that goes well. A neighbouring department wants to sell their stock (which is at the moment not in a zencart setup and they sell things in a different way) through our store to our members .

    I was looking at the multisite plug-in, but that requires me to manage their stock in my database for them. I do not have the human resources to do that.

    In your opinion, could your setup be a solution: I could setup a second instance of zencart admin without the front-end for them to manage their stock in a separate database, and then connect to their database/catalogue as a second catalogue on my front-end ?

  9. #9 by Imran on August 21, 2014 - 9:49 am

    Hi Thu,

    I don’t think you should go for this solution. This is generally for people who need to pull data from an existing NON-Zencart database.
    For your solution, I would suggest you to copy your Lab equipment store and create a separate store for the other department in a different domain (or a subdirectory of the same domain). Which means they will have a separate Zencart front and backend.

    Your way of doing the solution would add unnecessary complexity, not impossible though.

    Imran

  10. #10 by Thu Nguyen on August 25, 2014 - 7:15 am

    Thank you Imran,

    I will take your advise into account during the discussions.

  11. #11 by Sanjay on June 10, 2015 - 8:00 pm

    Hi Imran,

    I have a zencart store with database A
    I have also created a responsive version of the store under a sub domain which does not have all the features as the main store with database B
    The mobile visitors are redirected to the responsive version of my store.
    Now the problem is that I have to manually sync the customer accounts and orders between database A and database B
    Is there any way that both the databases get updated whenever a customer creates an account or places an order.

    Regards and Thanks for this useful blog.

Comments are closed.