Magento – Selecting a table and column

databasemodelsql

I am trying to get data from a custom table which I have added to the database. But when I call data from it I do not get anything from that table. My page displays data I had previously requested, from an alternate custom table, but the additional data I require is not showing.

Looking in var/log/exception.log I see a message which includes

Column not found: 1054 Unknown column 'prefcentreOptions.prefcentreoptions_id' in 'where clause''

from this message I assumed there was a problem with my config.xml (but I can't see one) as you can see from my config my additional table is called prefcentre, I pull data from here successfully, it is prefcentreOptions that I am having trouble with

...
<models>
        <prefs>
            <class>Ps_Prefs_Model</class>
            <resourceModel>prefs_resource</resourceModel>
        </prefs>

        <prefs_resource>
            <class>Ps_Prefs_Model_Resource</class>
            <entities>
                <prefs>
                    <table>prefcentre</table>
                </prefs>
                <prefsemail>
                    <table>prefcentreOptions</table>//not showing
                </prefsemail>
            </entities>
        </prefs_resource>
....

I then took a look at my models (I have commented what my understanding of each file does, I would appreciate it someone could please correct if i'm wrong – which i'm sure i am)

class Ps_Prefs_Model_Prefsemail
    extends Mage_Core_Model_Abstract
{
    public function _construct()
    {   //initalises the model _init('module/modelname', 'targetedColumn')
        $this->_init('prefs/prefsemail', 'prefcentreoptions_id');
    }
}

class Ps_Prefs_Model_Resource_Prefsemail
    extends Mage_Core_Model_Resource_Db_Abstract
{
    protected function _construct()
    {    //initalises the resource _init('module/modelname', 'targetedColumn')
         $this->_init('prefs/prefsemail', 'prefcentreoptions_id');
    }
}

class Ps_Prefs_Model_Resource_Prefsemail_Collection
    extends Mage_Core_Model_Resource_Db_Collection_Abstract
{
    protected function _construct()
    {   ////initalises the collection _init('module/tablename')
        $this->_init('prefs/prefcentreoptions');
    }
}

In my database there is a table called prefcentreOptions with a column called prefcentreOptions_id which contradicts the exception I have recieved

So far I have not really considered what each one of these files does, apart from the very basic comments which I have added any further explination of above files and assistance as to why my module is not working will be very helpful

Best Answer

Some comments:

  • Name your tables with lowercase rather, so prefscentre_options
  • The _init method you call on your model Ps_Prefs_Model_Prefsemail takes 1 argument, only the resource model location.

About the table, did you manually create it? Normally it is done via a installation file, I'll assume you have that.

You say in the table you have a column named prefcentreOptions_id whereas in the magento configuration you specify it to be named prefcentreoptions_id. So difference in the casing. I'd call the table prefcentre_options and call the id field entity_id field because it is quite logically already that it's the id field if it's the primary key, so it's an unnecessary long name.

Related Topic