Magento direct sql query not working in my dynamic adminhtml module config

Question

Following this tutorial I've implemented a dynamic key => value pair setting in my magento adminhtml backend module config.

So, this is my system.xml

<shipping_method_mapping translate="label">
    <label>Shipping Method Mapping</label>
    <comment><![CDATA[Select an active shipping method in magento and map it to the code used by Asendia.]]></comment>
    <frontend_model>infiniteconnect/fieldasendia</frontend_model>
    <backend_model>adminhtml/system_config_backend_serialized_array</backend_model>
    <source_model>infiniteconnect_adminhtml/system_config_source_dropdown_activeshippingmethods</source_model>
    <sort_order>4</sort_order>
    <show_in_default>1</show_in_default>
    <show_in_website>0</show_in_website>
    <show_in_store>0</show_in_store>
</shipping_method_mapping>

and this is my Activeshippingmethods.php (dropdown source model):

class Intellibi_InfiniteConnect_Adminhtml_Model_System_Config_Source_Dropdown_Activeshippingmethods
{
    public function toOptionArray()
    {
        // Init Option Array
        $optionArray = array();

        // Load Magento DB Connection
        $resource = Mage::getSingleton('core/resource');
        $readConnection = $resource->getConnection('core_read');

        // Query Magento DB
        $active_shipping_methods = $readConnection->fetchAll(
            "SELECT DISTINCT `shipping_description` AS `name` FROM ". $resource->getTableName('sales_flat_order'));

        // Build Option Array
        foreach ($active_shipping_methods as $active_shipping_method) {
            $optionArray[] = array(
                'value' => md5($active_shipping_method['name']),
                'label' => Mage::helper('infiniteconnect')->__($active_shipping_method['name'])
            );
        }

        // Finished
        return $optionArray;
    }
}

When I test this locally on my wamp installation; it works great:

enter image description here

However, when I upload this to my remote DEV server running LEMP stack, the behaviour changes and the config screen breaks/does not render correctly like this:

enter image description here

If I modify my Activeshippingmethods.php dropdown source model and comment out all the direct query stuff and return an empty array, then the config screen doesn't break and renders correctly - so this is why I thought there's a problem with the direct query...

I've already checked if my remote linux server has entries in sales_flat_order table and it does (58 distinct shipping_description).

Any idea why this isn't working correctly on a linux server, but it's fine on the WAMP server for some reason? Is this some sort of issue with my module not having the correct ACL to use core_read connection?

To rewrite your sql see http://stackoverflow.com/questions/4511314/filter-magento-collection-but-not-products-using-distinct — MagePal Extensions, Nov 12 '14 at 11:55
I've checked my var/log folder and both empty. I've also checked my nginx and php-fpm logs and nothing found nothing in there related to this error my module. — Latheesan, Nov 12 '14 at 11:57
Are you just trying to get a list of all active shipping method? Take a look at http://stackoverflow.com/questions/9433627/programatically-retrieve-list-of-all-shipping-methods — MagePal Extensions, Nov 12 '14 at 11:57
Sorry, that's not what I need. I need a distinct list of shipping_description from sales_flat_order table. — Latheesan, Nov 12 '14 at 11:59
You should ALWAYS try to keep your dev environment as close replicate of production (os, php, mysql etc) — MagePal Extensions, Nov 12 '14 at 12:01
I don't think that's the problem here. My wamp installation has Apache 2.x, MySQL 5.5 and PHP 5.5. My staging server has LEMP stack (still mysql 5.5 and php 5.5). What's bizarre is that it works fine on WAMP and does not on the remote LEMP server stack. I've narrowed it down to that dropdown source model and if you were to check it, there's nothing fancy in there... so why does it break? — Latheesan, Nov 12 '14 at 12:03
Try $resource->getTableName('sales/order'); instead of $resource->getTableName('sales_flat_order') — MagePal Extensions, Nov 12 '14 at 12:07
Table has no prefix and btw, both method of getting table name is valid. I've checked. — Latheesan, Nov 12 '14 at 12:10
Try running the sql on the production db, how many result it return? — MagePal Extensions, Nov 12 '14 at 12:13
This is very bizarre... I've removed my direct query and turned it into an model collection query (as suggested) like this: http://pastebin.com/5vpFy94D (again, this is working on my local WAMP installation, however it does not in the LEMP stack staging server...) — Latheesan, Nov 12 '14 at 12:14
It does not help, the screen still breaks and not render correctly. — Latheesan, Nov 12 '14 at 12:22
could it be a memory issue? Try hardcode 1 value to return, comment out the sql, then add back code one at a time — MagePal Extensions, Nov 12 '14 at 12:25

score 1 · Answer 1 · answered Nov 12 '14 at 13:07

I've solved the problem by updating my drop down source model like this:

public function toOptionArray()
{
    // Init Option Array
    $optionArray = array();

    // Query Magento & Build Option Array
    $collection = Mage::getModel('sales/order')->getCollection();
    $collection->addAttributeToSelect('shipping_description');
    $collection->getSelect()->distinct(true);
    foreach ($collection as $order) {
        $optionArray[] = array(
            'value' => md5($order->getShippingDescription()),
            'label' => str_replace("'", "\'", $order->getShippingDescription())
        );
    }

    // Finished
    return $optionArray;
}

The selection options were used inside javascript, which broke when any "labels" have a single quote in them. So I had to explicitly escape it...

I'd recommend using Mage::helper('core')->jsQuoteEscape() instead of str_replace (see How to escape output). It does the same but is more explicit. Also, feel free to accept your own answer. — Fabian Schmengler, Feb 20 '15 at 09:59

Magento direct sql query not working in my dynamic adminhtml module config

1 Answers1