Magento 2 – How to Import Products from Another Database

First thing you need to do is create a connection in your module's config.xml. It should look similar to the default_setup in your /app/etc/local.xml. Here you can specify the host to be localhost and then set a different dbname or you can specify a different host completely. I have also used a socket before which works also.

<resources>
    <new_db>
        <connection>
            <host><![CDATA[localhost]]></host>
            <username><![CDATA[db_username]]></username>
            <password><![CDATA[db_password]]></password>
            <dbname><![CDATA[db_name]]></dbname>
            <model>mysql4</model>
            <type>pdo_mysql</type>
            <active>1</active>
        </connection>
    </new_db>
</resources>

Now after this you will be able to connect to this database an perform queries as follows:

$new_db_resource = Mage::getSingleton('core/resource');
$connection = $new_db_resource->getConnection('new_db');
$results    = $connection->query('SELECT * FROM table');

If you want to do this via a model then you can specify the read, write and setup resources as follows. This will again be done inside the resources node in your config.xml and you should replace test with what your model has been setup as.

<resources>
    <new_db>
        <connection>
            <host><![CDATA[localhost]]></host>
            <username><![CDATA[db_username]]></username>
            <password><![CDATA[db_password]]></password>
            <dbname><![CDATA[db_name]]></dbname>
            <model>mysql4</model>
            <type>pdo_mysql</type>
            <active>1</active>
        </connection>
    </new_db>
    <test_write>
        <connection>
            <use>new_db</use>
        </connection>
    </test_write>
    <test_read>
        <connection>
            <use>new_db</use>
        </connection>
    </test_read>
    <test_setup>
        <connection>
            <use>new_db</use>
        </connection>
    </test_setup>
</resources>
<models>
    <test>
        <class>My_Test_Model</class>
        <resourceModel>test_resource</resourceModel>
    </test>
    <test_resource>
        <class>My_Test_Model_Resource</class>
        <entities>
            <test>
                <table>test</table>
            </test>
        </entities>
    </test_resource>
</models>

The model itself will try to find it's connection information in the function getConnection /app/code/core/Mage/Core/Model/Resource.php. If you log the $name passed in you will see values like poll_write, tag_write and cms_read where the first part matches the models section in the config.xml, in our case you would see test_write, test_read or test_setup. If it cannot find a connection matching this then it will use the default connections core_read, core_write or core_setup

I don't like answering my own questions, but here goes! Just a different approach to sorting the products and making it more manage-able helped. My steps:

1. Disable the category sort order's ordering on the front end view (remove "position" from the sort order thanks to this nice post [http://prattski.com/2009/11/30/magento-how-to-remove-the-position-sort-option/][1])
2. Add a new Attribute for all products not visible/search-able etc, just sort-able and give that a label such as "Default".

The new attribute is easily changed along with all the other product data via magento's import/export data-flow profiles. The only set back is to have a reminder that if you update your theme, to go and repeat step 1 above.