Use multiple databases in OpenCart

Posted: April 13, 2013 by Sankar Vijayakumar in OpenCart
Tags: , , , , , ,
To use multiple databases in OpenCart (1.5.*), just update 3 files as given below:

  1. Config.php:
    Add:

    //New DB
    define('NEWDB_DRIVER', 'mysql');
    define('NEWDB_HOSTNAME', 'localhost');
    define('NEWDB_USERNAME', 'root');
    define('NEWDB_PASSWORD', 'password');
    define('NEWDB_DATABASE', 'sitename_newdb');
    define('NEWDB_PREFIX', 'ndb');

  2. Index.php:
    Below the current database setup ($db = new DB(DB_DRIVER …) add:

    $newdb = new DB(NEWDB_DRIVER, NEWDB_HOSTNAME, NEWDB_USERNAME, NEWDB_PASSWORD, NEWDB_DATABASE);
    $registry->set('newdb', $newdb );

  3. system/database/mysql.php:
    Modify:

    change:
    if (!$this->link = mysql_connect($hostname, $username, $password)) {
    to:
    if (!$this->link = mysql_connect($hostname, $username, $password, true)) {

After these above steps we can access the new database by:

$this->newdb->query("SELECT * FROM ". NEWDB_PREFIX . "users");

That’s it. Happy coding 😆 !

Advertisements
Comments
  1. Alvin M says:

    Hello…..I know its an old post but I found it very helpful..thank you.
    However I am stuck on the last line regarding switching to the new database ?
    I am not sure where to change this line ?..please help.
    Regards,
    Alvin

    Like

  2. Waj says:

    really great work, solved my problem which I was stuck with for whole one day. You must a opencart Guru 🙂

    Like

  3. Alex says:

    Perfect!!! Thanks!

    Like

  4. agus says:

    hi
    i have 2 store use multistore function. i would like to know how to totally separate backend and frontend for 2nd store ? 2nd store cannot see any product and dashboard of 1st store?

    Like

    • Sankar says:

      Hi Agus,

      For front end you can select separate theme for your stores via ‘System > Settings > Edit (your 2nd store)’, ‘Platform’ tab – ‘Template:’ option.

      I doubt whether the admin url can be changed or not. Eventhough we can login to admin via store1.com/admin and store2.com/admin, once we logged-in the login url gets redirected to the default store admin ‘store1.com/admin’. If you’ve separate admin users for your store then you can assign separate user groups for those users based on their store and change css files based on the store user groups.

      Have a nice day 🙂 !!

      Like

      • agus says:

        hi the admin url i can change by change the modificaton config.php in admin folder
        // HTTP
        define(‘HTTP_SERVER’, ‘/admin/’);
        define(‘HTTP_CATALOG’, ‘/’);

        // HTTPS
        define(‘HTTPS_SERVER’, ‘/admin/’);
        define(‘HTTPS_CATALOG’, ‘/’);
        now my other domain can login to admin site by using their own url without redirecting to main store url

        but the 2nd store still displaying the value and database of main store url. how to totally redirect 2nd store to newdb ? what value should i change?
        is the config.php i also need to alter in /admin/ folder?

        Like

        • Sankar says:

          “now my other domain can login to admin site by using their own url without redirecting to main store url “
          – what happens after login from second store? Does it get redirected to main stores admin url?

          “but the 2nd store still displaying the value and database of main store url. how to totally redirect 2nd store to newdb ? what value should i change?”
          – Why do you use separate database for the second store? For using multistore feature in opencart, you don’t need to use separate databases for each store.

          Like

  5. agus says:

    because if no separate. when 2nd store admin login to the backend he can see 1st store products also. 1st store and 2nd store will selling different things and different owner. this is what i thought. but right now still fail.

    Like

    • Sankar says:

      If both stores are totally independent, why do you need the multi-store setup, instead of two separate opencart installation?

      I think you could have managed the same by assigning usergroup to admin users and adding conditions in code for loading site data.

      Like

  6. agus says:

    i use this method is to prevent user no need to do uploading opencart. and easier manage for admin. i try to provide to user all the template into 1 opencart system. so if i assign multiplestore user can choose other template that i have upload previously. i’m thinking with this method if i have 10 user and 10 template i only need to upload once and then i assign the template to the users.

    if i use individual opencart. i have 10 template and 10 user i need to upload all the same template to 10 user it will consume my harddisk resources.

    one of the similar solutions that i’m looking is kodemall.com. 1 system but can have multiple and different store inside. this is being customize by kodeplay.com

    Like

  7. Sankar says:

    What you’re trying to achieve is separate backend and frontend for 2nd store, right? Do you have separate store id’s for your stores? Your issue may be that your multi-store relations exists in the main table. As the multi-store data are stored in different databases, the default opencart join queries cannot fetch data from the other database.

    Like

  8. thank for your article it’s helpful but may i know possible to use in 2 different type database driver for example Mysqli & MSSQL with different server?

    Like

  9. muhamad makrif says:

    can we do the same thing on postgresql driver

    Like

  10. rainer says:

    I’m facing a problem that is a bit related to what’s discussed here, so I give it a shot:
    I have two Opencart stores. One based on v1.5.5.1 (store1), the other on v2.x (store2).
    All products in store1 are also sold in store2, however with completely different descriptions, prices, images, SEO, etc. Thus, no synergies in this regard. However, I use the same invoicing and inventory system for both stores, so I want to link them with respect to product quantity and invoice number. Example: if someone buys a product in either of the two stores, the invoice number counter in both stores should go up and the product quantity counter in both stores should be decreased.

    I thought this to be a common problem, but couldn’t find any discussions or solutions in this regard. Do you have an idea perhaps how to tackle this?

    Like

    • You’re using 2 opencart versions, so the code is different for the 2 stores. Opencart supports multi-store feature. So the best solution is to add your old store in opencart v2 as another store.

      Like

      • rainer says:

        Yep, but that’s the problem. I cannot upgrade the older store to v2, since it has too many customizations and it would be a nightmare to build everything up again in v2. Therefore I want to leave it as is, esp. since v2 is currently still in a stage of development and updates are released frequently. In about two years.
        I thought that under those circumstances it would be “easiest” to update the stock counter in either of the two stores after a purchase has been made in the other store. Do you have an idea how to do this?

        Like

        • What about implementing store 2 in older opencart as another store? so that both stores will share the same database. since you’ve made so many customizations in the older version i don’t know any other solution.. also try posting this question in opencart forum.

          Like

  11. megha says:

    Hi, I am new with opencart.i followed all the changes but when i am trying to register the second db in index.php page, the site shows blank.nothing working….Please help

    Like

  12. Gus says:

    Hi
    I have project that need to connect 2 database.
    1st database is opencart 1.5.6
    2nd database is just contain user id and domain information

    Userid domain
    1 domain1.com
    2 domain2.com

    I need to load the value of userid 2 if the http host open this file. How to load this 2nd database userid inside filemanager class ? Thanks in advanced

    Like

    • Did you try the steps mentioned in this post?

      Like

      • Gus says:

        Yes, i did try until this step

        3. system/database/mysql.php: (i change in mysqli,php)
        Modify:
        change:
        if (!$this->link = mysql_connect($hostname, $username, $password)) {
        to:
        if (!$this->link = mysql_connect($hostname, $username, $password, true)) {

        but i dont know how to use this $this->newdb->query(“….”);

        i wanted to call this but it seems have error Parse error: syntax error, unexpected ‘fashion1’ (T_STRING) in /home/tokowebs/public_html/fashion/vqmod/vqcache/vq2-admin_controller_common_filemanager.php on line 39

        $this->data[‘directory’] = HTTP_CATALOG . ‘image/data/’;
        $this->data[‘directory’] = HTTP_CATALOG . ‘image/data/.’$this->newdb->query(“SELECT ID FROM domain where domain=$_SERVER[‘HTTP_HOST’]”)’.’;

        please advise correct way to use this function many thanks

        Like

        • Once you’ve completed all 3 steps, you can fetch data from new database like:
          ‘$this->newdb->query(“….”);’ and no change is neeeded to fetch data from the default database ( $this->db->query(“….”); ).

          Like

  13. sanjay says:

    HI i try in my opencart 2.3 version it give me registry variable undefined so please help me ASAP.

    Like

How's it? Your comments and suggestions...

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s